tips to speed up LINQ query?

Question

I have a LINQ query which is searching a SQL table of around 250,000 records and only searching on 2 fields. Both fields have been indexed but I find its still running rather slow.

Below is the code, can anyone suggest anything to help speed it up?

thanks

var qryN = (
    from bn in dbs.Uploads
    orderby bn.ID descending
    select new
    {

        ID = bn.ID,
        Serial = bn.serial_no,
        Manufacturer = bn.Mfgr,
        Model = bn.model,
        Code = bn.code,
        Qty = bn.qty,
        Description = bn.description,
        Comments = bn.comments,
        Location = bn.location,
        Price = bn.price,
        Email = "Register / Login for full details"
    });

if (dlType.Text != " " && dlType.Text != "")
{
    qryN = qryN.Where(bn => bn.Manufacturer == dlType.SelectedValue);
}

if (txtWord.Text != "")
{
    qryN = qryN.Where(bn => bn.Description.Contains(txtWord.Text));
    }

gvLoggedOff.DataSource =
    from p in qryN
    select new
    {
        p.ID, 
        p.Serial, 
        p.Manufacturer, 
        p.Model, p.Code, 
        p.Qty, 
        p.Description, 
        p.Comments, 
        p.Location, 
        p.Price, 
        p.Email
    };

gvLoggedOff.DataBind();    var qryN = (
    from bn in dbs.Uploads
    orderby bn.ID descending
    select new
    {

        ID = bn.ID,
        Serial = bn.serial_no,
        Manufacturer = bn.Mfgr,
        Model = bn.model,
        Code = bn.code,
        Qty = bn.qty,
        Description = bn.description,
        Comments = bn.comments,
        Location = bn.location,
        Price = bn.price,
        Email = "Register / Login for full details"
    });

if (dlType.Text != " " && dlType.Text != "")
{
    qryN = qryN.Where(bn => bn.Manufacturer == dlType.SelectedValue);
}

if (txtWord.Text != "")
{
    qryN = qryN.Where(bn => bn.Description.Contains(txtWord.Text));
    }

gvLoggedOff.DataSource =
    from p in qryN
    select new
    {
        p.ID, 
        p.Serial, 
        p.Manufacturer, 
        p.Model, p.Code, 
        p.Qty, 
        p.Description, 
        p.Comments, 
        p.Location, 
        p.Price, 
        p.Email
    };

gvLoggedOff.DataBind();

Jon is absolutely right. Your question is currently very vague. We don't know how you data model looks, what your gets generated, whether you're using LINQ to SQL, Entity Framework, NHibernate, etc. — Steven, Sep 29 '11 at 11:06

score 7 · Answer 1 · answered Sep 29 '11 at 11:04

The filter on Description could be a problem:

qryN = qryN.Where(bn => bn.Description.Contains(txtWord.Text));

The Contains calls translates to a SQL description LIKE '%' + @p0 + '%'. Which means that even if you have an index on the description column, SQL server has to loop over the complete index (or perhaps even the complete table). If possible, change the line to the following:

qryN = qryN.Where(bn => bn.Description.StartsWith(txtWord.Text));

This will translate to description LIKE @p0 + '%' which allows your database server to effectively use the index.

score 1 · Accepted Answer · edited May 23 '17 at 11:47

Two things:

Since you're adding filters, move the projection (selecting into a new anonymous type) to the last line instead of the first, the DataSource line.
You're ordering very early. Unless the queryable or the query analyzer are smart enough to analyze and optimize this, the database will have to order 250,000 rows before it starts filtering. Move the ordering to as late as you can, probably just before the projection takes place.

Here's a short attempt at modifying your sample:

var qryN = dbs.Uploads.AsQueryable();

if (dlType.Text != " " && dlType.Text != "")
    qryN = qryN.Where(bn => bn.Mfgr == dlType.SelectedValue);

if (txtWord.Text != "")
    qryN = qryN.Where(bn => bn.description.Contains(txtWord.Text));

gvLoggedOff.DataSource = qryN.OrderByDescending(bn => bn.ID)
    .Select(bn => new { 
        bn.ID, Serial = bn.serial_no, Manufacturer = bn.Mfgr,
        Model = bn.model, Code = bn.code, Qty = bn.qty,
        Description = bn.description, Comments = bn.comments,
        Location = bn.location, Price = bn.price,
        Email = "Register / Login for full details" });

gvLoggedOff.DataBind();

Since this was accepted as an answer: Steven's answer about text searching is probably also applicable in some situations. Full text searching can drain performance like you wouldn't believe.

yep, I do realise that but thats what the client wanted. I employed your solution, then put Stevens into place for one field and the results are great - thanks again — DarkW1nter, Oct 03 '11 at 08:30
@Pedro: Oh sure, I'm not saying "don't do it", I'm just saying "be aware of it". — Jesper, Oct 04 '11 at 11:37
one minor addition: `if (dlType.Text != " " && dlType.Text != "")` can be `if (!string.IsNullOrWhiteSpace(dlType.Text))` — JP Hellemons, Aug 28 '12 at 13:02
Amongst other improvements. I chose to not make them to make correlation easier. — Jesper, Aug 28 '12 at 13:32

score 1 · Answer 3 · edited May 23 '17 at 12:19

1

Description.Contains sounds heavy, you may try to use full text search instead.

Here is a post describing how you can use FTS from LINQ.

edited May 23 '17 at 12:19

Community

1
1

answered Sep 29 '11 at 11:09

Albin Sunnanbo

46,430
8
69
108

tips to speed up LINQ query?

3 Answers3