SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help me make an efficient query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech1
Starting Member

49 Posts

Posted - 05/15/2014 :  08:07:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1733 Posts

Posted - 05/15/2014 :  08:52:34  Show Profile  Reply with Quote
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.

Edited by - gbritton on 05/15/2014 08:53:21
Go to Top of Page

tech1
Starting Member

49 Posts

Posted - 05/15/2014 :  09:13:29  Show Profile  Reply with Quote
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!
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1733 Posts

Posted - 05/15/2014 :  09:46:20  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000