CODEWITHSUNDEEP
sqlsql-server
GorkemHalulu
GorkemHalulu

Reputation: 3075

Huge performance difference between exec of SP and running the statements of sp is solved by alter query (without any change) of the same SP

Today I run into an interesting problem that no one in our 8-person software team have ever seen.

EXEC FirmSearch 1, 1

That simple sp took 45 sec. to execute in every time. Running the exact same statements inside the stored procedure took less than 1 sec. I create FirmSearchV2 with same functionality. Then:

EXEC FirmSearchV2 1, 1

FirmSearchV2 took less than 1 sec. I tried to execute modify query (without any change) of FirmSearch sp and the problem disappeared. Now FirmSearch executes in less than 1 sec. I am using MsSql 2008.

What are the possible reasons of this problem?

Upvotes: 1

Views: 228

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

I don't know sql-server so this may be completely wrong, but in another DBMS stored procedures are compiled with the statistics that exists when the sp are created. That is, if data changes a lot the access plan compiled for the sp may be sub-optimal. This may explain why a recreate of the sp improves performance. Check if there are any utility that can recompile the sp without having to recreate it.

Upvotes: 2

Related Questions