September 8, 2004 at 9:31 am
Hi All,
I observed that SQL Profiler was showing multiple executions of the same Stored procedure when executed thru an ADO.Net Client where as it was showing only 1 execution of the same SP when using the SQL Server Client.
Exec sp_executeSql N'exec getMyResults...'
Exec sp_executeSql N'exec getMyResults...'
Exec sp_executeSql N'exec getMyResults...'
Exec sp_executeSql N'exec getMyResults...'
Exec sp_executeSql N'exec getMyResults...'
...
Any Ideas
I had to re-type this since I overwrote the original post sorry...
Manoj S.
September 9, 2004 at 6:44 am
Maybe it's because you're using a Dynamic query which can't be cached? I'm not sure, I've moved onto ADO.NET 1.1 for the past year and soon 2.0. Try just putting the whole SQL Statement in code, and then trying maybe putting it into a Stored Proc or something to see if it does the same thing.
September 9, 2004 at 6:59 am
Hi All,
Thanks for the reply... The Performance issue was due to the fact that an underlying table / index was corrupted...
The observation of SQL Profiler showing multiple executions of the same query seems to be because
1. This procedure was making 10 sub procedure calls...
2. when executed thru ADO.Net the profiler is not able to show the underlying calls..
whereas when executing from the Sql Server Client it is able to show the underlying Sql Calls in this case 10 sub calls...
So in reality it was making only 1 call and it was attempting to display in the profiler all the subsequent stmts from there...
I tested this atleast 10 times with different procs and it seems to be consistent...
But as I said the main issue with the performance was due to an underlying table and when we saw the multiple calls we were concerned and took of on a tangent...
Manoj S.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply