我有一个在现有VB6应用程序中进行硬编码的sql语句。我正在C#中升级新版本,并使用Linq To Sql。我能够让LinqToSql生成相同的sql(在我开始重构之前),但是由于某种原因,LinqToSql生成的Sql比原始sql慢5倍。这是直接在LinqPad中运行生成的Sql。
我的微弱sql眼睛可以发现的唯一真正区别是
WITH(NOLOCK),如果我将其添加到LinqToSql生成的sql中,则没有任何区别。
有人可以指出我在做什么错吗?谢谢!
现有的硬编码Sql(5.0秒)
SELECT DISTINCT
CH.ClaimNum, CH.AcnProvID, CH.AcnPatID, CH.TinNum, CH.Diag1, CH.GroupNum, CH.AllowedTotal
FROM Claims.dbo.T_ClaimsHeader AS CH WITH (NOLOCK)
WHERE
CH.ContractID IN ('123A','123B','123C','123D','123E','123F','123G','123H')
AND ( ( (CH.Transmited Is Null or CH.Transmited = '')
AND CH.DateTransmit Is Null
AND CH.EobDate Is Null
AND CH.ProcessFlag IN ('Y','E')
AND CH.DataSource NOT IN ('A','EC','EU')
AND CH.AllowedTotal > 0 ) )
ORDER BY CH.AcnPatID, CH.ClaimNum
从LinqToSql生成的Sql(27.6秒)
-- Region Parameters
DECLARE @p0 NVarChar(4) SET @p0 = '123A'
DECLARE @p1 NVarChar(4) SET @p1 = '123B'
DECLARE @p2 NVarChar(4) SET @p2 = '123C'
DECLARE @p3 NVarChar(4) SET @p3 = '123D'
DECLARE @p4 NVarChar(4) SET @p4 = '123E'
DECLARE @p5 NVarChar(4) SET @p5 = '123F'
DECLARE @p6 NVarChar(4) SET @p6 = '123G'
DECLARE @p7 NVarChar(4) SET @p7 = '123H'
DECLARE @p8 VarChar(1) SET @p8 = ''
DECLARE @p9 NVarChar(1) SET @p9 = 'Y'
DECLARE @p10 NVarChar(1) SET @p10 = 'E'
DECLARE @p11 NVarChar(1) SET @p11 = 'A'
DECLARE @p12 NVarChar(2) SET @p12 = 'EC'
DECLARE @p13 NVarChar(2) SET @p13 = 'EU'
DECLARE @p14 Decimal(5,4) SET @p14 = 0
-- EndRegion
SELECT DISTINCT
[t0].[ClaimNum],
[t0].[acnprovid] AS [AcnProvID],
[t0].[acnpatid] AS [AcnPatID],
[t0].[tinnum] AS [TinNum],
[t0].[diag1] AS [Diag1],
[t0].[GroupNum],
[t0].[allowedtotal] AS [AllowedTotal]
FROM [Claims].[dbo].[T_ClaimsHeader] AS [t0]
WHERE
([t0].[contractid] IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7))
AND (([t0].[Transmited] IS NULL) OR ([t0].[Transmited] = @p8))
AND ([t0].[DATETRANSMIT] IS NULL)
AND ([t0].[EOBDATE] IS NULL)
AND ([t0].[PROCESSFLAG] IN (@p9, @p10))
AND (NOT ([t0].[DataSource] IN (@p11, @p12, @p13)))
AND ([t0].[allowedtotal] > @p14)
ORDER BY [t0].[acnpatid], [t0].[ClaimNum]
新的LinqToSql代码(30秒钟以上...超时)
var contractIds = T_ContractDatas.Where(x => x.EdiSubmissionGroupID == "123-01").Select(x => x.CONTRACTID).ToList();
var processFlags = new List<string> {"Y","E"};
var dataSource = new List<string> {"A","EC","EU"};
var results = (from claims in T_ClaimsHeaders
where contractIds.Contains(claims.contractid)
&& (claims.Transmited == null || claims.Transmited == string.Empty )
&& claims.DATETRANSMIT == null
&& claims.EOBDATE == null
&& processFlags.Contains(claims.PROCESSFLAG)
&& !dataSource.Contains(claims.DataSource)
&& claims.allowedtotal > 0
select new
{
ClaimNum = claims.ClaimNum,
AcnProvID = claims.acnprovid,
AcnPatID = claims.acnpatid,
TinNum = claims.tinnum,
Diag1 = claims.diag1,
GroupNum = claims.GroupNum,
AllowedTotal = claims.allowedtotal
}).OrderBy(x => x.ClaimNum).OrderBy(x => x.AcnPatID).Distinct();
我正在使用上面的常量列表来使LinqToSql生成IN('xxx','xxx'等),否则它将使用同样慢的子查询...
最佳答案
比较两个查询的执行计划。 linqtosql查询正在使用大量参数,查询优化器将根据参数中的MIGHT建立执行计划,硬编码SQL具有文字值,查询优化器将根据实际值建立执行计划。它可能会为字面量生成一个更为有效的计划。最好的选择是尝试找出执行计划中的慢速位,并尝试使linq2sql产生更好的查询。如果不能,但是您认为可以手动构建,则创建一个SP,然后可以将其作为方法公开在linqtosql的数据上下文类中。