很郁闷的问题，为什么设置的日期间隔越短，查询越慢？

[hanbaojun]

为什么设置的日期间隔越短，查询越慢？


如果日期间隔为 5天以内数据就查不出来了。数据表 SendColl 的数据5万左右，BK_BusinessData表有20万存放的是明细，TreeInfo 有1000多条数据。存储过程如下：

ALTER PROCEDURE [dbo].[SendColl_SD_LIST_OUT]

@bmID varchar(50),
@startDate varchar(50),
@endDate varchar(50),
@humanID varchar(50),
@startID varchar(50),
@endID varchar(50),
@num1 int

AS

DECLARE @s varchar(8000)
DECLARE @sqlStr varchar(8000)

SET @s = ''

SELECT @s=@s+',['+RTRIM(bkName)+'] = SUM(CASE b.bkID when '''+RTRIM(bkID)+''' then b.doCount else 0 end)'
FROM UseCompany_BK
ORDER BY bkID ASC

SET @sqlStr = '
SELECT
选择=a.s,
序号=CAST(a.sendID as varchar(30)),
发票号码=a.PID,
单位名称=IsNull(a.companyName,''''),
地址=IsNull(a.sendAddress,''''),
段别=a.sendInArea,
电话=IsNull(a.receiveTel,''''),
发行员=a.markEr,
转到部门=c.treeName
'+ @s +',
备注=a.contentStr

FROM SendColl a,BK_BusinessData b,TreeInfo c

WHERE a.sendID = b.collID
AND a.sendInAreaID like c.areaID + ''%''
AND Len(c.areaID)>5
AND c.treeTypeName=''FUNCTION''
AND a.addEr = '''+ @bmID +'''
AND (isOut =1 OR isOut = 2) AND b.collTypeName=''SEND''
'

IF @num1 = 0
SET @sqlStr = @sqlStr + ' AND num1 = 0 '
ELSE
IF @num1 = 1
SET @sqlStr = @sqlStr + ' AND num1 = 1'
ELSE
SET @sqlStr = @sqlStr + ' AND (num1 = 0 OR num1 = 1) '

IF @startDate <> '' AND @endDate <> ''
SET @sqlStr = @sqlStr+ ' and a.addDate >= '''+ @startDate +' 0:00:00'' and a.addDate < '''+ @endDate +' 23:59:59'''

if @humanID <> ''
SET @sqlStr = @sqlStr + ' and c.treeID like '''+ @humanID +'%'' '

if @startID <> '' and @endID <> ''
SET @sqlStr = @sqlStr + ' and sendID >='+ @startID +' and sendID <='+@endID

SET @sqlStr = @sqlStr + '
GROUP BY a.PID,a.sendID,a.companyName,a.sendAddress,a.receiveTel,a.sendinarea,a.marker,a.contentStr,a.addEr,c.treeName,a.s
ORDER BY a.sendID desc
'

EXEC(@sqlStr)

[wzy_love_sly]

为什么设置的日期间隔越短，查询越慢？
-------------------------------------
长了会快吗

[hanbaojun]

比如设置 从 2007-11-12 到 2007-12-23 出现的比较快的

但如果是设置 从 2007-12-20 到 2007-12-23 却提示查询超时。

即使在本机查询也是一样，不过我直接在Sql 2005的客户端里执行这个存储过程可以慢慢的出来结果，我自己写的winform程序里却提示超时！

[dawugui]

还有这种怪现象?

数据少了反而查不出来?

[dawugui]

这个问题值得关注.

[jinjazz]

在查询计划里面看一下就明了了

[dawugui]

自己单步跟踪一下,看是不是时间的问题.

[wzy_love_sly]

csdn这几天完蛋了

[fcuandy]

ALTER   PROCEDURE   [dbo].[SendColl_SD_LIST_OUT] 

@bmID                   varchar(50), 
@startDate       varchar(50), 
@endDate         varchar(50), 
@humanID       varchar(50), 
@startID               varchar(50), 
@endID                 varchar(50), 
@num1                   int 
@mysql	VARCHAR(2000)
AS 
...
...

SET @mySQL = @sqlSTR

在你的winform里,把sql语句取出来看一下. 就知道你传的参数是否会有异常了.

或者直接在sql事件探查器里看.

[dmarkplus]

要不在winform 里设置timeout？

[dawugui]

wzy_love_sly

csdn这几天完蛋了

--------------------

哈哈,这下,你完蛋了,这里有几个鸟人专门关注你这样的骚动分子.