How do I display total runtime in SqlServer Management Studio 2005?

Question

I usually do not use Sql Server Management Studio, I usually use Linqpad to run all my db queries. Anyhow.... My boss seems to think that stored procs are "a lot faster than linq".

So to test this, I want to run a simple stored proc and display the time it takes to run against an equal linq statement.

Any good ideas on how to achieve this? I am sure you guys (and gals) have run across this before.

Any ideas on how to compare that to the runtime of a linq statement?

EDIT: Let me clarify some things; First when my boss says "linq" I can only assume she is talking about Linq-to-Sql. Second, I am willing to try every way possible to test out this theory.

Answer 1

I usually create a variable for this. Ex:

Declare @Start DateTime
Set @Start = GetDate()

Exec YourStoredProcedureHere

Select DateDiff(Millisecond, @Start, GetDate())

Answer 2

You should be able to use SQL Profiler to track both queries and see what the differences are. Using this method your timings will be done in the same place, rather than trying to compare a TSql timing with something in C# for your LINQ query.

Answer 3

The argument that "stored proces are a lot faster than LINQ" is a bit fuzzy. Are we talking about running a single stored procedure, or several stored procedures using the same database connection? Are we talking about using cursors or transactions? Some specifics would be very helpful.

My understanding is that LINQ to SQL is useful for working with data that's been returned to your application from the database server, or to issue commands to the database server using a more SQL-esque syntax. The type and number of the operations will likely have a significant bearing on the performance of one versus the other.

I, myself, am of the opinion (based on my limited experience) that stored procedures should always be faster because they reside locally with the data itself, and you remove the overhead of any network traffic that may be required for your application to make repeated calls to the server. (There's always some required when you establish the connection.) But that can be mitigated via optimization.

As I said, clarification is needed.

EDIT: In my post, I'm referring to end-to-end performance, not the performance of the SQL that LINQ generates vs. the SQL in stored procedures.

Answer 4

I think what he's asking for is not a methodology for logging the time, but more like some realistic, valid tests to run as a stored proc vs. Linq.

Answer 5

Your boss is right in the sense that stored procedures are like compiled code whereas LINQ (which uses SQL) is more like interpreted code.

BUT... you lose flexibility with stored procs. Also, are you running them alot, as in over 10,000 times a minute? If not then you won't really notice a difference.

Alot of things can influence query speed, the least of which is stored proc vs freeform query. I'd worry more about database structure and things like indexes before I'd worry about making everything stored procs.