Improve efficiency of script [message #682152] |
Tue, 06 October 2020 19:02 |
|
Dan22
Messages: 2 Registered: October 2020
|
Junior Member |
|
|
Hi all
My first post here. Hope someone can help with a basic question.
I have the below script and it just runs for way too long. Is there a way to improve the efficiency?
I don't have privileges to create temp tables or anything.
SELECT DISTINCT s1.ANOTHER_NUM ,s1.LAST_UPD
FROM S_CONTACT s1
LEFT JOIN S_ASSET_CON s2 ON s2.CONTACT_ID = s1.row_id
LEFT JOIN s_asset s3 ON s2.ASSET_ID = s3.row_id
LEFT JOIN S_PROD_INT s4 ON s3.row_id = s4.row_id
WHERE s1.FIELDA= 'Partially Complete'
AND s1.FIELDB = 'Re-verification Required'
AND s3.NUM IS NOT NULL
AND s1.ANOTHER_NUM IS NOT NULL
|
|
|
|
|
Re: Improve efficiency of script [message #682157 is a reply to message #682152] |
Wed, 07 October 2020 01:24 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Your use of outer joins is forcing the join order, do you really need them?
As you have the filter
AND s3.NUM IS NOT NULL
that outer join must be pointless: it is generating null rows which you then throw them away.
Is the DISTINCT actually doing anything? Are there any duplicates without it?
Is the join to s4 necessary? You do not project any columns from s4, and as it is (again!) an outer join, it does not filter any rows either.
|
|
|
|
|
Re: Improve efficiency of script [message #682202 is a reply to message #682158] |
Thu, 08 October 2020 20:19 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Dan22 wrote on Wed, 07 October 2020 08:29
So I am trying to retrieve all S1. Customers who HAVE an account, i.e. not null for S3.
So, the join is definitely not OUTER. And, as the query shall return values from S1 only, you may use IN or EXISTS subquery for the existence check (the description of expected results explicitly mentions it), something like:
select s1.another_num, s1.last_upd
from s_contact s1
where s1.fielda = 'Partially Complete'
and s1.fieldb = 'Re-verification Required'
and s1.another_num is not null
and s1.row_id in (
select s2.contact_id from s_asset_con s2
inner join s_asset s3 on s2.asset_id = s3.row_id
--left join s_prod_int s4 on s3.row_id = s4.row_id
where s3.num is not null
)
|
|
|
Re: Improve efficiency of script [message #682205 is a reply to message #682158] |
Fri, 09 October 2020 03:30 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:I don't know if there will be any duplicates, just taking precautions. A mantra worth trying:
You must understand your data! You must understand your queries!
To write code that is both efficient and fills the business need, you have to know the data with which you are working. In this case, is it actually possible that there will be duplicates? If you investigate you may find that the constraints (existing constraints, or more that you can declare) mean duplicates are not possible. Or perhaps they are possible, but in business terms should not be. Or if they are possible and reasonable, perhaps they should be handled differently.
Use of DISTINCT is one of my pet hates. It so often covers up a poor data model, or lack of a clear problem definition.
|
|
|