存储过程在查询分析器中1秒获取结果，而在VB6中等几分钟都是无响应
程序执行环境：
WIN XP SP3
SQL 2005
VB6
（在我自己电脑上）


这个存储过程用了2年了，一直很快
昨天开始有问题了。搞不懂。
在查询分析器里运行下面的存储过程
结果是1秒就获取了结果（设定时间为最近一个月的时间）
而放到VB6中就等几分钟都无响应，最后手动关掉软件
VB6中使用ADODC。
该ADODC的代码是：
With Adodc1
.ConnectionString = cnn.cnnStr
.CommandType = adCmdText
.CommandTimeout = 0
.RecordSource = clsReport1.SQL
.Refresh
End With

clsReport1.SQL=“exec dbo.S_GetYRReportVolumePeopleWage '2013-03-01','07:00:00','2013-03-31','07:00:00'”

网上查询说是存储过程的执行计划的问题，而使用了
exec sp_recompile @objname='[S_GetYRReportVolumePeopleWage]'
后没有效果。仍然是查询分析器中快，程序中调用慢。
拜求解决方案

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



ALTER            Proc [dbo].[S_GetYRReportVolumePeopleWage]
(
@m_SDate varchar(100),
@m_STime varchar(100),
@m_EDate varchar(100),
@m_ETime varchar(100)
)
AS
BEGIN



set @m_SDate=@m_SDate + ' ' + @m_STime
set @m_EDate=@m_EDate + ' ' + @m_ETime




select ISNULL(G_YRPeople.B_Class,'') as B_Class,B_People,
sum(case B_Process when '染色' then B_FactSum else null end) as '染色',
sum(case B_Process when '拉色' then B_FactSum else null end) as '拉色',
sum(case B_Process when '堵布回染' then B_FactSum else null end) as '堵布回染',
sum(case B_Process when '断头回染' then B_FactSum else null end) as '断头回染',
sum(case B_Process when '加色' then B_FactSum else null end) as '加色',
sum(case B_Process when '加色回染' then B_FactSum else null end) as '加色回染',
sum(case B_Process when '前处理' then B_FactSum else null end) as '前处理',
sum(case B_Process when '后处理' then B_FactSum else null end) as '后处理',
sum(case B_Process when '洗缸' then B_FactSum else null end) as '洗缸',
sum(case B_Process when '冲缸' then B_FactSum else null end) as '冲缸',
sum(case B_Process when '剥色' then B_FactSum else null end) as '剥色',
sum(case B_Process when '落水' then B_FactSum else null end) as '落水',

sum(case B_Process when '染色' then B_FactSum else 0 end) +
sum(case B_Process when '拉色' then B_FactSum else 0 end) +
sum(case B_Process when '堵布回染' then B_FactSum else 0 end) +
sum(case B_Process when '断头回染' then B_FactSum else 0 end) +
sum(case B_Process when '加色' then B_FactSum else 0 end) + 
sum(case B_Process when '加色回染' then B_FactSum else 0 end) +
sum(case B_Process when '前处理' then B_FactSum else 0 end) + 
 
sum(case B_Process when '后处理' then B_FactSum else 0 end) +
sum(case B_Process when '洗缸' then B_FactSum else 0 end) +
sum(case B_Process when '冲缸' then B_FactSum else 0 end) +
sum(case B_Process when '剥色' then B_FactSum else 0 end) +
sum(case B_Process when '落水' then B_FactSum else 0 end) as B_FactSum,

@m_SDate as 起始日期,
@m_EDate as 终止日期,
Convert(varchar(100),GetDate(),20) as B_PrintDate
from 
(

Select B_Pople as B_People,B_Process,
sum(ISNULL(B_Volume,0)) as B_Volume,sum(ISNULL(B_Volume,0)) * G_CJPriceYR.B_Price as B_FactSum
From
(
Select G_YRBill.B_GangHao,G_YRBill.B_Pople,G_YRBill.B_Date,
G_YRBill.B_Rate,G_YRBill.B_Process,
G_YRBill.B_Rate * 0.1 * G_YRGang.B_OutPut as B_Volume
From G_YRBill,G_YRGang
Where 1=1
And G_YRBill.B_GangHao=G_YRGang.B_GangHao
And G_YRBill.B_Date Between cast(@m_SDate as datetime) And cast(@m_EDate as datetime)
And B_VolumeValidPeople=1
And G_YRBill.B_GangHao<>'上中'
And G_YRBill.B_GangHao<>'下中'


And G_YRBill.B_ID Not In
(
--时间段内所有公斤数<50公斤的
Select B_ID
From
(
Select G_YRBill.B_ID,
dbo.[GetQtyFact](G_YRBillDetailBarCode.B_BarCode) as B_Qty
From G_YRBill,G_YRGang,G_YRBillDetailBarCode
Where 1=1
And G_YRBill.B_GangHao=G_YRGang.B_GangHao
And G_YRBill.B_Date Between cast(@m_SDate as datetime) And cast(@m_EDate as 

datetime)
And B_VolumeValidPeople=1
And G_YRBill.B_GangHao <>'上中'
And G_YRBill.B_GangHao <>'下中'
And G_YRBill.B_ID=G_YRBillDetailBarCode.B_ID
) as P
Where isnull(P.B_Qty,0)<50
)



) as P Left Outer Join G_CJPriceYR 
ON G_CJPriceYR.B_Type='非中样'
And G_CJPriceYR.B_ProcessName=P.B_Process

Group by B_Pople,B_Process,G_CJPriceYR.B_Price
)  as P Left Outer Join G_YRPeople
ON P.B_People=G_YRPeople.B_Name
Group by B_People,ISNULL(G_YRPeople.B_Class,'')
Order By ISNULL(G_YRPeople.B_Class,''),B_People


END

[解决办法]
你的VB代码能确保没有任何人改动过？
[解决办法]
“查询分析器中快，程序中调用慢。”
有可能是参数嗅探问题，也有可能被缓存的计划刚好是一个比较差的计划，
你在优化器中是怎么查的？ 用
exec dbo.S_GetYRReportVolumePeopleWage '2013-03-01','07:00:00','2013-03-31','07:00:00来查询的，还是直接把参数替代为具体值，然后直接执行SP里的T-SQL ？
[解决办法]

引用:

问题找到了，在
--时间段内所有公斤数<50公斤的
下面的代码出的问题
NOT IN的问题
无奈先采用了中间表的办法解决了，
速度很快。

那么这样的NOT IN，如果不采用中间表应该怎么做 ？

改成not exists看看
[解决办法]

引用:

问题找到了，在
--时间段内所有公斤数<50公斤的
下面的代码出的问题
NOT IN的问题
无奈先采用了中间表的办法解决了，
速度很快。

那么这样的NOT IN，如果不采用中间表应该怎么做 ？

不已经给你提示了嘛，你说在“查询分析器中快，程序中慢”，所以首先要分析为什么在是查询分析器快，程序中慢，比如调用方式的不同会引起查询计划的不同，等

当然，使用临时表可以降低语句的复杂度，使得QO更能够找出比较好的查询计划。
[解决办法]
用Profiler Trace追踪一下，看看代码发送到数据库到数据库返回给客户端的时间。同时查看执行计划看看是否可以改进。