Can this be optimized? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Can this be optimized?

Author

Topic

robertser
Starting Member

4 Posts

Posted - 2005-05-23 : 16:38:16

Here is the query I am running. Is there any way to optimize this so it runs faster? Is there some way to do a join instead of the not exists?

SELECT 'Unpatched MS05-020' AS 'Patch', 'KB890923' AS 'KB#', COUNT(wk.name) AS 'Total', 'IE Cumulative Patch' AS 'Description', '04/12/05' AS 'Release Date',
'99' AS 'Sort'
FROM Inv_aex_AC_Identification wk
WHERE (wk.[OS NAME] = 'Microsoft Windows 2000' OR
(wk.[OS Name] = 'Microsoft Windows XP')) AND NOT EXISTS
(SELECT _ResourceGuid
FROM Inv_AeX_OS_Updates upd
WHERE wk._resourceGuid = upd._resourceGuid AND ([Patch ID] LIKE 'KB890923%')) AND NOT EXISTS
(SELECT ResourceGuid
FROM CollectionMembership cm
WHERE wk._resourceGuid = cm.ResourceGuid AND CollectionGuid = '{db5d3e38-192f-412f-a741-37e6de49ba19}')

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-23 : 17:04:02

It's easier to read when you use [ code] [ /code] tags...with out the space..and it's better if you use some form of identing. What you have won't even run though


SELECT 'Unpatched MS05-020' AS 'Patch'
	, 'KB890923' AS 'KB#'
	, COUNT(wk.name) AS 'Total'
	, 'IE Cumulative Patch' AS 'Description'
	, '04/12/05' AS 'Release Date'
	, '99' AS 'Sort'
  FROM Inv_aex_AC_Identification wk 
 WHERE (wk.[OS NAME] = 'Microsoft Windows 2000' 
    OR  wk.[OS Name] = 'Microsoft Windows XP') 
   AND NOT EXISTS ( SELECT _ResourceGuid
		      FROM Inv_AeX_OS_Updates upd
		     WHERE wk._resourceGuid = upd._resourceGuid 
		       AND [Patch ID] LIKE 'KB890923%') 
   AND NOT EXISTS ( SELECT ResourceGuid
		      FROM CollectionMembership cm
		     WHERE wk._resourceGuid = cm.ResourceGuid 
		       AND CollectionGuid = '{db5d3e38-192f-412f-a741-37e6de49ba19}')

You use NOT EXISTS, but you need to coorelate the su queries.

Brett

8-)

robertser
Starting Member

4 Posts

Posted - 2005-05-23 : 17:52:43

Actually what I have works fine. The query runs and returns the results. I am just trying to make it run faster. Not sure what you mean by it doesn't coorelate?????

Bad_Boy
Troll

6 Posts

Posted - 2005-05-24 : 06:38:17

truncate table Inv_aex_AC_Identification <-- shortcut

robertser
Starting Member

4 Posts

Posted - 2005-05-24 : 08:47:14

I'll be sure and try that cool truncate command. Hope that speeds things up.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-24 : 08:47:35

Bad_boy seems to have trouble thinking straight. Dont drink and post!

Brett, these queries seem perfectly correlated to me. You haven't confused this with something else, have you?


       AND NOT EXISTS ( SELECT _ResourceGuid
		      FROM Inv_AeX_OS_Updates upd
		     WHERE wk._resourceGuid = upd._resourceGuid 
		       AND [Patch ID] LIKE 'KB890923%')

Robert, most subqueries using NOT EXISTS can be replaced with a LEFT JOIN and a test for NULL on the "right" table. For example:


SELECT A.Col1 FROM A
 WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.Col2 = B.Col2)

...can be re-written as...


SELECT A.Col1 FROM 
 A LEFT JOIN B ON A.Col2 = B.Col2
 WHERE B.Col2 IS NULL

robertser
Starting Member

4 Posts

Posted - 2005-05-24 : 09:01:14

Thanks for the tip on the Left Join. I knew there had to be a way to use a join instead of a not in scenario. What I still can't figure out, and maybe there is no better way to do it, is how to still test for the Patch ID in the not exists. The reason I'm doing it this was is because of a one-to-many relationship in the OS_Updates table. It has the same _resourceguid listed for several different patch id's.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-24 : 09:42:05

Ah yes, a one-to-many relationship would return duplicate rows when using a left join. You can however, filter out the duplicates by adding in a DISTINCT.


SELECT DISTINCT A.Col1 FROM 
 A LEFT JOIN B ON A.Col2 = B.Col2
 WHERE B.Col2 IS NULL

In certain cases this will give you better performance than with a NOT exists. You could also try replacing the not exists with a NOT IN, but it is unlikely that you will get better performance with it. Also, a null in your subquery could screw up the results with a NOT IN. Your best bet is to test various options and see which one performs best. You should also ensure that you have the appropriate indexes...they can make a big difference.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources