Slow stored procedure 1

[Waynest]

Hi

I've written a stored procedure which populates a table which acts as a recordsource for some reports. When I run the sp manually it takes about 20secs which is fine. But I've set up a job in enterprise manager to run the sp every hour and it takes 20 mins to complete when run from here! Any ideas why please?

 

[tlbroadbent]

The first quesion that comes to my mind is, "Is the table being updated in use by other users during the scheduled update?" The table or pages of the table may be locked. Have you checked to see if locking is occurring during the scheduled run? Locking could cause that kind of slow down.

How many records are being inserted? Do the tables involved have indexes? What data types are being inserted? These factors can affect performance.

Can you post the SP? We might be able to find potential bottlenecks or recommend improvements. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Waynest]

Here's the sp, no laughing please I'm new to these

Alter Procedure CreateWIPNextOp
As

DECLARE @InCompTicketNo Char(7)
DECLARE @OpsTicketNo Char(7)
DECLARE @OpsOpCode Char(3)
DECLARE @OpsOpEmp Char(5)
DECLARE @OpsOpDate Char(8)
DECLARE @OpsStyleCode Char(3)
DECLARE @OpsSize Int
DECLARE @OpsQty Int
DECLARE @Found Bit

DECLARE @InComplete CURSOR
DECLARE @Ops CURSOR

TRUNCATE TABLE tmp_WIPNextOp

SET @Incomplete = CURSOR LOCAL SCROLL FOR
SELECT DISTINCT O.TicketNo FROM TicketOps O WHERE O.FinalOp = 1 AND O.OpDate IS NULL ORDER BY O.TicketNo
OPEN @Incomplete

FETCH NEXT FROM @Incomplete INTO @InCompTicketNo
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Ops = CURSOR LOCAL SCROLL FOR
SELECT O.TicketNo, O.OpCode, O.OpEmp, O.OpDate, T.StyleCode, T.Size, T.Qty FROM TicketOps O INNER JOIN Ticket T ON O.TicketNo = T.TicketNo WHERE O.TicketNo = @InCompTicketNo AND O.OpCode <> '400'
OPEN @Ops
FETCH LAST FROM @Ops INTO @OpsTicketNo, @OpsOpCode, @OpsOpEmp, @OpsOpDate, @OpsStyleCode, @OpsSize, @OpsQty
SELECT @Found = 0
WHILE @@FETCH_STATUS = 0 AND @Found = 0
BEGIN
IF @OpsOpEmp IS NOT NULL
BEGIN
SELECT @Found = 1
END
ELSE
BEGIN
FETCH PRIOR FROM @Ops INTO @OpsTicketNo, @OpsOpCode, @OpsOpEmp, @OpsOpDate, @OpsStyleCode, @OpsSize, @OpsQty
END
END
FETCH NEXT FROM @Ops INTO @OpsTicketNo, @OpsOpCode, @OpsOpEmp, @OpsOpDate, @OpsStyleCode, @OpsSize, @OpsQty
INSERT INTO tmp_WIPNextOp (TicketNo, Style, Size, OpCode, Qty) VALUES (@OpsTicketNo, @OpsStyleCode, @OpsSize, @OpsOpCode, @OpsQty)
CLOSE @Ops
DEALLOCATE @Ops
FETCH NEXT FROM @Incomplete INTO @InCompTicketNo
END

CLOSE @Incomplete
DEALLOCATE @Incomplete

return

The Ticket & TicketOps tables which the SELECT statements run against are very heavily used, but this is the case whether I run it manually or from a job, so why the big time difference?
The Ticket & TicketOps tables dont have any indexes yet other than the PKs which are TicketNo on the Ticket table and TicketNo, Seq, OpCode on the TicketOps table.

 

[tlbroadbent]

There may be other modifications that will improve performance but here is a quick change that may help.

Add the following lines to the SP just before truncating the table.[ul]Begin Transaction

Select count(*) From tmp_WIPNextOp With (tablockx holdlock) Where 1=2[/ul]Tablockx forces an exclusive table lock.
Use tablock to do a non-exclusive lock.
Holdlock tells SQL Server to hold locks for duration of the transaction.

Add Commit Transaction just before the end of the SP. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[Waynest]

Terry

Those lines have brought the exec time down to 20 secs as when run manually, but I'm not sure if I understand why.
I still get the same slowdown when the job runs on my test database where nobody else is accessing the tables. Thanks very much for solving my problem, but can you shed any light on why locking causes delays depending on how the sp is executed?

 

[rajeevnandanmishra]

Hi Waynest,

Try adding two more lines to your stored procedure. This will help you to find, how much time it is taking to execute the procedure manually and when executed through job.
--------------------------------
exec master..xpcmdshell &quot;time /t >>c:\test.txt&quot;,NO_OUTPUT
Your normal procedure code
exec master..xpcmdshell &quot;time /t >>c:\test.txt&quot;,NO_OUTPUT
--------------------------------
First execute the procedure manually, and then execute it through job. If there is any difference in time taken that means the job itself is taking more time.