stored procedure is fast, but slow from code
-
I have got a stored procedure which executes very fast in Sql Server Management Studio, but when I call it with the same parameters from C#, it executes slower and sometimes even throws timeout exception. Does anybody know the reason? hint: I have noticed that when I comment out one of the conditions in where clause, the query executes very fast both on SSMS and C#. Another point is that when I remove the comment and put the condition back in the query (which causes the stored procedure to compile again) the query execution becomes a little faster in C#, but after a while it becomes slow again (all with the same parameters)
-
I have got a stored procedure which executes very fast in Sql Server Management Studio, but when I call it with the same parameters from C#, it executes slower and sometimes even throws timeout exception. Does anybody know the reason? hint: I have noticed that when I comment out one of the conditions in where clause, the query executes very fast both on SSMS and C#. Another point is that when I remove the comment and put the condition back in the query (which causes the stored procedure to compile again) the query execution becomes a little faster in C#, but after a while it becomes slow again (all with the same parameters)
I think that for anybody to stand a chance of answering your question, you should edit your post and include the stored procedure and the c# code that calls it. Please do NOT reply to me, this is just some advice to help you get an answer. :)
Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”
-
I have got a stored procedure which executes very fast in Sql Server Management Studio, but when I call it with the same parameters from C#, it executes slower and sometimes even throws timeout exception. Does anybody know the reason? hint: I have noticed that when I comment out one of the conditions in where clause, the query executes very fast both on SSMS and C#. Another point is that when I remove the comment and put the condition back in the query (which causes the stored procedure to compile again) the query execution becomes a little faster in C#, but after a while it becomes slow again (all with the same parameters)
May I suggest ... In SQL Server Studio Manager, execute your stored procedure, but under the menu "Query" choose the option to "Display Esitmated Execution Plan". This will give you great insight into how the SQL server is going to retrieve your data. You want to avoid large "scans" of tables, try to take advantage of indexes. Also, you may want to look into SQL Profiler and watch what is going on while your code executes.
-
May I suggest ... In SQL Server Studio Manager, execute your stored procedure, but under the menu "Query" choose the option to "Display Esitmated Execution Plan". This will give you great insight into how the SQL server is going to retrieve your data. You want to avoid large "scans" of tables, try to take advantage of indexes. Also, you may want to look into SQL Profiler and watch what is going on while your code executes.
Thanks for your advice, All the indexes are being used (index seek) and no table scan is happening. That's why the query gets executed very fast in Sql Server. That's why I wonder what the difference is when I execute it from code with the same parameters.
-
Thanks for your advice, All the indexes are being used (index seek) and no table scan is happening. That's why the query gets executed very fast in Sql Server. That's why I wonder what the difference is when I execute it from code with the same parameters.
I would now look into the SQL Profiler, because you can now monitor how your application is interacting with the database. Things to check on the client side would be network traffic, memory usage and CPU usage while this is executing. Sounds like a fun challenge. :cool:
-
I have got a stored procedure which executes very fast in Sql Server Management Studio, but when I call it with the same parameters from C#, it executes slower and sometimes even throws timeout exception. Does anybody know the reason? hint: I have noticed that when I comment out one of the conditions in where clause, the query executes very fast both on SSMS and C#. Another point is that when I remove the comment and put the condition back in the query (which causes the stored procedure to compile again) the query execution becomes a little faster in C#, but after a while it becomes slow again (all with the same parameters)
I found something recently that discussed arith_abort (I think, the link is at work) which is set on by default, setting it to off apparently made a difference to some procs that showed this behavior. I have seen this behavior and after doing all the optimising possible the proc was still slow, that's when I started chasing down the more esoteric suggestions. If I find the link I will post it Monday....
Never underestimate the power of human stupidity RAH