beginner: long running sp

hi,
i have a sp that takes around 5-6 sec even though there're around ONLY 5000,
therefore, i need to tune it.
any ideas for me to do so?
thanks a lot!
PS.
Table
====
OutboundQueue [JobID, ....] JobID - P.K
OutboundQueueItem [JobItemID, JobID, .....] JobItemID - P.K and JobID - F.K
SP
===
CREATE PROC GetJobItems @.Machine as varchar(20), @.OutboundType as int as
declare @.JobID as bigint
declare @.JobItemID as bigint
BEGIN
SET NOCOUNT ON
BEGIN Tran
-- change the un-caught local print failed jobs to failure (status =5)
update outboundqueueitem set Status =5 where JobItemID in (select
jobitemid from outboundqueueitem where OutboundType = 1 and isLocalPrint =
1 and Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10)
-- Select one JobItem from OutboundQueue
select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority desc,
SubmissionDate asc
-- ****************************************
*********************
-- select JobItems within that JobID just got
-- ****************************************
*********************
-- Create temp Table for storing JobItemIDs to be processed
CREATE TABLE #tmpJobItemID(tJobItemID bigint)
INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
@.OutboundType
-- Lock the Job at OutboundQueue, and update the status to "Working"
update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
Status = 2, LastWorkingDate = getDate() where JobItemID in (select
tJobItemID from #tmpJobItemID)
-- return recordset based on the sequence
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID) order by submissiondate
COMMIT Tran
ENDHello Mullin,
A lot of this is going to depend on your indexing. In order to really tell
what is going on we will need more information. Some of the best information
comes from looking at the execution plan and also looking at what IO occurs.
To get the I/O information do this before you call your procedure.
SET STATISTICS IO ON
EXEC <proc>
SET STATISTICS IO OFF
To view the execution plan for posting here you can do
SET SHOWPLAN_TEXT ON
GO
exec <proc>
GO
SET SHOWPLAN_TEXT OFF
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> hi,
> i have a sp that takes around 5-6 sec even though there're around ONLY
> 5000, therefore, i need to tune it.
> any ideas for me to do so?
> thanks a lot!
> PS.
> Table
> ====
> OutboundQueue [JobID, ....] JobID - P.K
> OutboundQueueItem [JobItemID, JobID, .....] JobItemID - P.K and JobID
> - F.K
> SP
> ===
> CREATE PROC GetJobItems @.Machine as varchar(20), @.OutboundType as
> int as
> declare @.JobID as bigint
> declare @.JobItemID as bigint
> BEGIN
> SET NOCOUNT ON
> BEGIN Tran
> -- change the un-caught local print failed jobs to failure (status
> =5)
> update outboundqueueitem set Status =5 where JobItemID in (select
> jobitemid from outboundqueueitem where OutboundType = 1 and
> isLocalPrint =
> 1 and Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10)
> -- Select one JobItem from OutboundQueue
> select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0
> and
> AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority
> desc,
> SubmissionDate asc
> -- ****************************************
*********************
> -- select JobItems within that JobID just got
> -- ****************************************
*********************
> -- Create temp Table for storing JobItemIDs to be processed
> CREATE TABLE #tmpJobItemID(tJobItemID bigint)
> INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem
> where JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and
> OutboundType = @.OutboundType
> -- Lock the Job at OutboundQueue, and update the status to "Working"
> update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
> Status = 2, LastWorkingDate = getDate() where JobItemID in (select
> tJobItemID from #tmpJobItemID)
> -- return recordset based on the sequence
> select * from OutboundQueueItem where JobItemID in (select
> tJobItemID from
> #tmpJobItemID) order by submissiondate
> COMMIT Tran
> END
>|||but, i'm using temp table, and can't output the text version of execution
plan with the following error. but i can view the graphical execution plan
Server: Msg 208, Level 16, State 1, Procedure GetJobItems, Line 23
Invalid object name '#tmpJobItemID'.
any ideas?
btw, the following is the server trace i select at Query Analyzer:
set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0
select IS_SRVROLEMEMBER ('symin') SQL:StmtCompleted 0 0 0 0
SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0
SET NOCOUNT ON SP:StmtCompleted 0 0 0 0
BEGIN Tran -- change the un-caught local print failed jobs to failure
(status =5) SP:StmtCompleted 0 0 0 0
CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem FOR
UPDATE AS SP:StmtCompleted 0 0 0 0
IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
select JobItemID from inserted SP:StmtCompleted 0 0 2 0
OPEN jobitemid_cursor -- Perform the first fetch and store the values in
variables. -- Note: The variables are in the same order as the columns --
in the SELECT statement. SP:StmtCompleted 0 0 14 0
FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check @.@.FETCH_STATUS
to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
update outboundqueueitem set Status =5 where JobItemID in (select jobitemid
from outboundqueueitem where OutboundType = 1 and isLocalPrint = 1 and
Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10) --
Select one JobItem from SP:StmtCompleted 0 0 104 0
select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority desc,
SubmissionDate asc --
****************************************
************* SP:StmtCompleted 0 0
40 0
CREATE TABLE #tmpJobItemID(tJobItemID bigint) SP:StmtCompleted 0 0 10 0
INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
@.OutboundType -- Lock the Job at OutboundQueue, and update the status
to "Working" -- SP:StmtCompleted 0 0 48 0
CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem FOR
UPDATE AS SP:StmtCompleted 0 0 0 0
IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
select JobItemID from inserted SP:StmtCompleted 0 0 2 0
OPEN jobitemid_cursor -- Perform the first fetch and store the values in
variables. -- Note: The variables are in the same order as the columns --
in the SELECT statement. SP:StmtCompleted 0 0 14 0
FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check @.@.FETCH_STATUS
to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine, Status
= 2, LastWorkingDate = getDate() where JobItemID in (select tJobItemID from
#tmpJobItemID) -- return recordset based on the sequence -- select
* from Outbou SP:StmtCompleted 0 0 68 0
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID) order by submissiondate SP:StmtCompleted 0 0 18 0
COMMIT Tran SP:StmtCompleted 0 0 0 0
getjobitems 'aaa', 1 SQL:StmtCompleted 62 62 66 0
SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0
thanks!
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:309826632428962507725664@.news.microsoft.com...
> Hello Mullin,
> A lot of this is going to depend on your indexing. In order to really tell
> what is going on we will need more information. Some of the best
information
> comes from looking at the execution plan and also looking at what IO
occurs.
> To get the I/O information do this before you call your procedure.
> SET STATISTICS IO ON
> EXEC <proc>
> SET STATISTICS IO OFF
> To view the execution plan for posting here you can do
> SET SHOWPLAN_TEXT ON
> GO
> exec <proc>
> GO
> SET SHOWPLAN_TEXT OFF
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>
>|||Mullin
I'd move the CREATE tempoary table at the beginning of the stored procedure.
If you re-write it as
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID where #tmpJobItemID.JobItemID =OutboundQueueItem .JobItemID )
order by submissiondate
check it out.
Also . run SQL Server Profile to see what is going on?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23RRxo6NCFHA.3588@.TK2MSFTNGP11.phx.gbl...
> but, i'm using temp table, and can't output the text version of execution
> plan with the following error. but i can view the graphical execution
plan
> Server: Msg 208, Level 16, State 1, Procedure GetJobItems, Line 23
> Invalid object name '#tmpJobItemID'.
> any ideas?
> btw, the following is the server trace i select at Query Analyzer:
> set noexec off set parseonly off SQL:StmtCompleted 0 0 0 0
> select IS_SRVROLEMEMBER ('symin') SQL:StmtCompleted 0 0 0 0
> SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0
> SET NOCOUNT ON SP:StmtCompleted 0 0 0 0
> BEGIN Tran -- change the un-caught local print failed jobs to failure
> (status =5) SP:StmtCompleted 0 0 0 0
> CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem
FOR
> UPDATE AS SP:StmtCompleted 0 0 0 0
> IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
> select JobItemID from inserted SP:StmtCompleted 0 0 2 0
> OPEN jobitemid_cursor -- Perform the first fetch and store the values
in
> variables. -- Note: The variables are in the same order as the
olumns --
> in the SELECT statement. SP:StmtCompleted 0 0 14 0
> FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check
@.@.FETCH_STATUS
> to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
> WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
> CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> update outboundqueueitem set Status =5 where JobItemID in (select
jobitemid
> from outboundqueueitem where OutboundType = 1 and isLocalPrint = 1 and
> Status = 2 and DateDiff(mi, LastWorkingDate, getDate()) > 10) --
> Select one JobItem from SP:StmtCompleted 0 0 104 0
> select top 1 @.JobID=JobID from OutboundQueueItem where isLocked = 0 and
> AddToProcessing = 1 and OutboundType = @.OutboundType order by Priority
desc,
> SubmissionDate asc --
> ****************************************
************* SP:StmtCompleted 0 0
> 40 0
> CREATE TABLE #tmpJobItemID(tJobItemID bigint) SP:StmtCompleted 0 0 10
0
> INSERT INTO #tmpJobItemID SELECT JobItemID FROM OutboundQueueItem where
> JobID = @.JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType =
> @.OutboundType -- Lock the Job at OutboundQueue, and update the
status
> to "Working" -- SP:StmtCompleted 0 0 48 0
> CREATE TRIGGER tr_OutboundQueueItem_Update ON dbo.OutboundQueueItem
FOR
> UPDATE AS SP:StmtCompleted 0 0 0 0
> IF NOT UPDATE(Status) SP:StmtCompleted 0 0 0 0
> select JobItemID from inserted SP:StmtCompleted 0 0 2 0
> OPEN jobitemid_cursor -- Perform the first fetch and store the values
in
> variables. -- Note: The variables are in the same order as the
olumns --
> in the SELECT statement. SP:StmtCompleted 0 0 14 0
> FETCH NEXT FROM jobitemid_cursor INTO @.JobItemID -- Check
@.@.FETCH_STATUS
> to see if there are any more rows to fetch. SP:StmtCompleted 0 0 0 0
> WHILE @.@.FETCH_STATUS = 0 SP:StmtCompleted 0 0 0 0
> CLOSE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> DEALLOCATE jobitemid_cursor SP:StmtCompleted 0 0 0 0
> update OutboundQueueItem set isLocked = 1, MachineLocked = @.Machine,
Status
> = 2, LastWorkingDate = getDate() where JobItemID in (select tJobItemID
from
> #tmpJobItemID) -- return recordset based on the sequence --
select
> * from Outbou SP:StmtCompleted 0 0 68 0
> select * from OutboundQueueItem where JobItemID in (select tJobItemID from
> #tmpJobItemID) order by submissiondate SP:StmtCompleted 0 0 18 0
> COMMIT Tran SP:StmtCompleted 0 0 0 0
> getjobitems 'aaa', 1 SQL:StmtCompleted 62 62 66 0
> SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0
> thanks!
>
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:309826632428962507725664@.news.microsoft.com...
tell
> information
> occurs.
>