SQL Server Performance Forum – Threads Archive
Execution Time Differences in Backend to Frontend
I have a SP,If i run the sp from QA it takes 4:33(4minutes and 33secs),the same spif run from vb.net code it takes 23 minutes.I checked the tables which is used in the sp for indexing but everthing is proper in the sp.And also i checked with vb6.0 code but still it takes the 23 minutes to execute from frontend. Kindly help me…thanks in advance……..for last 5 days i am working on this..but i
couldn’t able to find the solution… geesathishkumar
can you run a trace to find what other activities happens when you fire query from vb.
Are there any networking issue?
have tried firing sp from client QA, the machine from where vb.net fires sp
Thanks ranjitjain for your interest Yes,I tried the sp from the client QA where the vb.net codes is resides. I checked the same sp in QA and VB.net in the Same machine but i got the execution time differences that is,from QA it takes 4:33(4minutes and 33 seconds),in vb.net code it takes 23 minutes to execute the same sp in the same machine.
i wont thing this is a network problem, because i am running from the same machine,if its the problem then the sp work run within 4:33 in the QA,am i right? Thanks again for your interest. I am sitting with this problem for around 8 days….even i tested with vb6.0 ado code but no use ….i am getting the same result that is 23 minutes.
geesathishkumar
Is the ADO code just making a connection to the back-end, and starting a stored procedure? Or is there any processing going on in the client app? What kind of parameters (if any) are you uploading? At the start of any stored procedure, it is recommended to add SET NOCOUNT ON, otherwise row counts will be sent to the client app, which is known to have a bad effect on response time.
Thanks adriaan i used everthing which you have mentioned above… I am using ado in the vb code…. it just makes the connection(oledb) to the sqlserver2000
,using command object i am creating 7 parameters
and executing the sp using the command’s object executenonquery.
SET NOCOUNT ON is also there in my sp.
geesathishkumar
Then is your procedure returning milliopns of rows, or big blobs? And what type of ADO connection is it – there are lots of settings about servier-side vs client-side, etc. etc. Sorry if I’m not an expert on ADO.
i’m not sure but can try installing mdac 2.81 for latest oledb provider if its not there.
sp won’t return any rows..it will manipulate with 5146 rows,that is insert,update and calculating compond interest…like wise….
I used the aduseserver cursor in my ado code…but it doesn’t gives the result… and also i used aduseclient cursor…but no use of it…..
i am confused to find the solution for this problem. i am awaiting for better results…
geesathishkumar
Can you catch both execution plans?
no…i how to catch that …. but tried in profiler with the stored procedure event class..
for back end and front end…
And also one more doubt that is, i checked the sp in the profiler with out adding the event class in the profiler in this case it takes 4 minutes and 33 seconds to execute the sp..but if i inculde the stored procedure event class in the profiler,it takes 23 minutes in the backend itself..am i wondering about this,,,so much confused for the activites of the sqlserver2000….
thanks,
geesathishkumar
Is your client machine’s ODBC logging/trace turned on?
sorry,we are using sqloledb,i don’t think odbc logging creates this problem. geesathishkumar
]]>