find the part of stored procedure cause slow

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)

find the part of stored procedure cause slow

Author

Topic

fh200300us
Starting Member

28 Posts

Posted - 2008-01-28 : 14:37:22

Hi,
I need help to find in stored procedure causes run slow. Do you know any methods can catch the place cause the stored procudure running slow?

Thanks,

Jack

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-28 : 14:45:13

One method is:

Since you are looking for a specific "place" that is slow I am assuming the SP is a multi-statement SP. If so -
Go to a Query window and open the SP code. comment out the code "CREATE PROC...AS" code, declare any input parameters as local variables and set them to whatever values you called the SP with. Then step through the code running each statement manually. Perform any optimization as necessary. When you're satisfied, uncomment the "CREATE PROC...AS", comment out the local variables code and compile the SP.

Be One with the Optimizer
TG

fh200300us
Starting Member

28 Posts

Posted - 2008-01-28 : 15:18:52

Thanks TG. Can I ask you another question? In the stored procedure I create a table variable @temptable with one column districtID used to save districtID 1,2,3..30
There is condition in stored procedure that is tablea.DistictID in ( select districtID from @temptable). The stored procedureruns very slow but if I use hard code 1,2,3..30. It can run very fast. Do you know what reason cause this?

Thanks a lot,

Jack

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-28 : 17:13:46

quote:
Originally posted by fh200300us

Thanks TG. Can I ask you another question? In the stored procedure I create a table variable @temptable with one column districtID used to save districtID 1,2,3..30
There is condition in stored procedure that is tablea.DistictID in ( select districtID from @temptable). The stored procedureruns very slow but if I use hard code 1,2,3..30. It can run very fast. Do you know what reason cause this?

Thanks a lot,

Jack

I imagine what is happening is that the optimizer is choosing a bad plan when you don't hardcode the values. you can confirm by looking at the execution plan both ways. To correct, there are a few things you can try: Make sure the order of the tables listed in the FROM clause start with the most limiting table first. You can try INNER JOINING to the table variable rather than using an IN (subquery). You could try using a #temp table (with CREATE STATISTICS applied to it) rather than a table variable. Finally, if all else fails you try using an option clause at the end of the statement once you've ordered your table appropriately:
"OPTION (force order)"

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources