How to improve Linq-To-Sql code

Question

Using the StackExchange.Profiling.MiniProfiler class to profile an ASP.NET MVC application with Linq-To-Sql as ORM.

I'm trying to reduce one action to one SQL, so that I don't have any duplicates anymore. So I changed my linq-to-sql code accordingly, but it didn't have any positive effect on the speed.

Then I checked the time that is needed for the SQL.

This shows the MiniProfiler:

enter image description here

When I fire up the exact same SQL in Management Studio it is super fast:

enter image description here

Here is the code:

from t in type
let tDoc = (from d in context.documents
            where d.Key == t.No
            && d.RType == (int)RType.Art
            && d.AType == (int)AType.Doc
            select d).FirstOrDefault(d => d.UseForThumb)
select new Time
{
    Id = t.Id,
    //... more simple mappings here
    // then a complex one:
    DocsCount = context.documents.Count(d =>
        (d.Key == t.Id.ToString()
        && d.RType == (int)RType.Type
        && d.AType == (int)AType.Doc)
        ||
        (d.Key == t.No
        && d.RType == (int)RType.Art
        && d.AType == (int)AType.Doc)),

    // and another one
    ThumbId = (tDoc != null && tDoc.FRKey.HasValue) ? tDoc.FRKey.Value : 0
};

What can be the reason for the huge difference? - Edit: There is no difference, I just misenterpreted SSMS :(

Anyway, my problem persits. What could I change to make it faster?

I read sometime that the mapping from Linq-To-Sql has a performance problem. Is there a way to workaround this?

You got same the performance : 1sec in your code and in SSMS, isn't it ? — remi bourgarel, Apr 25 '13 at 13:27
Oh, is that so? I though in SSMS it is 1 ms! Embarrasing! It's still to slow to fetch those 12 rows... — Philipp M, Apr 25 '13 at 13:41
use fdottrace for figuring out what is happening, stop guessing ! — remi bourgarel, Apr 25 '13 at 14:36
I started a trace on SqlServer with Sql Server Profiler, and got a lot of information, but I don't know what to do with it yet... so I'm learning by doing right now. I will try fdottrace too. — Philipp M, Apr 25 '13 at 14:47
your problem is not with sql server, use dottrace to figure out which part of your code is slow : template, sql, computing... — remi bourgarel, Apr 25 '13 at 15:39
Well, the SQL query is the bottleneck, when I check in MiniProfiler... — Philipp M, Apr 25 '13 at 16:00
Upload the profile generated in SQL Server Profiler to the Database Tuning Advisor (SSMS -> Tools - Database Tuning Advisor) and see what recommendations are made — Halceyon, Apr 25 '13 at 16:05
execute your query in SSMS and displays the actual execution plan, here you'll be able to find out what takes the most ressource / time to excute. — remi bourgarel, Apr 26 '13 at 07:28
I added some indexes, but this didn't improve the speed of the query — Philipp M, Apr 26 '13 at 08:23
In the execution plan I can see that a `Filter` operation costs 82%, which I assume is because it filters from 17.788.096 rows... — Philipp M, Apr 26 '13 at 09:44
Now I'm trying to find out why it's so many rows, that are being filtered, because the actual tables have only 30k and 50k rows — Philipp M, Apr 26 '13 at 09:48

score 0 · Accepted Answer · answered Apr 29 '13 at 07:54

I did some trial and error and changed the Linq-To-Sql code to this:

from t in types
let docs = context.documents.Where(d => (d.RKey == t.Id.ToString()
                                     && d.RType == (int)RType.Type
                                     && d.AType == (int)AType.Doc)
                                   ||
                                        (d.RKey == t.No 
                                     && d.RType == (int)RType.Art 
                                     && d.AType == (int)AType.Doc))
let tDoc = docs.FirstOrDefault(d => d.RType == (int)RType.Art && d.UseForThumb)
let docsCount = docs.Count()
select new Time
{                                               
  Id = t.Id,
  //... more simple mappings here
  DocsCount = docsCount,
  ThumbId = (tDoc != null && tDoc.FRKey.HasValue) ? tDoc.FRKey.Value : 0,
}

This made the query much, much faster.

How to improve Linq-To-Sql code

1 Answers1