Query Optimization - 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)

Query Optimization

Author

Topic

nidabp
Starting Member

15 Posts

Posted - 2010-12-02 : 02:39:27

Hi,
I have the following data.

PMGUID PSGUID status3
x001 y001 active
x002 y002 Inactive
x001 x001 status3
x003 x001 active
y002 y002 status3
y001 y002 Inactive
y001 y001 active

I used the following query to get my required data.

select (select count(*)[total] from testquery where
(PMGUID = t.PMGUID OR PSGUID = t.PSGUID)),
(select count(*) from testquery where status = 'Inactive'
and (PMGUID = t.PMGUID or PSGUID = t.PSGUID)) [inactive],
(select count(PMGUID) from testquery where status = 'status3'
and (PMGUID = t.PMGUID or PSGUID = t.PSGUID)) [CNF] from testquery t

where
(t.PMGUID = 'x001' AND t.PSGUID = 'x001')

The result is 3,0,1

I am getting the count of the records with a particular status in a single query. But it takes 3 table scans to get the required output. Is it possible to achieve the result in a single table scan.

Thanx

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 02:56:45

Above Query can be rewrite like the below -


SELECT SUM(CASE WHEN ( t.PMGUID = 'x001' OR t.PSGUID = 'x001')  THEN 1 ELSE 0 END )
, SUM( CASE WHEN ( t.PMGUID = 'x001' OR t.PSGUID = 'x001') AND Status = 'Inactive' THEN 1 ELSE 0 END ) 'Inactive'
, SUM( CASE WHEN ( t.PMGUID = 'x001' OR t.PSGUID = 'x001') AND Status = 'status3' THEN 1 ELSE 0 END ) 'CNF'
FROM TestTable t

If you will match execution plan for both the query
then your query cost is 0.013
and My query cost is 0.003
80 - 20 %

Vaibhav T

If I cant go back, I want to go fast...

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 03:00:03

[code]SELECT COUNT(t1.PMGUID),
SUM(CASE WHEN t1.status = 'Inactive' THEN 1 ELSE 0 END),
SUM(CASE WHEN t1.status = 'status3' THEN 1 ELSE 0 END)
from testquery AS t
LEFT OUTER JOIN testquery AS t1
ON t1.PMGUID = t.PMGUID OR t1.PSGUID = t.PSGUID
where
(t.PMGUID = 'x001' AND t.PSGUID = 'x001')
[/code]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 03:08:18

quote:

Originally posted by Kristen

SELECT SUM(CASE WHEN PMGUID = t.PMGUID OR PSGUID = t.PSGUID THEN 1 ELSE 0 END),
       SUM(CASE WHEN status = 'Inactive' and (PMGUID = t.PMGUID or PSGUID = t.PSGUID) THEN 1 ELSE 0 END),
       SUM(CASE WHEN status = 'status3' and (PMGUID = t.PMGUID or PSGUID = t.PSGUID) THEN 1 ELSE 0 END)
from testquery AS t
 
where 
(t.PMGUID = 'x001' AND t.PSGUID = 'x001')

Will not give required output;
need to remove red part.

Vaibhav T

If I cant go back, I want to go fast...

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 03:23:00

Good point, I mis-read the question. If the OP had posted some DDL for a table and data I would have tested it, but I haven't got time today to build the DDL as well ...

Modified my original, to the concept that was in my mind. But that version is untested too and comes with no warranty!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 03:38:33

Thats perfect to produce the required result.
But eating performance.

Moreover My SQL and your Previous SQL without redpart (which is same) is more optimized.

Vaibhav T

If I cant go back, I want to go fast...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-02 : 04:14:38

Change the AND to OR and you're set

DECLARE	@Sample TABLE
	(
		PMGUID VARCHAR(8),
		PSGUID VARCHAR(8),
		[Status] VARCHAR(8)
	)

INSERT	@Sample
	(
		PMGUID,
		PSGUID,
		[Status]
	)
SELECT	'x001', 'y001', 'active' UNION ALL
SELECT	'x002', 'y002', 'Inactive' UNION ALL
SELECT	'x001', 'x001', 'status3' UNION ALL
SELECT	'x003', 'x001', 'active' UNION ALL
SELECT	'y002', 'y002', 'status3' UNION ALL
SELECT	'y001', 'y002', 'Inactive' UNION ALL
SELECT	'y001', 'y001', 'active'

SELECT	COUNT(*) AS Total,
	SUM(CASE WHEN [Status] = 'Inactive' THEN 1 ELSE 0 END) AS Inactive,
	SUM(CASE WHEN [Status] = 'Status3' THEN 1 ELSE 0 END) AS CNF
FROM	@Sample AS t 
WHERE	t.PMGUID = 'x001'
	OR t.PSGUID = 'x001'

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 04:16:24

My previous SQL does not have your tests for

( t.PMGUID = 'x001' OR t.PSGUID = 'x001')

if you leave the part out - so I don't think it would work

Your example presumably makes a single scan? That's fine, but its quite difficult to modify, so my code may be preferable for that reason (albeit not as performant). My OUTER JOIN probably makes 2 scans (depending on indexes on PMGUID / PSGUID I expect), but should be better than doing 3 separate "select count(*)" in the SELECT statement in the O/P's original.

I'm guessing that the O/P's actual query is more complex - particular as the sample data for a column called "xxGUID" does not look like a GUID. If there are lots of SELECT columns, ALL currently doing individual SELECT COUNT(*) then your approach (with more complex SELECT statement) will still take one scan? and mine 2 scans? but both are scalable regardless of the number of SELECT formulaic columns I think ??

... if O/P had posted DDL I c/would have tested that

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 04:17:00

Ah ... the DDL has arrived - thanks Peso

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 04:20:53

Code normalised to Peso's table



SET NOCOUNT ON
DECLARE	@Sample TABLE
	(
		PMGUID VARCHAR(8),
		PSGUID VARCHAR(8),
		[Status] VARCHAR(8)
	)

INSERT	@Sample
	(
		PMGUID,
		PSGUID,
		[Status]
	)
SELECT	'x001', 'y001', 'active' UNION ALL
SELECT	'x002', 'y002', 'Inactive' UNION ALL
SELECT	'x001', 'x001', 'status3' UNION ALL
SELECT	'x003', 'x001', 'active' UNION ALL
SELECT	'y002', 'y002', 'status3' UNION ALL
SELECT	'y001', 'y002', 'Inactive' UNION ALL
SELECT	'y001', 'y001', 'active'

PRINT 'Peso'
SELECT	COUNT(*) AS Total,
	SUM(CASE WHEN [Status] = 'Inactive' THEN 1 ELSE 0 END) AS Inactive,
	SUM(CASE WHEN [Status] = 'Status3' THEN 1 ELSE 0 END) AS CNF
FROM	@Sample AS t 
WHERE	t.PMGUID = 'x001'
	OR t.PSGUID = 'x001'

PRINT 'vaibhavktiwari83'
SELECT SUM(CASE WHEN ( t.PMGUID = 'x001' OR t.PSGUID = 'x001')  THEN 1 ELSE 0 END )
, SUM( CASE WHEN ( t.PMGUID = 'x001' OR t.PSGUID = 'x001') AND Status = 'Inactive' THEN 1 ELSE 0 END ) 'Inactive'
, SUM( CASE WHEN ( t.PMGUID = 'x001' OR t.PSGUID = 'x001') AND Status = 'status3' THEN 1 ELSE 0 END ) 'CNF'
FROM @Sample t

PRINT 'Kristen'
SELECT COUNT(t1.PMGUID),
       SUM(CASE WHEN t1.status = 'Inactive' THEN 1 ELSE 0 END),
       SUM(CASE WHEN t1.status = 'status3'  THEN 1 ELSE 0 END)
from @Sample AS t
     LEFT OUTER JOIN @Sample AS t1
         ON t1.PMGUID = t.PMGUID OR t1.PSGUID = t.PSGUID
where 
(t.PMGUID = 'x001' AND t.PSGUID = 'x001')

SET NOCOUNT OFF

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 04:30:19

Yeah thats true If filter of GUID values is changing then it will be difficult to modify
then Your SQL with Where Clause ( t.PMGUID = 'x001' OR t.PSGUID = 'x001') will work fine.

1. Your SQL with Where Clause as mentioned above,
2. Peso's SQL
3. Mine SQL
all are same in performance I suppose even I tested.

but Your SQL with OUTER JOIN is eating more performance then all the above three.

This is comparative test I have done.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources