Need Help on performance Improvement

Question

Post reply

Need Help on performance Improvement

yatish.patil

Ten Centuries

Points: 1268
More actions
November 26, 2010 at 3:55 am

#231929

Hello,
I need help on performance Improvement, I have large database where in i need to find the average days spend by one entity record. I have tried different ways, like using temp tables, using SQL functions like row_number but the performance is still very slow.
can anyone guide me for performance Improvement. I have attached test plan of the code that is taking more time.

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

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

Brigadur SSCrazy Points: 2097 More actions · Answer 1

A first quick look on the query plan provided shows that you have two heavy sort operations on:

[StudentTest].[dbo].[StudentHistory].StudentId Ascending,

[StudentTest].[dbo].[StudentHistory].StudentHistoryId Descending

These take 48% of the resource. You should have an index on that table:

CREATE UNIQUE NONCLUSTERED INDEX IX_StudentHistory_StudentId_StudentHistoryId

ON dbo.StudentHistory

(

StudentId ASC,

StudentHistoryId DESC

);

I suspect StudentId, StudentHistoryId together is unique, if not, you have to omit the UNIQUE clause.

Cheers

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 2

You are using the cte StudHistory multiple times , in this case this is hurting you.

Contrary to popular belief , cte are not spooled or cached in any way shape or form.

You can see that in the plan there are multiples evaluations of it.

I think you will make things easier is you manually spool the cte into a temp table first off then use that.

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

yatish.patil (11/26/2010)
Hello,
I need help on performance Improvement, I have large database where in i need to find the average days spend by one entity record. I have tried different ways, like using temp tables, using SQL functions like row_number but the performance is still very slow.
can anyone guide me for performance Improvement. I have attached test plan of the code that is taking more time.

I agree with both of the previous posts especially Dave's on the subject of cte's being executed once for each time called.

This is a classic example of where doing it all in one query isn't the best idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 4

Some other thoughts, you're trying to find the average number of days but you're returning 146,000 rows. Do you really need that much data? Reducing the amount moved around can help performance. And those scans on 1.7 million rows, those are killing you. Again, better filters in place to reduce the data being moved around could make a huge difference.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

yatish.patil Ten Centuries Points: 1268 More actions · Answer 5

Hello,

I tried to use temp table instead of CTE but the performance got even bad after using temp table instead of CTE. I will explain the inner block logic

consider below example

Historyid, id, changedate, subjectid, nextchangedate

===========================================

1,1, '06/30/2010',1,'07/01/2010'

2,1, '07/01/2010',1,'07/15/2010'

3,1, '07/15/2010',2,'07/31/2010'

4,1, '07/31/2010',3,'08/01/2010'

5,1, '08/01/2010',1,'08/31/2010'

6,1, '08/31/2010',1,'10/15/2010'

6,1, '10/15/2010',1,null

in this example i am trying to retrieve

1,1, '06/30/2010',1,'07/01/2010'

2,1, '07/01/2010',1,'07/15/2010'

3,1, '07/15/2010',2,'07/31/2010'

4,1, '07/31/2010',3,'08/01/2010'

and exclude all those records with subjectid 1 and nextchangedate is null until the old subject id in this example 3 at row no 4.

can anyone tell me if there is any chance to improve the inner code block.

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 6

Sorry , lost track of this one,

There does seem room for improvement here, Im not 100% clear on your requirements though.

Can you try restating and clarifying ?

Clear Sky SQL
My Blog[/url]

steven.malone Say Hey Kid Points: 688 More actions · Answer 7

Hello,
I tried to use temp table instead of CTE but the performance got even bad after using temp table instead of CTE. I will explain the inner block logic

After creating and populating the temp table, look at the query plan for the next step.

I am guessing adding an index to the temp table would help.