By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

The scripts in the store procedure run slowly, but quickly exec in the Query Analyzer

P: 1
Hi,

When i call the procedure sp_jysczld_new from the Query Analyzer, it runs slowly.
But when i extract the sql scripts from the procedure sp_jysczld_new, it runs quickly.
Why? I don't understand.

store procedure sp_jysczld_new:
--------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_jysczld_new
@gs_userid CHAR(16),
@location char(10),
@pri char (10)
AS

SELECT Z.ORDER_NUM,ZI.ORDER_ITEM,Z.CUSTOM_ID,ZI.PRODUCT_I D,ZI.PRODUCT_NAME,ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L _HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQ TYS,ZI.plannum, ZI.totalwgt,
ZI.banglong,ZI.bangzs,ZI.qgzs ,ZI.BANG_GRADE,ZI.PRI, ZI.LOCATION,Z.END_DATE, ZI.NOTESZ AS NOTES--,ZI.MJCOUNT

INTO #Z

FROM ORDERZ Z INNER JOIN ORD_ITEMZ ZI ON Z.ORDER_ID=ZI.ORDER_ID

WHERE (ZI.over_work in ( 'A','B','D') ) AND ZI.PC_MODE = 'Y' and ZI.sgqtys > 0
AND (ZI.location = @location or @location = 'ALL')
AND (ZI.pri = @pri or @pri = 'ALL')
AND Z.order_num in ( select order_num from temp_order_id where username = @gs_userid)


drop table #Z


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------

The scripts which i extract from the store procedure(the same as the store procedure):
---------------------------------------------------

declare @gs_userid char(16)
declare @location char(10)
declare @pri char(10)

set @gs_userid = 'mmm'
set @location = 'ALL'
set @pri = 'ALL'

SELECT Z.ORDER_NUM,ZI.ORDER_ITEM,Z.CUSTOM_ID,ZI.PRODUCT_I D,ZI.PRODUCT_NAME,ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L _HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQ TYS,ZI.plannum, ZI.totalwgt, ZI.banglong,ZI.bangzs,ZI.qgzs ,ZI.BANG_GRADE,ZI.PRI, ZI.LOCATION,Z.END_DATE, ZI.NOTESZ AS NOTES--,ZI.MJCOUNT

INTO #Z

FROM ORDERZ Z INNER JOIN ORD_ITEMZ ZI ON Z.ORDER_ID=ZI.ORDER_ID

WHERE (ZI.over_work in ( 'A','B','D') ) AND ZI.PC_MODE = 'Y' and ZI.sgqtys > 0
AND (ZI.location = @location or @location = 'ALL')
AND (ZI.pri = @pri or @pri = 'ALL')
AND Z.order_num in ( select order_num from temp_order_id where username = @gs_userid)
drop table #z

---------------------------------------------------
Sep 16 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.