Performance issues on several SUM(CASE)

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)

Performance issues on several SUM(CASE)

Author

Topic

jobejufranz
Starting Member

33 Posts

Posted - 2009-10-11 : 23:19:13

Hi Guys,

I just want to have some enlightenment about my query, any suggestions/comments is highly appreciated since i am not that strong in SQL Statements.

I have this existing query on my asp.net 2.0 page that includes several SUM(CASE WHEN...(about 25 to be exact) in a single SELECT statement. Now, i noticed that when I add another SUM(CASE WHEN... the query gets slower and slower especially if several users are also accessing the SQL Server.

Now my question is that how bad is it to use such several SUM(CASE WHEN... in a single SELECT statement? Second question, is there any other possible optimization that can be done on this query? And lastly, i am trying to experiment getting all raw data, dump it on TEMP Table and do the multiple SUM(CASE WHEN... statement there, will this do the trick?

Again, any comments/suggestions is highly appreciated. Million thanks in advanced guys.

jobejufranz
Starting Member

33 Posts

Posted - 2009-10-11 : 23:20:59

And one more thing, the above multiple SUM(CASE WHEN statement comes from a join 2 table.

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-12 : 01:44:01

We'll have to see the query.

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

jobejufranz
Starting Member

33 Posts

Posted - 2009-10-12 : 02:16:01

Thanks. Below is the Query:


SELECT 
	(DATEDIFF(DAY, '20010101', TrxDate)/7) AS 'Week', 
	SUM(CASE WHEN TrxBranch='BRANCH1' THEN TrxDiscPrc END) AS 'BRN1',...	
	SUM(TrxDiscPrc) AS 'TOTAL' 
FROM 
	Stk WITH (NOLOCK) INNER JOIN Trx WITH (NOLOCK) ON
		Stk.Code=Trx.Code
WHERE 	
	Stk.StkDisabled<>'YES' AND 
	Stk.StkCat='Category1' AND 
	Stk.StkBrnd IN ('Brand1','Brand2','Brand3') AND 
	Trx.TrxDate>='1/1/2008' AND Trx.TrxDate<'5/1/2008' 
GROUP BY 
	(DATEDIFF(DAY, '20010101', TrxDate)/7) 
ORDER BY 
	(DATEDIFF(DAY, '20010101', TrxDate)/7) DESC;

Thanks again.

jobejufranz
Starting Member

33 Posts

Posted - 2009-10-12 : 20:33:08

Any possible solution guys?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-13 : 01:18:35

Two things

1) StkDisabled <> 'Yes'
2) StkBrand IN ('Brand1', 'Brand2', 'Brand3')

will force your query to scan (rather than seek) your table.

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

jobejufranz
Starting Member

33 Posts

Posted - 2009-10-13 : 02:36:26

Peso thanks for the reply.

Any alternative in dealing with this things?

Will "=" any faster than IN?

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-13 : 02:52:36

No. The IN part will be translated to three OR's.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-13 : 02:59:46

Try this

SELECT		DATEDIFF(DAY, '20010101', TrxDate) / 7 AS 'Week', 
		SUM(CASE WHEN TrxBranch = 'BRANCH1' THEN TrxDiscPrc END) AS [BRN1],
		SUM(TrxDiscPrc) AS [TOTAL]
FROM		Stk WITH (NOLOCK)
INNER JOIN	Trx WITH (NOLOCK) ON Trx.Code = Stk.Code
			AND Trx.TrxDate >= '1/1/2008'
			AND Trx.TrxDate < '5/1/2008' 
WHERE 		Stk.StkDisabled = 'NO'
		AND Stk.StkCat = 'Category1'
		AND Stk.StkBrnd LIKE 'Brand[123]'
GROUP BY	DATEDIFF(DAY, '20010101', TrxDate) / 7 
ORDER BY	DATEDIFF(DAY, '20010101', TrxDate) / 7 DESC

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

jobejufranz
Starting Member

33 Posts

Posted - 2009-10-13 : 03:09:20

Thanks for the reply again.

Few things about your suggested statement,

1) How about if Stk.StkBrnd has a several diff Brand
ref: (Stk.StkBrnd LIKE 'Brand[123]')

2) Does providing the date condition in JOIN do any faster than
providing the condition under WHERE?

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-13 : 03:13:51

1) IN would your best bet
2) Maybe. It depends on your indexes and amount of data.

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

jobejufranz
Starting Member

33 Posts

Posted - 2009-10-13 : 03:40:02

Thanks again.

Around 3600 rows are being returned if i won't use SUM(CASE WHEN.. do you think this is a lot in a real-world data?

If the index is the main culprit, then i have to discuss this with the DBA, i'm just a data miner.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources