tips to speed up LINQ query?

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 == 开发者_JAVA百科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();

---

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.

---

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.

---

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.