473,387 Members | 1,798 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

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

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
0 1660

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

Similar topics

7
by: Bill Kellaway | last post by:
Hi there - this should be fairly simple for someone. Basically I can't figure out how to pass the parameters from ASP to a Stored Procedure on SQL. Here's my code: I just need to help in...
5
by: Raj | last post by:
Hi all, Can anyone help me with a script which would delete files or move them to a different folder at some scheduled time..! Please.....!!! Thanks in advance...
5
by: Warren Wright | last post by:
Hi group, I have a select statement that if run against a 1 million record database directly in query analyzer takes less than 1 second. However, if I execute the select statement in a stored...
2
by: Begoņa | last post by:
in my java application I've made a call to this stored procedure CREATE procedure pruebaICM @pANI varchar(20), @pTABLA varchar(20), @pInsert varchar(500), @pUpdate varchar(1000), @pFLAG...
1
by: Witold Iwaniec | last post by:
Hi I have a procedure that calls a view. The view is built with some outer joins but it performs fine. If I run in Query Analyzer select count(*) from long_name_view where name_id = 'AAA'
2
by: Crazy Cat | last post by:
Hi, I wrote the following in the SQL Server 2005 Express Management Studio Query Analyzer and hit execute. Even though Query Analyzer indicated success when I hit refresh on the stored...
11
by: c676228 | last post by:
Hi everyone, I am just wodering in asp program, if there is anybody writing store procedure for inserting data into database since there are so many parameters need to be passed into store...
9
by: Frawls | last post by:
Hi I Am am having problems with a stored Procedure that i wrote. Basically whats happening is that the Stored procedure Runs fine when i EXECUTE it in SQL Query analyzer. But when i debug...
1
by: codefragment | last post by:
Hi I've heard 2 things recently, can I confirm if their true/false? (1) If you have a stored procedure and you want to optimise it you can call exec proc1, you could also use define/set for...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.