Thursday, August 25, 2011

TSQL Stored Procedure faster from SSMS than the application

No, I have no quick fix for this because SQL Server is not about magic and there is a reason why there are guys that work 100% of the time as SQL developers.

You have to ensure your queries are optimized and you know what query plan will be used when they are run. This is not about Java or .NET problems because your SQL Server Management Studio (SSMS) is "apparently" running the query 10 or 20 times faster than the application. It is indeed about your SQL code optimizations.

So if you have this problem then run the stored procedure using something like:
SET ARITHABORT OFF
EXEC sp_custom_procedure 'param1', 2, 'param3'
GO

Then run it again like:
SET ARITHABORT ON
EXEC sp_custom_procedure 'param1', 2, 'param3'
GO

You should get a slower response for the first and that will only tell you that your stored procedure is using a non efficient query plan. Now it is time to do your homework reading the previous link and make your stored procedure run with a custom well predefined fast and efficient query plan (of course as fast and efficient as you can)

No comments:

Followers