SqlDataReader executing TSQL is faster than management studio executing TSQL

Question

If i run a TSQL Statement in management studio and run the same the query through SqlDataReader, the latter gives the result faster than the former...

Any reason??

Answer 1

It could be related to the time that it takes to display the results in the SSMS? Which would probably be related to the size of the result set.

How big is the result set?

Answer 2

One possibility is the connection state is different - in particular, the SET options etc - not just the obvious ones (stats, profiling, etc) - but even things like ANSI_NULLS can have a big impact on some queries (especially with xml columns, or peristed calculated columns).

Also - what are you doing with the data when you read it? Are you displaying it? Storing it? Just dropping it? SSMS has to buffer it in a table-like mechanism to display in a grid... if you are parsing it into standard, typed classes (that already match the table layout) - or just dropping the row unprocessed, then you have less work to do.

From what I recall, it also updates the screen in batches - suggesting there is some threading going on... lots of variables here...

Answer 3

Some thoughts:

The only way to know exactly is to use SQL Profiler to trap duration etc. SSMS require resource to take the result set and display it. What are you doing with the SQL Data Reader results?Or

Or, is this all on your PC, or are you running the SqlDataReader on a server?

Or, are you running the query via SSMS first so the plan is cached and compiled, with data in memory?

Answer 4

The Tsql query I was running has no set based operations...I was just curious to check the time difference by running a select query on a server that gave out 163336 record sets with 15 columns of all sorts of datatyes. I noted the time at the status bar at the bottom of ssms where time is displayed.

I ran the same query with SqlDataReader too. ANd i was running the same select query multiple times (mind it the cases and spaces were all same)in both forms of query reader.

THe time taken by sqldatareader varied from 15-28 secs where as the query at the ssms varied from 29-31 secs...

However I was not running them at the same time, but alternately and multiple times...so i dont think the network bandwidth or memory or CPU usage denies that the tsql executed with datareader gives the better performance than that executed in ssms