Clever Geek Handbook

How do I show the total execution time in SqlServer Management Studio 2005?

I usually don't use Sql Server Management Studio, I usually use Linqpad to run all my db queries. Anyway .... My boss seems to think the stored procs are "much faster than linq".

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

Any good ideas on how to achieve this? I'm sure you guys (and gals) have encountered this before.

Any ideas on how to compare this with the runtime of a linq expression?

EDIT: Let me clarify a few things; At first, when my boss says "linq", I can only assume she is talking about Linq-to-Sql. Secondly, I am ready to test this theory in every possible way.

+1


source to share


5 answers


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

BUT ... you lose flexibility when storing procs. Also, do you use them a lot, like over 10,000 times a minute? If not, you won't notice the difference.



Many things can affect query speed, the smallest of which is stored proc vs freeform query. I would worry more about the structure of the database and things like indexes before I worry about all the stored procs.

+3


source


I usually create a variable for this. Example:



Declare @Start DateTime
Set @Start = GetDate()

Exec YourStoredProcedureHere

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


source


I think it is not asking for a time logging methodology, but rather as realistic, valid tests to run as a stored procedure against Linq.

+1


source


You can use SQL Profiler to track both queries and see the differences. Using this method, your timings will run in the same place, instead of trying to compare TSql time to something in C # for your LINQ query.

0


source


The argument that "stored processes are much faster than LINQ" is a little fuzzy. Are we talking about running a single stored procedure or multiple stored procedures using the same database connection? Are we talking about using cursors or transactions? Some features would be very helpful.

I understand that LINQ to SQL is useful for working with data that was returned to your application from the database server, or for issuing commands to the database server using more SQL-esque syntax. The type and number of operations are likely to have a significant impact on the performance of both.

I myself am of the opinion (based on my limited experience) that stored procedures should always be faster because they are localized locally with the data itself, and you remove the overhead of any network traffic that your application might need to call back to server. (Always needed when you establish a connection). But this can be mitigated with optimization.

As I said, clarification is needed.

EDIT: In my post, I mean end-to-end performance, not SQL performance that LINQ generates versus SQL in stored procedures.

0


source






More articles:


All Articles