performance improvement

Question

Post reply

performance improvement

sushil_dwid

Ten Centuries

Points: 1241
More actions
October 12, 2015 at 1:10 am

#308788

Performance issue on Query. Here is query which i ran and it takes around 7-8 min to give the output. Could you please suggest the alternate way to write it.

SELECT COUNT,MESS,

count(distinct(CASE WHEN EVTIME=@D1 THEN EVENTUID ELSE null END)) AS 'D1'

,count(distinct(CASE WHEN EVTIME=@D2 THEN EVENTUID ELSE null END)) AS 'D2'

,count(distinct(CASE WHEN EVTIME =@D3 THEN EVENTUID ELSE null END)) AS 'D3'

,count(distinct(CASE WHEN EVTIME =@D4 THEN EVENTUID ELSE null END)) AS 'D4'

,count(distinct(CASE WHEN EVTIME =@D5 THEN EVENTUID ELSE null END)) AS 'D5'

,count(distinct(CASE WHEN EVTIME =@D6 THEN EVENTUID ELSE null END)) AS 'D6'

,count(distinct(CASE WHEN EVTIME =@D7 THEN EVENTUID ELSE null END)) AS 'D7'

,count(distinct(CASE WHEN EVTIME =@D8 THEN EVENTUID ELSE null END)) AS 'D8'

,count(distinct(CASE WHEN EVTIME =@D9 THEN EVENTUID ELSE null END)) AS 'D9'

,count(distinct(CASE WHEN EVTIME =@D10 THEN EVENTUID ELSE null END)) AS 'D10'

,count(distinct(CASE WHEN EVTIME =@D11 THEN EVENTUID ELSE null END)) AS 'D11'

,count(distinct(CASE WHEN EVTIME =@D12 THEN EVENTUID ELSE null END)) AS 'D12'

,count(distinct(CASE WHEN EVTIME =@D13 THEN EVENTUID ELSE null END)) AS 'D13'

,count(distinct(CASE WHEN EVTIME =@D14 THEN EVENTUID ELSE null END)) AS 'D14'

FROM DBO.#TEMP C WITH(NOLOCK)

GROUP BY COUNT,MESS

ORDER BY 1,2

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23731 More actions · Answer 1

There are a few things...

#1) There's no need to use the NOLOCK hint here. It's a temp table which means you have exclusive access to it.

#2) Why are are you doing distinct counts? The name,"EVENTUID", implies that this column already has unique values.

#3) The biggie... Index your temp table. COUNT & MESS should be the first two key columns... If the distinct is necessary, then EVENTUID should be the 3rd key column. If not, then it can be an "included" column.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

sushil_dwid (10/12/2015)

Performance issue on Query. Here is query which i ran and it takes around 7-8 min to give the output. Could you please suggest the alternate way to write it.

SELECT COUNT,MESS,

count(distinct(CASE WHEN EVTIME=@D1 THEN EVENTUID ELSE null END)) AS 'D1'

,count(distinct(CASE WHEN EVTIME=@D2 THEN EVENTUID ELSE null END)) AS 'D2'

,count(distinct(CASE WHEN EVTIME =@D3 THEN EVENTUID ELSE null END)) AS 'D3'

,count(distinct(CASE WHEN EVTIME =@D4 THEN EVENTUID ELSE null END)) AS 'D4'

,count(distinct(CASE WHEN EVTIME =@D5 THEN EVENTUID ELSE null END)) AS 'D5'

,count(distinct(CASE WHEN EVTIME =@D6 THEN EVENTUID ELSE null END)) AS 'D6'

,count(distinct(CASE WHEN EVTIME =@D7 THEN EVENTUID ELSE null END)) AS 'D7'

,count(distinct(CASE WHEN EVTIME =@D8 THEN EVENTUID ELSE null END)) AS 'D8'

,count(distinct(CASE WHEN EVTIME =@D9 THEN EVENTUID ELSE null END)) AS 'D9'

,count(distinct(CASE WHEN EVTIME =@D10 THEN EVENTUID ELSE null END)) AS 'D10'

,count(distinct(CASE WHEN EVTIME =@D11 THEN EVENTUID ELSE null END)) AS 'D11'

,count(distinct(CASE WHEN EVTIME =@D12 THEN EVENTUID ELSE null END)) AS 'D12'

,count(distinct(CASE WHEN EVTIME =@D13 THEN EVENTUID ELSE null END)) AS 'D13'

,count(distinct(CASE WHEN EVTIME =@D14 THEN EVENTUID ELSE null END)) AS 'D14'

FROM DBO.#TEMP C WITH(NOLOCK)

GROUP BY COUNT,MESS

ORDER BY 1,2

COUNT(DISTINCT...) suggests that your temp table could be constructed differently for better results. During preparation of your temp table, try this level of aggregation:

GROUP BY Count, Mess, EVTIME, EVENTUID

then for each output aggregate, something like this:

SUM(CASE WHEN EVTIME =@D13 THEN 1 ELSE 0 END) AS 'D13'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

ScottPletcher

SSC Guru

Points: 100262

October 12, 2015 at 3:22 pm

#1832886

When you create the temp table, i.e before it's loaded, cluster it on ( COUNT, MESS ). A nonclustered index will not be efficient. That will speed up your query considerably.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

sushil_dwid (10/12/2015)

Performance issue on Query. Here is query which i ran and it takes around 7-8 min to give the output. Could you please suggest the alternate way to write it.

SELECT COUNT,MESS,

count(distinct(CASE WHEN EVTIME=@D1 THEN EVENTUID ELSE null END)) AS 'D1'

,count(distinct(CASE WHEN EVTIME=@D2 THEN EVENTUID ELSE null END)) AS 'D2'

,count(distinct(CASE WHEN EVTIME =@D3 THEN EVENTUID ELSE null END)) AS 'D3'

,count(distinct(CASE WHEN EVTIME =@D4 THEN EVENTUID ELSE null END)) AS 'D4'

,count(distinct(CASE WHEN EVTIME =@D5 THEN EVENTUID ELSE null END)) AS 'D5'

,count(distinct(CASE WHEN EVTIME =@D6 THEN EVENTUID ELSE null END)) AS 'D6'

,count(distinct(CASE WHEN EVTIME =@D7 THEN EVENTUID ELSE null END)) AS 'D7'

,count(distinct(CASE WHEN EVTIME =@D8 THEN EVENTUID ELSE null END)) AS 'D8'

,count(distinct(CASE WHEN EVTIME =@D9 THEN EVENTUID ELSE null END)) AS 'D9'

,count(distinct(CASE WHEN EVTIME =@D10 THEN EVENTUID ELSE null END)) AS 'D10'

,count(distinct(CASE WHEN EVTIME =@D11 THEN EVENTUID ELSE null END)) AS 'D11'

,count(distinct(CASE WHEN EVTIME =@D12 THEN EVENTUID ELSE null END)) AS 'D12'

,count(distinct(CASE WHEN EVTIME =@D13 THEN EVENTUID ELSE null END)) AS 'D13'

,count(distinct(CASE WHEN EVTIME =@D14 THEN EVENTUID ELSE null END)) AS 'D14'

FROM DBO.#TEMP C WITH(NOLOCK)

GROUP BY COUNT,MESS

ORDER BY 1,2

7-8 minutes for how many rows?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

sushil_dwid Ten Centuries Points: 1241 More actions · Answer 5

sushil_dwid

Ten Centuries

Points: 1241

October 14, 2015 at 11:42 pm

#1833368

I can not create cluster because it has duplicate records

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sushil_dwid (10/14/2015)

I can not create cluster because it has duplicate records

Clustered indexes don't have to be unique.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286562 More actions · Answer 7

One other suggestion. Not going to make a difference for performance but you should get out of the habit of ordering by ordinal position. You should always use the column name. If your columns change and you forget to change the order by the rows will be returned in a different order.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/