Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of my stored procedure has long execution time (average around 4 to 7 minutes).

Now I trying to tweak it an make it run faster.

I am looking at execution plan and two things I see that using most of percentage. First is 68% of "Clustered Index Scan" of one main tables for reporting, This table has primary key of two columns and 2000000 records inside.

Second most demanding task is 26% of "Merge join" (left outer join), this is connection between already mentioned table and other with foreign key to one of two columns of primary key in first table.

I checked Index physical statistic and index for these tables are in good conditions.

What can I do to increase performance of this stored procedure.

Do I need to make new index on this tables.

Does set transaction isolation level read uncommitted statement help to improve performance ?

share|improve this question

migrated from stackoverflow.com Feb 8 '12 at 15:08

This question came from our site for professional and enthusiast programmers.

    
Than You on offered help, But I am kind a disgraced asking always other to do my jobs and tasks, If I put query on SO probably someone is going to solve that and that is not way things should be done. I more like to learn about problem and way it can be solved. That's why I was not detailed on asking question. –  adopilot Nov 9 '09 at 12:15

4 Answers 4

up vote 4 down vote accepted

If you post schema and query etc, that would be useful, in addition to the other 3 answers.

Some links anyway, from Simple-Talk. Good stuff.

share|improve this answer

Your approach is correct: A clustered index scan basically loops through the whole table, which is something you want to avoid. Look at the details of the clustered index scan: There should be a WHERE predicate telling you which field is searched by the scan. Make sure that there is an index on this field (or these fields, if there is more than one), then try again.

share|improve this answer
    
But it shouldn't even be a clustered index scan. It should be a clustered index SEEK. –  erikkallen Nov 7 '09 at 20:15

That first query that causes 68% of the time spent - what does it look like? What does your table and its primary key look like?

If you find a way to eliminate that clustered index scan, things should be a lot better - but in order to help you find the problem, we need some more info! Please edit your original question and add those things to them - thanks!

share|improve this answer

You are doing a clustered index scan which basically means read in the whole table. How wide is your clustered index? Based on the logic of the query you are trying to perform, do you think you should be reading the whole table? This might be true if you want to group / sum on all of history in the table, but it is much more likely that your intention is to only look at a portion of the table. If the latter is true, please tell us more about the table, the clustered index columns, and the query you are trying to satisfy.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.