.net - 加快 LINQ 查询速度的技巧？

Question

我有一个 LINQ 查询，它正在搜索包含大约 250,000 条记录的 SQL 表，并且仅搜索 2 个字段。这两个字段都已建立索引，但我发现它的运行速度仍然相当慢。

下面是代码，有人可以提出任何建议来帮助加快速度吗？

谢谢

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();

Answer 1

Description 上的过滤器可能存在问题:

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

Contains 调用会转换为 SQL 描述，如 '%' + @p0 + '%'。这意味着即使您在description 列上有索引，SQL Server 也必须循环遍历整个索引(甚至可能是整个表)。如果可能，请将行更改为以下内容:

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

这将转换为描述，如@p0 + '%'，它允许您的数据库服务器有效地使用索引。