The following query when run on my live server as a query pasted into SSMS returns this info in the Profiler (SQL 2005):SQL:BatchCompleted exec mysp_getAdminPeople @userID=1796,@customerID=25,@separator=' ' Microsoft SQL Server Management Studio - Query sa 1062 4639 0 1853 5628 56 2011-09-25 14:51:49.307 2011-09-25 14:51:51.167
There are 4639 reads, where 2752 rows of data are returned in about five seconds.When the EXACT same query is run from a web page with ASP.NET (same server), the results within Profiler show this, and the code takes over a minute to complete, and sometimes even times out instead of completing:RPC:Completed exec mysp_getAdminPeople @userID=1796,@customerID=25,@separator=' .Net SqlClient Data Provider LMSAdminUser 71407 728946 0 71533 8876 58 2011-09-25 14:55:57.370 2011-09-25 14:57:08.883 0X0000000004000000260065003400730070005F00670065007400410064006D0069006E00500065006F0070006C0065002400000003000600381069006E007
For some reason there are 728946 reads and a chunk of binary data. Could anyone please help explain:1. Why there is such an enormous difference in execution time?2. Why there is binary data showing in the Profiler when the query runs the second time?Thank in advance.
Same query - massively different execution times
Posted - 2011-09-25 : 10:05:44
