help optimizing query - 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)

help optimizing query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-23 : 03:50:22

I have the following query that works fine but i'm wondering if there is a way to optimize it better as when I analyze through sql profiler it is at the top of the list of using the cpu

SELECT DISTINCT site, d, (SELECT COUNT(id) FROM anP aPV2 WHERE aPV2.confirmed=1 and aPV2.stage=2 and aPV2.inserted=0 and aPV2.site=aPV1.site and aPV2.d>=aPV1.d and aPV2.d<=aPV1.d) AS mycount FROM anP aPV1 WHERE confirmed=1 AND stage=2 AND inserted=0 ORDER BY site,d

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 03:56:50

This is your query, right?

SELECT		DISTINCT
		aPV1.site,
		aPV1.d,
		(
			SELECT	COUNT(aPV2.id)
			FROM	anP AS aPV2
			WHERE	aPV2.confirmed = 1
				AND aPV2.stage = 2
				AND aPV2.inserted = 0
				AND aPV2.site = aPV1.site
				AND aPV2.d >= aPV1.d
				AND aPV2.d <= aPV1.d
		) AS mycount
FROM		anP AS aPV1
WHERE		aPV1.confirmed = 1
		AND aPV1.stage = 2
		AND aPV1.inserted = 0
ORDER BY	aPV1.site,
		aPV1.d

Look at the condition depicted here

AND aPV2.d >= aPV1.d
AND aPV2.d <= aPV1.d

Column aPV2.d cannot be both less than and greater than aPV1.d!
You can shorten down that to

AND aPV2.d = aPV1.d

When that is done, your query now look like

SELECT		DISTINCT
		aPV1.site,
		aPV1.d,
		(
			SELECT	COUNT(aPV2.id)
			FROM	anP AS aPV2
			WHERE	aPV2.confirmed = 1
				AND aPV2.stage = 2
				AND aPV2.inserted = 0
				AND aPV2.site = aPV1.site
				AND aPV2.d = aPV1.d
		) AS mycount
FROM		anP AS aPV1
WHERE		aPV1.confirmed = 1
		AND aPV1.stage = 2
		AND aPV1.inserted = 0
ORDER BY	aPV1.site,
		aPV1.d

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 03:59:35

Now you can see that you can replace whole thing with simple

SELECT		Site,
		d,
		COUNT(*) AS MyCount
FROM		anP
WHERE		Confirmed = 1
		AND Stage = 2
		AND Inserted = 0
GROUP BY	Site,
		d
ORDER BY	Site,
		d

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-23 : 05:03:44

thanks - that seems much better

i assume the same can be done with this

select distinct site,d,(SELECT COUNT(id) FROM an aPV2 WHERE ((apv2.status!='100%' and apv2.status!='repeat') or apv2.status is null) and apv2.stage=0 and aPV2.site=aPV1.site and aPV2.d>=aPV1.d and aPV2.d<=aPV1.d) AS mycount from an apv1 where ((status!='100%' and status!='repeat') or status is null) and stage=0 order by d,site

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 05:08:50

Yes.

SELECT		Site,
		d,
		COUNT(*) AS MyCount
FROM		an
WHERE		COALESCE(Status, '') NOT IN ('100%', 'repeat')
		AND Stage = 0
GROUP BY	Site,
		d
ORDER BY	d,
		Site

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 05:09:34

If ID columns can be NULL, you should replace COUNT(*) with COUNT(ID) for both suggestions.

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-23 : 05:37:29

thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts