stored proc takes too long time for processing

[itangelmanoj]

hi,
i have 1 stored procedure in database, that fetches records from table that has big number of records. it does it work fine, i get the data in 2-3 sec. i copied the stored proc and created the another one with different name that does exactly the same. but when i run the new stored proc it takes long time 15 sec or so. Both the stored proc are exctly the same. i am not able to understand the reason of this behaviour.

please help me out.

thanking you in advance

 

[jhonty]

HI,

It seems that you have changed the search condition in the proc? Use SET SHOWPLAN ON and then SET NOEXEC ON and exec the proc. , check if it is doing table scan, correct the where condition so that it will use proper index on column then OFF both SET option command, and then reexecute the procedure.

All the best.

 

[itangelmanoj]

thanks fopr your reply.

but i haven't chenged the stored procedure a bit. It is exactly the same.

please let me know, what should be done in that case.

thanks,
manoj

 

[JMCraig]

jhonty's advice is still good. If there's a difference in performance, it is clearly due to a different query plan.

One thing I would try (when you find that it's doing a table scan rather than an index scan) is to update the statistics on the table in question; drop and recreate the stored procedure if it doesn't improve after the update statistics call.

Best of luck!

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[RayPeck]

You have to find out what your stored procedure is doing before you can see what to fix. Even though its the "same" procedure as the other, it is obviously doing something different. That's why they're asking you to do:

set showplan on
go
set statistics io on
go
exec proc_name
go

I would do this for BOTH procedures and then compare the results--see what it's doing differently.

Ray