Replacement of exists - 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 2005 Forums

Transact-SQL (2005)

Replacement of exists

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 09:09:57

I have one query below


SELECT Distinct D.RegID FROM PTH_PatchDeploymentStatus D WITH(NOLOCK)
INNER JOIN Pth_PatchDeploymentLookup DL ON D.RegID = DL.RegID AND D.ScanDateTime = DL.ScanDateTime
AND D.Patch_Status <> 'Operation Completed Successfully' 
AND D.Patch_Status <> 'UPLOADED' 
AND D.Patch_Status <> 'Patch Already Installed'
AND EXISTS
(
	SELECT TOP 1 REGID FROM PTH_PATCHASSESSMENTINSTALLED WITH(NOLOCK)
	WHERE REGID=D.REGID AND UPDATEID=D.UPDATEID
	AND SCANDATETIME=(SELECT SCANDATETIME FROM PTH_PATCHLOOKUP WITH(NOLOCK) WHERE REGID=D.REGID)
	UNION
	SELECT TOP 1 REGID FROM VIWPTHMISSING WITH(NOLOCK)
	WHERE REGID=D.REGID AND UPDATEID=D.UPDATEID AND SCANDATETIME=
	(SELECT SCANDATETIME FROM PTH_PATCHLOOKUP WITH(NOLOCK) WHERE REGID=D.REGID)
)

tables before exists statements are manageable in size
but
in the exists statement
PTH_PATCHASSESSMENTINSTALLED and VIWPTHMISSING are very huge tables
so every time i am getting time out for the this query.

can anyone suggest me how can i replace exist with some other logic.

Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 09:53:28

whats the purpose of top 1 in both selects inside exists?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 10:18:24

Mean i have to select those regid who are in alteast one of the two tables
PTH_PATCHASSESSMENTINSTALLED and VIWPTHMISSING

Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:23:35

try using individual exists with each table rather than merging them with UNION

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 10:29:54

See if there is any improvement


SELECT Distinct D.RegID FROM PTH_PatchDeploymentStatus D WITH(NOLOCK)
INNER JOIN Pth_PatchDeploymentLookup DL ON D.RegID = DL.RegID AND D.ScanDateTime = DL.ScanDateTime
INNER JOIN PTH_PATCHLOOKUP as P ON  P.REGID=D.REGID
WHERE
D.Patch_Status <> 'Operation Completed Successfully' 
AND D.Patch_Status <> 'UPLOADED' 
AND D.Patch_Status <> 'Patch Already Installed'
AND EXISTS
(
	SELECT TOP 1 REGID FROM PTH_PATCHASSESSMENTINSTALLED WITH(NOLOCK)
	WHERE REGID=D.REGID AND UPDATEID=D.UPDATEID
	AND SCANDATETIME=P.SCANDATETIME 
)
AND EXISTS
(
	SELECT TOP 1 REGID FROM VIWPTHMISSING WITH(NOLOCK)
	WHERE REGID=D.REGID AND UPDATEID=D.UPDATEID 
	AND SCANDATETIME=P.SCANDATETIME 
)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:31:46

quote:

Originally posted by madhivanan

See if there is any improvement


SELECT Distinct D.RegID FROM PTH_PatchDeploymentStatus D WITH(NOLOCK)
INNER JOIN Pth_PatchDeploymentLookup DL ON D.RegID = DL.RegID AND D.ScanDateTime = DL.ScanDateTime
INNER JOIN PTH_PATCHLOOKUP as P ON  P.REGID=D.REGID
WHERE
D.Patch_Status <> 'Operation Completed Successfully' 
AND D.Patch_Status <> 'UPLOADED' 
AND D.Patch_Status <> 'Patch Already Installed'
AND (EXISTS
(
	SELECT TOP 1 REGID FROM PTH_PATCHASSESSMENTINSTALLED WITH(NOLOCK)
	WHERE REGID=D.REGID AND UPDATEID=D.UPDATEID
	AND SCANDATETIME=P.SCANDATETIME 
)
ANDOR EXISTS
(
	SELECT TOP 1 REGID FROM VIWPTHMISSING WITH(NOLOCK)
	WHERE REGID=D.REGID AND UPDATEID=D.UPDATEID 
	AND SCANDATETIME=P.SCANDATETIME 
)
)

Madhivanan

Failing to plan is Planning to fail

I think it should be OR

Mean i have to select those regid who are in alteast one of the two tables
PTH_PATCHASSESSMENTINSTALLED and VIWPTHMISSING

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 10:35:56

Yes. You are correct

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:38:43

quote:
Originally posted by madhivanan

Yes. You are correct

Madhivanan

Failing to plan is Planning to fail

thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-15 : 10:42:56

Thanks both of you,
this is making big difference in performance.

Thank you very much

Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:44:13

good..I guessed so as UNION will surely have its impact on performance especially when tables are large

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 10:49:28

quote:
Originally posted by visakh16

good..I guessed so as UNION will surely have its impact on performance especially when tables are large

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Other issues was using PTH_PATCHLOOKUP in both the places

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-19 : 02:21:42

Still this query is running very slow as in the exists block PTH_PATCHASSESSMENTINSTALLED and viwpthmissing are the views and each view is union all of 10 tables.

please suggest any solution.

Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 12:27:34

quote:
Originally posted by vaibhavktiwari83

Still this query is running very slow as in the exists block PTH_PATCHASSESSMENTINSTALLED and viwpthmissing are the views and each view is union all of 10 tables.

please suggest any solution.

Vaibhav T

are you doing any aggregation inside view? is it partitioned?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources