如何加速这个linq到sql函数？

I have a function (called "powersearch", the irony!) that searches for a set of strings across a bunch(~ 5) of fields. The words come in as one string and are separated by spaces.
Some fields can have exact matches, others should have "contains".

我有一个函数(称为“powersearch”,具有讽刺意味!),它在一堆(~5)个字段中搜索一组字符串。单词以一个字符串形式出现,并以空格分隔。某些字段可以具有完全匹配,其他字段应具有“包含”。

(Snipped for brevety)

(因为吝啬)

//Start with all colors
IQueryable<Color> q = db.Colors;
//Filter by powersearch
if (!string.IsNullOrEmpty(searchBag.PowerSearchKeys)){
    foreach (string key in searchBag.SplitSearchKeys(searchBag.PowerSearchKeys)
                                    .Where(k=> !string.IsNullOrEmpty(k))){
        //Make a local copy of the var, otherwise it gets overwritten
        string myKey = key;
        int year;
        if (int.TryParse(myKey, out year) && year > 999){
            q = q.Where(c => c.Company.Name.Contains(myKey)
                || c.StockCode.Contains(myKey)                                
                || c.PaintCodes.Any(p => p.Code.Equals(myKey))
                || c.Names.Any(n => n.Label.Contains(myKey))
                || c.Company.CompanyModels.Any(m => m.Model.Name.Contains(myKey))
                || c.UseYears.Any(y => y.Year.Equals(year))
            );
        }
        else{
            q = q.Where(c => c.Company.Name.Contains(myKey)
                || c.StockCode.Contains(myKey)
                || c.PaintCodes.Any(p => p.Code.Contains(myKey))
                || c.Names.Any(n => n.Label.Contains(myKey))                                
                || c.Company.CompanyModels.Any(m => m.Model.Name.Equals(myKey))
            );
        }
    }
}

Because the useYear count is rather large, I tried to check for it as little as possible by outruling all numbers that can never be a number that makes sence in this case. Similar checks are not possible on the other fields since they can pretty much contain any thinkable string.

因为useYear计数相当大,所以我试图通过输出所有数字来尽可能少地检查它,这些数字在这种情况下永远不会是一个数字。在其他字段上不可能进行类似的检查,因为它们几乎可以包含任何可思考的字符串。

Currently this query takes about 15 secs for a single, non-year string. That's too much. Anything I can do to improve this?

目前,对于单个非年份字符串,此查询大约需要15秒。这太多了。我能做些什么来改善这个?

--Edit--
Profiler shows me the following info for the part where the string is not a year:

--Edit-- Profiler向我展示了字符串不是一年的部分的以下信息:

exec sp_reset_connection Audit login

exec sp_reset_connection审核登录

exec sp_executesql N'
SELECT COUNT(*) AS [value]
FROM [dbo].[CLR] AS [t0]
INNER JOIN [dbo].[CO] AS [t1] ON [t1].[CO_ID] = [t0].[CO_ID]
WHERE 
    ([t1].[LONG_NM] LIKE @p0)
    OR ([t0].[EUR_STK_CD] LIKE @p1)
    OR (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[PAINT_CD] AS [t2]
        WHERE ([t2].[PAINT_CD] LIKE @p2)
            AND ([t2].[CLR_ID] = [t0].[CLR_ID])
            AND ([t2].[CUSTOM_ID] = [t0].[CUSTOM_ID])
        )
    )OR (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[CLR_NM] AS [t3]
        WHERE ([t3].[CLR_NM] LIKE @p3)
            AND ([t3].[CLR_ID] = [t0].[CLR_ID])
            AND ([t3].[CUSTOM_ID] = [t0].[CUSTOM_ID])
        )
    ) OR (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[CO_MODL] AS [t4]
        INNER JOIN [dbo].[MODL] AS [t5] ON [t5].[MODL_ID] = [t4].[MODL_ID]
        WHERE ([t5].[MODL_NM] = @p4)
            AND ([t4].[CO_ID] = [t1].[CO_ID])
        )
    )
',N'@p0 varchar(10),@p1 varchar(10),@p2 varchar(10),@p3 varchar(10),@p4 varchar(8)',@p0='%mercedes%',@p1='%mercedes%',@p2='%mercedes%',@p3='%mercedes%',@p4='mercedes'

(took 3626 msecs) Audit Logout (3673 msecs) exec sp_reset_connection (0msecs) Audit login

(花了3626 msecs)审核注销(3673 msecs)exec sp_reset_connection(0msecs)审核登录

exec sp_executesql N'
SELECT TOP (30) 
[t0].[CLR_ID] AS [Id],
[t0].[CUSTOM_ID] AS [CustomId],
[t0].[CO_ID] AS [CompanyId], 
[t0].[EUR_STK_CD] AS [StockCode], 
[t0].[SPCL_USE_CD] AS [UseCode], 
[t0].[EFF_IND] AS [EffectIndicator]
FROM [dbo].[CLR] AS [t0]
INNER JOIN [dbo].[CO] AS [t1] ON [t1].[CO_ID] = [t0].[CO_ID]
WHERE 
    ([t1].[LONG_NM] LIKE @p0)
    OR ([t0].[EUR_STK_CD] LIKE @p1)
    OR (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[PAINT_CD] AS [t2]
        WHERE ([t2].[PAINT_CD] LIKE @p2)
            AND ([t2].[CLR_ID] = [t0].[CLR_ID])
            AND ([t2].[CUSTOM_ID] = [t0].[CUSTOM_ID])
        )
    )
    OR (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[CLR_NM] AS [t3]
        WHERE ([t3].[CLR_NM] LIKE @p3)
            AND ([t3].[CLR_ID] = [t0].[CLR_ID])
            AND ([t3].[CUSTOM_ID] = [t0].[CUSTOM_ID])
        )
    )
    OR (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[CO_MODL] AS [t4]
        INNER JOIN [dbo].[MODL] AS [t5] ON [t5].[MODL_ID] = [t4].[MODL_ID]
        WHERE ([t5].[MODL_NM] = @p4)
            AND ([t4].[CO_ID] = [t1].[CO_ID])
        )
    )'
,N'@p0 varchar(10),@p1 varchar(10),@p2 varchar(10),@p3 varchar(10),@p4 varchar(8)',@p0='%mercedes%',@p1='%mercedes%',@p2='%mercedes%',@p3='%mercedes%',@p4='mercedes'

(took 3368 msecs)

(花了3368毫秒)

The database structure, sadly, is not under my control. It comes from the US and has to stay in the exact same format for compatibility reasons. Although most of the important fields are indeed indexed, they are indexed in (unnecessary) clustered primary keys. There's verry little I can do about that.

遗憾的是,数据库结构不在我的控制之下。它来自美国,出于兼容性原因必须保持完全相同的格式。虽然大多数重要字段确实已编入索引,但它们在(不必要的)聚簇主键中编入索引。我无能为力。

2 个解决方案

#1

Okay, let's break this down - the test case you're interested in first is a single non-year, so all we've got is this:

好的,让我们打破这个 - 你首先感兴趣的测试用例是一个非年,所以我们得到的就是:

q = q.Where(c => c.Company.Name.Contains(myKey)
            || c.StockCode.Contains(myKey)
            || c.PaintCodes.Any(p => p.Code.Contains(myKey))
            || c.Names.Any(n => n.Label.Contains(myKey))
            || c.Company.CompanyModels.Any(m => m.Model.Name.Equals(myKey))

Am I right? If so, what does the SQL look like? How long does it take just to execute the SQL statement in SQL Profiler? What does the profiler say the execution plan looks like? Have you got indexes on all of the appropriate columns?

我对吗?如果是这样,SQL看起来像什么?在SQL事件探查器中执行SQL语句需要多长时间?探查者说执行计划是什么样的?您是否在所有相应的列上都有索引?

#2

Use compiled queries.

使用编译的查询。

If you don't, you will lose up to 5-10x times performance, as LINQ-to-SQL will have to generate SQL from query every time you call it.

如果不这样做,您将失去高达5-10倍的性能,因为每次调用它时,LINQ-to-SQL都必须从查询中生成SQL。

Things become worse when you use non-constants in LINQ-to-SQL as getting their values is really slow.

当你在LINQ-to-SQL中使用非常量时,事情变得更糟,因为它们的值非常慢。

This assumes that you already have indexes and sane DB schema.

这假设您已经拥有索引和理智的数据库架构。

BTW, I am not kidding about 5-10x part.

顺便说一句,我不是开玩笑的大约5-10倍。