September 22, 2006 at 1:28 pm
I have a stored procedure that when run by itself...
EXEC sp_leeland
takes under 30 seconds to complete.
NOW if I take that same stored procedure and put it into a job step and run the job the job takes upwards of 9 minutes to run.
What are some initial things I can look at to see why this is taking so long to run?
Thanks,
Leeland
September 22, 2006 at 4:06 pm
That is quite odd since it is a parameterless procedure. Does the executionplan changes when you execute it under sqlagent's permission? Is there additional locking? Are the connectionsettings the same (SET NOCOUNT ...)? Are there lots of other scheduled jobs running at the same time?
September 23, 2006 at 5:38 am
The execution is the same, I have NOCOUNT ON, and the time doesn't matter when I run it (job schedule) always seems to be slow.
I don't know about the agent's permissions, the SP just uses one table on the local database, pulls some records out and puts them into a perminant staging table...so it isn't like it is going out to other places to complete the task, everything is local...
I know it seems very odd and I have never come across such a thing...which is why I felt I was missing something obvious.
September 23, 2006 at 9:41 am
Are there +20 jobs active at the same time? Sql server agent has a limited pool of threads.
September 23, 2006 at 5:36 pm
You know, Lee, I don't believe it could take 30 seconds to just pull some records and copy it to another table, even if that table is another database.
Unless these "some" are couple of millions.
So, what does your SP actually do?
_____________
Code for TallyGenerator
September 25, 2006 at 8:55 am
This is on a test server I am running the sp on, so I am the only one on it. And the process is the only thing running. So I do not think it would have to do with multiple processes running at the same time.
September 25, 2006 at 9:00 am
Hi Sergiy,
I am not 100% following your comment that you don't believe it could take 30 seconds...but I will try my best at the reply. The SP goes through a table that has around 200,000 records and filters out records based on criteria.
By the time it is finished it has around 36,000 records in the local temp table. It uses a cursor which I am working on getting rid of...
If you are interested in seeing the SP here is another post of mine that lists the code.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=308727&p=2
It is a little ways down on the page.
September 25, 2006 at 4:40 pm
INSERT INTO tb_CARDs_MTLInformation
(IN_MatNum, IN_VerNumMat, IN_MatDesc, IN_SpecSta, IN_OldMatID, IN_MatTypeCode, IN_MatSubTypeCode)
SELECT MatNum, VerNumMat, MatDesc, SpecSta, OldMatID, MatTypeCode, MatSubTypeCode
FROM DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec S
WHERE MatTypeCode IN (7,8,15)
AND NOT EXISTS (select 1 from tb_CARDs_MTLInformation I where I.IN_MatNum = S.IN_MatNum)
UPDATE I
SET IN_VerNumMat = COALESCE (S1.IN_VerNumMat, S2.IN_VerNumMat, S3.IN_VerNumMat),
..... -- same for other colums
FROM tb_CARDs_MTLInformation I
LEFT JOIN DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec S1 ON I.IN_MatNum = S1.IN_MatNum AND AND S1.SpecSta IN ('I')
LEFT JOIN DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec S2 ON I.IN_MatNum = S2.IN_MatNum AND AND S2.SpecSta IN ('H')
LEFT JOIN DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec S3 ON I.IN_MatNum = S3.IN_MatNum AND AND S3.SpecSta IN ('A', 'D')
WHERE S1.IN_MatNum IS NOT NULL OR S2.IN_MatNum IS NOT NULL OR S3.IN_MatNum IS NOT NULL
That's your procedure. Not much to work on.
If you have duplicated IN_MatNum in "DS07108TEST.CurwSpecLocal.dbo.tb_MatSpec" you need to use subqueries for inner joins.
But revise the way it's done currently.
GROUP BY...., VerNumMat, ....
HAVING VerNumMat = MAX(VerNumMat)
does not really work. It's always equal. Except when it's NULL.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply