why this simple linq query takes so much time?

Question

I have this linq query:

 public static Banner getSideBarBanner(){
    DataClassesDataContext db = new DataClassesDataContext();
    var bannerSiderBar = (from b in db.Banners
                  where b.Position.Equals(EBannersPosition.siderbar.ToString())
                  && b.Visible == true
                  select b).FirstOrDefault();
    return bannerSiderBar;
}

well, I use dotTrace to profile the application and I see that the query execution takes a lot of time (over 2s)

I am simply wondering, why so much time especially when my Banner table has about 30 records!!!

Thanks in advance for your opionions...

UPDATE: Banner's table schema:

UPDATE 2: If I use simple SQL connection instead of linq, the query execution takes 700ms which is a huge improvement...

 public static Banner getSideBarBanner()
{
    Banner bannerFound = new Banner();
    SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Library_prodConnectionString"].ConnectionString);
    try
    {
        myConnection.Open();
        SqlCommand myCommand = new SqlCommand("SELECT path, link FROM Banner b WHERE b.Position = @position AND b.Visible = 1 ", myConnection);
        myCommand.Parameters.Add(new SqlParameter("@position", EBannersPosition.siderbar.ToString()));
        SqlDataReader myReader = myCommand.ExecuteReader();
        while (myReader.Read())
        {
            if (myReader["path"] != null)
                bannerFound.Path = myReader["path"].ToString();
            if (myReader["link"] != null)
                bannerFound.Link = myReader["link"].ToString();
        }
        myConnection.Close();
    }
    catch (Exception e)
    {
        CreateLogFiles Err = new CreateLogFiles();
        Err.ErrorLog(HttpContext.Current.Server.MapPath("~/Site/Logs/ErrorLog"), e.ToString());
    }
    return bannerFound;
}

This tells me that translation of the linq query to sql has a very poor performance...What do you think?

Answer 1

You should consider grabbing the trial for http://l2sprof.com/ (if you're using LINQ to SQL) or http://efprof.com/ (if you're using Entity Framework) and using that to figure out what SQL your query is generating.

They're both free for 30 days, which I hope would be plenty of days to figure out this problem. ;)

Another possibility, pointed out by Robert in the comments, is to set the Log property on your DataContext, which will output the generated SQL to wherever you want.

You also could just use the SQL Server Profiler, which probably will display much more than you need, but hey, it would probably still get the job done.

Answer 2

Remember that LINQ is delayed in execution until you enumerate through the results. In this case, when you call FirstOrDefault, that's where it is actually running the database query, which might explain the delay.

It's not that FirstOrDefault is taking 2s, it's that the entire query is.

With that in mind, if you want people to narrow down further, you'll need to post your schema, data, etc.

Answer 3

First off, the time spent calling FirstOrDefault() is the time spent digesting the Linq expression tree into SQL, sending that SQL to the DB, retrieving the result in a result set, and mapping that result set to an object. That can take a while.

Second, I would profile the database. Do a trace and figure out the exact SQL statement sent to the DB for this call. If that statement does not include a representation of the FirstOrDefault limitation, such as SELECT TOP 1 ..., you're pulling ALL the records out of the table just to discard all but one of them. Linq2SQL should be smarter than that in this case; if not, look into upgrading to MSEF or NHibernate (a big job for just one query, I grant you, but if this statement isn't producing an efficient query then any query like it won't be efficient either).

Answer 4

Add an index to Position, and try this:

 public static Banner getSideBarBanner()
 {
    DataClassesDataContext db = new DataClassesDataContext();

    string thisPosition = EBannersPosition.siderbar.ToString();

    var bannerSiderBar 
        = db.Banners.FirstOrDefault<Banner>
             (x => x.Position == thisPosition && x.Visible);

    return bannerSiderBar;
}

Basically the ideas here are to:

1. Put the FirstOrDefault up front and make it strongly-typed, and
2. remove the multiple executions of EBannersPosition.siderbar.ToString(),

Answer 5

What you are witnessing, is, in my opinion, a problem with dotTrace. It reports exaggerated times for anything that is related to Linq-To-Sql. (See my comments on Best .NET memory and performance profiler?) It is not the only profiling product out there that has this problem.

I have experienced that myself and only very late in the process tried to verify the times of dotTrace with the System.Diagnostics.StopWatch. Of course, a profiler cannot report as accurate timings as StopWatch. But they were off by a large margin (some factors) and completely misrepresent the time taken by your code (for the L2S part).

It is somewhat evidenced by the fact that the total execution time exceeds the actual SQL Server work by a few factors.

Keep in mind though, that Linq-To-Sql (L2S) itself incurs some overhead, which can be significant at times. The object creation by L2S for each data row is not as simple as calling a constructor on an object and populating its properties. Not only because the Model classes are more than simple objects but also because it does a lot of verification of schema and datatypes and whatnot.

And of course, the compilation of the queries themselves can take quite some time.

So, in summary, try to get timings by using StopWatch. It would be good if you could share some results if you can verify my claims about dotTrace.

UPDATE1: One thing you could try, is to take the timings not on the first run, but on the second run of the code. Just to make sure you don't hit any one-time-costs. Also, with Linq, you always have the option to use compiled queries. Just search around for that a bit. In my experience you'll get the same inaccurate results though.

One note on compiled queries - don't use them, if not absolutely necessary. They have some major disadvantages, if all, that you are looking for, is a simple ORM. One is, you lose identity tracking. Also, you cannot use them for WHERE expr IN (setexpr)-type queries (list.Contains(...). Another one, of course, is readability. And finally, if you are going to use them, you might want to look at Nexterday's autocompilation for L2S (http://linqautocompiler.codeplex.com/)