ansaurus

Question

Performance bottleneck - Linq to SQL or the database - how do I tell?

Answer 1

+2 A:

There are 2 tools I use for this, LinqPad and the Visual Studio Debugger. First, check out LinqPad, even the free version is very powerful, showing you execution time, the SQL generated and you can use it to run any code snippet...it's tremendously useful.

Second, you can use the Visual studio debugger, this is something we use on our DataContext (note: only use this in debug, it's a performance hit and completely unnecessary outside of debugging)

#if DEBUG
  private readonly Stopwatch Watch = new Stopwatch();

  private static void Connection_StateChange(object sender, StateChangeEventArgs e)
  {
    if (e.OriginalState == ConnectionState.Closed && e.CurrentState == ConnectionState.Open) 
    {
      Current.Watch.Start();
    }
    else if (e.OriginalState == ConnectionState.Open && e.CurrentState == ConnectionState.Closed)
    {
      Current.Watch.Stop();

      string msg = string.Format("SQL took {0}ms", Current.Watch.ElapsedMilliseconds);
      Trace.WriteLine(msg);
    }
  }
#endif

private static DataContext New
{
  get
  {
    var dc = new DataContext(ConnectionString);
#if DEBUG
    if (Debugger.IsAttached)
    {
      dc.Connection.StateChange += Connection_StateChange;
      dc.Log = new DebugWriter();
    }
#endif
    return dc;
  }
}

In a debug build, as an operation completes with each context, we see the timestamp in the debug window and the SQL it ran. The DebugWriter class you see can be found here (Credit: Kris Vandermotten). We can quickly see if a query's taking a while. To use it we just initiate a DataContext by:

var DB = DataContext.New;

(The profiler is not an option for me since we don't use SQL server, this answer is simply to give you some alternatives that have been very useful for me)

Nick Craver 2010-02-13 12:49:58

Answer 2

+2 A:

I used this excelent tool: Linq2Sql profiler. It works on the application side, so there is no need for database server profiling functionality.

You have to add one line of initialization code to your application and then in separate desktop application profiler shows you SQL query for each LINQ query with exact line of code where it was executed (cs or aspx), database time and application time of executions and it even detects some common performance problems like n+1 queries (query executed for iteration) or unbounded datasets. You have to pay for it, but the trial version is also available.

PanJanek 2010-02-13 12:54:19

Direct link - http://l2sprof.com/ - we use this here on Stack Overflow; truly a wonderful asset when tuning any queries!

Jarrod Dixon 2010-02-13 13:04:22

Thanks for the link to linq2sql profiler - a great tool!

Sergio 2010-02-13 16:28:44

Answer 3

+2 A:

As you're using SQL Express which doesn't have Profiler, there is a free third party profiler you can download here. I've used it when running SQL Express. That will allow you to trace what's going on in the database.

Also, you can query the Dynamic Management Views to see what the costly queries are: e.g. TOP 10 queries that have taken the most time

SELECT TOP 10 t.text, q.*, p.query_plan
FROM sys.dm_exec_query_stats q
    CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t
    CROSS APPLY sys.dm_exec_query_plan (q.plan_handle) AS p
ORDER BY q.total_worker_time DESC

AdaTheDev 2010-02-13 13:04:37

+1 I would get the SQL developer edition with a copy of the production database for things like this, then you do get Profiler. But if you need to profile live Express databases, that looks like a good solution.

SqlACID 2010-02-13 13:14:46

ansaurus

tags:

views:

answers:

Performance bottleneck - Linq to SQL or the database - how do I tell?

related questions