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.
| Author |
Topic |
|
demons
Starting Member
6 Posts |
 Posted - 2013-10-30 : 05:20:16
|
| Hi allI have a problem with the store procedure.When i execute on SSMS SQL Server, it take 30s to finish, but when execute by c# and get the result to client, it takes 10-20 minutes.Please explain more and how to solveThanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-30 : 07:45:13
|
quote:
Originally posted by demons
Hi allI have a problem with the store procedure.When i execute on SSMS SQL Server, it take 30s to finish, but when execute by c# and get the result to client, it takes 10-20 minutes.Please explain more and how to solveThanks
Use Trace or Profiler to examine the query coming into SQL Server, and see how much time it is taking.If the query itself is taking more than when run from SSMS, see if the parameters are the same in both cases. If they are not, run both using the same parameters and compare. There should not be any significant difference.If the query is not what is taking up the time, then it has to be the data flow or the client application's processing of the data. The amount of data being passed back and forth may be relevant. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-30 : 07:50:39
|
| <in addition to what James has said>:couple possibilities:1. your application code is taking a long time to load a control or apply some logic/rules/sorting or something to the results as the stream in.2. when called from the application sql is using a bad execution plan. You can end up with different cached plans because of small changes to the connection settings. Often something like ADO.net will apply some SET options that are different from your SSMS connection and that means a different plan must be devised. It is possible that one of those plans (in this case the one from the application) is a bad plan. You can try re-compiling the SP to see if that makes a difference. If it does then one klugy way to fix this is compile the SP with WITH RECOMPILE. Because this is a fairly long running SP anyway the couple seconds needed to create a plan will not hurt too much. The longer term solution is to re-work the SP so that choosing the right plan is not so ambiguous for sql server.Be One with the OptimizerTG |
|
|
|
|
|
|
Slow in the application fast in ssms sql server