这一般是什么原因造成的？
两个几乎一样的语句.第一条记录少些，执行的时间反而长些.
The break point is set in private void BindData, same place. I record two queries:
SELECT ch.ProjectId,
p.ProjectName,
ch.CompanyName,
ch.CompanyId,
COUNT(DISTINCT EmployeeId) AS TotalWorkers,
SUM(rhw + ohw + dhw) AS TotalHours,
SUM(rhw * rRate + ohw * Orate + dhw * dRate) AS LaborCost
FROM cprHeader ch,
cprWeekly cw,
cprHourly HOUR,
Projects p
WHERE ch.cprHeadeRid = cw.cprHeadeRid
AND HOUR.cprWeeklyId = cw.cprWeeklyId
AND ch.ProjectId = p.ProjectId
AND cw.EmployeeId <> 0
AND ch.ActiveRecord = 1
AND ch.IsDraft = 0
AND cw.ActiveRecord = 1
AND ch.ProjectId IN (150,178,159,174,158)
AND ch.CompanyId IN (304,332,370,307,288)
AND (Substring(cw.CraftCode,11,1) = 0
OR Substring(cw.CraftCode,11,1) = 1)
AND (HOUR.DateWorked BETWEEN CAST( '3/30/2005 ' AS DATETIME)
AND CAST( '4/5/2007 ' AS DATETIME))
AND HOUR.rhw + HOUR.ohw + HOUR.dhw <> 0
GROUP BY ch.ProjectId,p.ProjectName,ch.CompanyId,ch.CompanyName
SELECT ch.ProjectId,
p.ProjectName,
ch.CompanyName,
ch.CompanyId,
COUNT(DISTINCT EmployeeId) AS TotalWorkers,
SUM(rhw + ohw + dhw) AS TotalHours,
SUM(rhw * rRate + ohw * Orate + dhw * dRate) AS LaborCost
FROM cprHeader ch,
cprWeekly cw,
cprHourly HOUR,
Projects p
WHERE ch.cprHeadeRid = cw.cprHeadeRid
AND HOUR.cprWeeklyId = cw.cprWeeklyId
AND ch.ProjectId = p.ProjectId
AND cw.EmployeeId <> 0
AND ch.ActiveRecord = 1
AND ch.IsDraft = 0
AND cw.ActiveRecord = 1
AND ch.ProjectId IN (43,74,52,50,51,33,120,119,121,42,14,15,13,
70,19,20,29,23,47,30,60,18,61,64,62,63,54,
53,55,164,165,166,163,117,76,77,39,103,104,
68,66,69,67,32,31,102,22,136,135,137,95,93,
94,100,98,99,97,40,105,109,106,41,107,108,
78,73,71,72,134,132,133,90,92,91,82,80,81,
85,83,84,88,86,87,122,17,45,58,59,56,57,27,
21,28,46,112,110,111,34,35,36,37,38,24,25,
26,75,160)
AND ch.CompanyId IN (175,382,260,117,304,385,176,119,199,120,173,
127,358,144,182,185,189,228,240,89,93,187,
139,110,243,247,480,91,68,172,143,125,238,
108,265,109,364,146,106,98,177,95,113,190,
184,354,250,195,306,114,94,111,105,83,154,
200,186,88,201,181,287,153,241,451,477,104,
107,356,174,183,196,188,112,273,84,389,171,
308,147,233,232,225,246)
AND (Substring(cw.CraftCode,11,1) = 0
OR Substring(cw.CraftCode,11,1) = 1)
AND (HOUR.DateWorked BETWEEN CAST( '3/30/2007 ' AS DATETIME)
AND CAST( '4/5/2007 ' AS DATETIME))
AND HOUR.rhw + HOUR.ohw + HOUR.dhw <> 0
GROUP BY ch.ProjectId,p.ProjectName,ch.CompanyId,ch.CompanyName
The 2 query is run for 2 different Agency
the first query has much less data in cpr* tables than the second does, but second runs much fast.
[解决办法]
检查一下你是否建了索引
------解决方案--------------------
友情帮顶+学习
[解决办法]
(HOUR.DateWorked BETWEEN CAST( '3/30/2005 ' AS DATETIME)
AND CAST( '4/5/2007 ' AS DATETIME))
-------------------------------------vs--------------------------------------
(HOUR.DateWorked BETWEEN CAST( '3/30/2007 ' AS DATETIME)
AND CAST( '4/5/2007 ' AS DATETIME))
时间区间长一点， 用时自然会长一点
