SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Slow in the application fast in ssms sql server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

demons
Starting Member

Vietnam
6 Posts

Posted - 10/30/2013 :  05:20:16  Show Profile  Reply with Quote
Hi all
I 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 solve
Thanks

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 10/30/2013 :  07:45:13  Show Profile  Reply with Quote
quote:
Originally posted by demons

Hi all
I 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 solve
Thanks


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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/30/2013 :  07:50:39  Show Profile  Reply with Quote
<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 Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000