Tools and techniques to optimize a LINQ to SQL query

Question

I have a couple of LINQ to SQL queries that I feel take a lot of time. What tool and techniques can I use to first find out:

1. How long they take and if there is room for improvement?
2. Find out what I can change and easily test if those changes are effective?

Both tools, techniques and reading resources would be appreciated (ergo up-voted ;))

Answer 1

Well, the best option probably still is the SQL Server Profiler, which will give you an in-depth look at what's going on at SQL Server's end of the wire. This is based on the assumption (backed by my own experience) that most often than not, the real problem for the performance lies more on the database backend and suboptimal indices and db design rather than the "front-end" LINQ queries...

See:

• How to identify slow running queries
• Video series on how to become a profiler master
• Free e-book on SQL Server profiler

Marc

Answer 2

I would check this blog t out by David Hayden...

Answer 3

This previous post may give you some help.

Answer 4

LinqPad can be helpful. Used in conjunction with SQL profiler you might be able to work out what is going on and how it can be improved.

Answer 5

I just attended a LINQ Tools presentation at a recent codecamp event given by Jim Wooley, one of the authors of LINQ In Action. He blogged about all the tools. Here are the ones that relate to your question, but his post lists other goodies to look into.

• SQL Server Profiler ($$$) - part of the SQL Management Studio.

• Huagati LINQ to SQL Profiler ($$$, 45-day trial) - Profiler which extends the Context.Log functionality. Includes performance and call stacks for the queries beyond what you get with SQL Server Profiler.

• LINQ to SQL Visualizer (free) - part of the C# Samples for VS2008. Download, compile, enjoy!

• LINQPad (free, pay for C# Intellisense) - light-weight tool to run C#/VB.NET code, connect to DBs, test Linq to SQL, view the generated SQL etc.

Answer 6

One nice "trick" when using LINQ to SQL is to use the DataContext.Log property to see the SQL generated by the framework:

context.Log = Console.Out

SQL Server Profiler will give you a more objective measure of the performance of your queries, but being able to inspect the queries directly may still reveals some unexpected information.

Answer 7

The amazing Linq to Sql Profiler by Hybernating Rhinos. You get real-time analysis of your code and alerts indicating patterns of misuse. 30 day trial.