Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
You got same the performance : 1sec in your code and in SSMS, isn't it ? – remi bourgarel Apr 25 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 at 13:41
use fdottrace for figuring out what is happening, stop guessing ! – remi bourgarel Apr 25 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 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 at 15:39
show 7 more comments

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.