SQL Server Performance Forum – Threads Archive

How store procedure use temporary table

Hi, When i call the procedure sp_jysczld_new from the Analysis Query, 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_ID,ZI.PRODUCT_NAME, ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L_HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQTYS,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_ID,ZI.PRODUCT_NAME, ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L_HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQTYS,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 —————————————————

Have you compiled the stored procedure and triggers too on the tables that are involved?
Also what is the index strategy and their defragmentation methods. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

See if the response time improves if you use CREATE TABLE for the #Z temp table, rather than the SELECT INTO syntax: CREATE TABLE #Z (<column definitions&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> INSERT INTO #Z (<column list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> SELECT <column list> FROM ….. Your syntax can cause locking issues. Other than that, follow up on Satya’s suggestions.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan See if the response time improves if you use CREATE TABLE for the #Z temp table, rather than the SELECT INTO syntax: CREATE TABLE #Z (<column definitions&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> INSERT INTO #Z (<column list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> SELECT <column list> FROM ….. Your syntax can cause locking issues. Other than that, follow up on Satya’s suggestions. <hr height="1" noshade id="quote"></blockquote id="quote"> Both Select into and create table takes the same amount of time In first case, Is locking the matter? Madhivanan Failing to plan is Planning to fail

Have you checked execution plans for both methods?

I didnt check it but I should have checked [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

‘ /> INSERT INTO #Z (<column list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> SELECT <column list> FROM ….. Your syntax can cause locking issues. Other than that, follow up on Satya’s suggestions. <hr height="1" noshade id="quote"></blockquote id="quote"> Thanks everybody. I had been try like Adriaan said, but it didn’t work.And the database dose not has a triggers. Will the execution plan different when involved in different environment, (such as the Query Analyzer, and the client application involved the store procedure)? The index had not been changed all the time.

When i use the profile to monitor the event, i found some message<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />TextData) "some event not report to the profile, because the server has been reach the max memory of the process" and a lot of "Lock:Acquied", "Lock:Released" event class. My question is: if it reach the max memory of the process, shall i config the process’s memory bigger than the setting now?

And the cpu is 100%.
Is there a block or lock? Any suggestions will be useful.Thanks

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan I didnt check it but I should have checked [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> As Madhivanan said, the plan is not good, but i just want to know what happen, why the performances will be so different.

What I mean is that we can know more about the reason if we compare both execution plans.

But how can i get the execution plans, there is no response when i call the store procedure.
And when i write the sql in the procedure like that:
/////////////////////
SELECT Z.ORDER_NUM,ZI.ORDER_ITEM,Z.CUSTOM_ID,ZI.PRODUCT_ID,ZI.PRODUCT_NAME, ZI.LENGTH,ZI.CDGC,ZI.HEIGHT,ZI.L_HEIGHT,ZI.U_HEIGHT,ZI.CATEGORY,ZI.COLOR_ID,ZI.SGQTYS,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) ////////////////////
Than, everything is ok. The performance is as quickly as the scripts execute in Query Analyzer.
My question is why?

database size is almost 1.2G
Table orderz 2840kb
Table ord_itemz 218,568kb
Table temp_order_id 88kb The version of my SQL Server 2000 is evaluation edition cpu: intel x86 1.0G
Memory: 512M
Virtual memory: 768M

Are the columns that you deactivated in yout WHERE statement – ZI.Location and ZO.pri – covered by an index?

Hi, adriaan. Thanks for your reply. I haven’t consider the index. What i want to know is why the performance executing in Query Analyzer is better than calling from the store procedure. The Query Analyzer can output the rowsets(9058 row), but the store procedure didn’t do that, just make the cpu 100%, and the memory grow up all the time.

In QA, did you try with the criteria for ZI.Location and ZO.pri – did it hang too? Also, are you comparing a production server (probably multiple CPUs) with a test server (probably single CPU)? In that case try adding this as the very last part of your query for the production server: OPTION (MAXDOP 1) This disables parallellism – there is a known bug in SQL Server where it will use parallellism for a relatively simple query, and the query hangs. Option only helps on multi-CPU servers, but there is no harm when using it on a single CPU server.

The reason is that the stored proc, will use the parameter values when it is first called to determine its execution plan. In your case if the first time the proc is called, it is passed a valid location and pri, then it is likely to choose a different plan than if ALL are passed. The statement should be split into the four possible cases. location=ALL and pri=ALL, either one =ALL, and then neither=ALL as in if @location=’ALL’ and @pri=’ALL’
— select statement without the two clauses in the where clause
else if @location=’ALL’
— select statement with only the pri clause in the where clause
else if @pri=’ALL’
— select statement with only the location clause in the where clause
else
— select statement with both clauses in the where clause alternatively adding a WITH RECOMPILE to the proc creation should ‘fix’ the problem too, but with the overhead of compiling the proc for every single call Cheers
Twan

When i add the criteria in the store procedure, it hang, but QA not hang.
////
AND (ZI.location = @location or @location = ‘ALL’)
AND (ZI.pri = @pri or @pri = ‘ALL’)
//// I know that’s not a good plan. And i only want to know what happen.
I also post the topic on msdn, but nobody reply to me.
Puzzling.

]]>