怎么存储过程比一般SQL语句还慢?

[dong894]

存储过程
--订单打印
ALTER proc Order_Print
@pickPosCode nvarchar(50)='', --提货点编号
@pickPosSign nvarchar(50)='', --提货点缩位码
@Order_code1 nvarchar(50)='', --订单号1
@Order_code2 nvarchar(50)='', --订单号2
@Order_date datetime , --订单日期
@Sequent_code nvarchar(50)='' --批次
as
select distinct worknumber_head.order_code,
adviser_code,worknumber_head.adviser_date,adviser_name
,worknumber_head.pickPoscode,worknumber_head.pickPosSign
,worknumber_detail.produce_code,
worknumber_detail.produce_name,
worknumber_detail.site_code,
amount,price,sku,
isnull(pick_area_Code,0) as pick_area_code,
worknumber_head.deliver_address,pickPosflag
from worknumber_head
left outer join worknumber_detail
on worknumber_head.order_code=worknumber_detail.bill_number
and worknumber_head.adviser_date=worknumber_detail.adviser_date
left outer join ss_site_produce
on ss_site_produce.site_code=worknumber_detail.site_code
where worknumber_head.adviser_date=@Order_date and worknumber_head.pickPossign=@pickPossign
and worknumber_head.pickPosCode=@pickPosCode and worknumber_head.Sequent_code=@Sequent_code

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

在查询分析器中执行exec dbo.Order_Print '0067','NB-','','','2004-11-17','02'，耗时0:00:08，记录行386行

在查询分析器中执行
select distinct worknumber_head.order_code,
adviser_code,worknumber_head.adviser_date,adviser_name
,worknumber_head.pickPoscode,worknumber_head.pickPosSign
,worknumber_detail.produce_code,
worknumber_detail.produce_name,
worknumber_detail.site_code,
amount,price,sku,
isnull(pick_area_Code,0) as pick_area_code,
worknumber_head.deliver_address,pickPosflag
from worknumber_head
left outer join worknumber_detail
on worknumber_head.order_code=worknumber_detail.bill_number
and worknumber_head.adviser_date=worknumber_detail.adviser_date
left outer join ss_site_produce
on ss_site_produce.site_code=worknumber_detail.site_code
where worknumber_head.adviser_date='2004-11-17' and worknumber_head.pickPossign='NB-'
and worknumber_head.pickPosCode='0067' and worknumber_head.Sequent_code='02'

耗时0:00:01，记录行386行

我实在是搞不懂了，怎么存储过程会比一般SQL语句还慢呢？
有哪位高手兄能告诉我这是怎么回事
服务器是windows2000 server sp4+sql server2000 sp3

[jiangchuandong]

因为先在查询分析器里运行过存储过程，然后运行sql，刚才查询的内容在内存里，当然sql要快

[davorsuker39]

同意一楼的说法
如果你把 '0067','NB-','','','2004-11-17','02' 提前写入存储过程，那么时间应该就差不多

[davorsuker39]

ALTER proc Order_Print
@pickPosCode nvarchar(50)='0067', --提货点编号
@pickPosSign nvarchar(50)='NB-', --提货点缩位码
@Order_code1 nvarchar(50)='', --订单号1
@Order_code2 nvarchar(50)='', --订单号2
@Order_date datetime='2004-11-17' , --订单日期
@Sequent_code nvarchar(50)='02' --批次

[pbsql]

SQL语句里条件写成常量与写成变量效率是不一样的，写成常量时能利用上索引，写成变量则不能
具体讨论参阅本帖：
http://search.csdn.net/Expert/topic/2605/2605466.xml?temp=.586529

[dong894]

谢谢大家帮忙，问题已经解决了，原来的是索引的关系，我用索引优化向导优化了一下，现在存储过程也只要1秒了，现在散分

[8LY8Apollo]

在2台机器上测试。
楼上的，如果客户机连接查询的话是在服务器内存里呢，还是返回到了客户机器的内存中？

[WangZWang]

删除过程后重建过程试试.

[netdreamingboy]

这个问题如果懂得SQL语句的执行过程就很好解释
解释语句-》优化语句-》查询
前面两个步骤需要时间的，解释的结果cache
这样下一次执行同样的语句可以直接在cache获取解释结果
时间当然更加快