Help me make an efficient query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help me make an efficient query

Author

Topic

tech1
Starting Member

49 Posts

Posted - 2014-05-15 : 08:07:23

Hi all.
my SQL-fu is a little rough these days and hoping someone can help.
This query is inefficient and needless to say, takes a while for it to come back with results. How can the query be improved?

quote:

SELECT l.[Month], l.[serial number], eus.servicenow.v_assets_current.[asset type], eus.servicenow.v_assets_current.[operational status], eus.ServiceNow.v_assets_current.location, l.[ibm serial number], eus.servicenow.v_assets_current.city, eus.servicenow.v_assets_current.[managed ownership type],
ROW_NUMBER() OVER (ORDER BY [Month] DESC) AS RowNumber
FROM lease.planned_returns l
INNER JOIN eus.servicenow.v_assets_current ON l.[serial number] = eus.servicenow.v_assets_current.[serial number]
INNER JOIN finance.lease.v_asset_lease_details_current ON l.[serial number] = finance.lease.v_asset_lease_details_current.[Serial Number]
WHERE (l.Country = @country)
AND ([Month] = CAST(@year AS varchar) + '-' + RIGHT ('00' + CAST(@month AS varchar), 2))
AND (l.[serial number] NOT IN (SELECT [serial number] FROM lease.asset_rnn))
AND (l.[serial number] NOT IN (SELECT [serial number] FROM finance.lease.v_lease_actual_returns))
AND (l.[serial number] NOT IN (SELECT [serial number] FROM eus.servicenow.v_assets_current
WHERE ([serial number] = l.[serial number]) AND ([managed ownership type] <> 'leased')))

I believe the last 3 AND clauses are the ones to blame....

thoughts?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 08:52:34

Does the query plan indicate scans for the three AND clauses you're worried about? That might indicate missing indexes. If the referenced tables are big, that would be a problem.

Also, you're joining the eus.servicenow.v_assets_current table on [serial number] but then you have this in the WHERE clause:


AND (l.[serial number] NOT IN (SELECT [serial number] FROM eus.servicenow.v_assets_current ...

That looks redundant to me. Not sure SQL will notice that when generating the plan (check the plan to be sure). If you agree, and remove the redundant subquery, then also pull that sub-query's WHERE clause out of the subquery and make it part of the main WHERE conditions.

Otherwise, ensure that the tables and views are properly indexed. See if SHOW PLAN recommends and indexes and explore those options.

tech1
Starting Member

49 Posts

Posted - 2014-05-15 : 09:13:29

thanks. It could be an indexes thing but the reason I say the AND clauses are a problem is because when I remove one or two of them, data comes back faster.

in regards to your suggestion about the redundancy reference - what should I do instead? What do you mean by pulling the Subquery's where clause out of it and make it part of the main where condition? - sorry, its been a long night!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 09:46:20

quote:
Originally posted by tech1

thanks. It could be an indexes thing but the reason I say the AND clauses are a problem is because when I remove one or two of them, data comes back faster.

in regards to your suggestion about the redundancy reference - what should I do instead? What do you mean by pulling the Subquery's where clause out of it and make it part of the main where condition? - sorry, its been a long night!

Something like this: ( I commented out your last AND condition and added one of my own, removing the double-negative)


SELECT l.[Month]
     , l.[serial number]
     , eus.servicenow.v_assets_current.[asset type]
     , eus.servicenow.v_assets_current.[operational status]
     , eus.ServiceNow.v_assets_current.location
     , l.[ibm serial number]
     , eus.servicenow.v_assets_current.city
     , eus.servicenow.v_assets_current.[managed ownership type]
     , ROW_NUMBER() OVER (ORDER BY [Month] DESC) AS RowNumber

FROM lease.planned_returns l
INNER JOIN eus.servicenow.v_assets_current 
        ON l.[serial number] = eus.servicenow.v_assets_current.[serial number] 
INNER JOIN finance.lease.v_asset_lease_details_current 
        ON l.[serial number] = finance.lease.v_asset_lease_details_current.[Serial Number] 

WHERE (l.Country = @country) 
  AND ([Month] = CAST(@year AS varchar) + '-' + RIGHT ('00' + CAST(@month AS varchar), 2))
  AND (l.[serial number] NOT IN (SELECT [serial number] FROM lease.asset_rnn))
  AND (l.[serial number] NOT IN (SELECT [serial number] FROM finance.lease.v_lease_actual_returns))
  AND (eus.servicenow.v_assets_current .[managed ownership type] = 'leased')
  --AND (l.[serial number] NOT IN (SELECT [serial number] FROM eus.servicenow.v_assets_current 
  --     WHERE ([serial number] = l.[serial number]) AND ([managed ownership type] <> 'leased')))

I'd probably alias the the two tables you're joining on, just to make it a bit easier to read. You'll have to verify that the results are as desired and the performance is improved (or not!)

Have you checked the query plan?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources