September 6, 2011 at 2:28 am
Hi guys I have the following stored procecure
ALTER PROCEDURE [dbo].[InsertJobLog]
-- Add the parameters for the stored procedure here
@Action nvarchar(5),
@JobID nvarchar(255),
@SPName nvarchar(255)
AS
BEGIN
set nocount on
if @Action = 'Start'
begin
INSERT INTO JobLog (ParentJobID,StoredProcedureName,StartDatetime) VALUES(@JobID,@SPName,getdate())
--select @@identity
end
else begin
update joblog set enddatetime = getdate() where jobID = @jobID
end
set nocount off
END
Which writes a record to a log file with the start time (or end time if the action is not Start)
I use it like this in stored procedures
DECLARE @JobID int
EXEC dbo.insertjoblog
@Action = N'Start',
@JobID = @MasterJobID,
@SPName = 'BSC_Update_Ethnicity'
SET @JobID = @@IDENTITY
... do some stuff ...
EXEC dbo.insertjoblog
@Action = N'End',
@JobID = @JobID,
@SPName = 'BSC_Update_Ethnicity'
The Master Job ID is passed in as a parameter to the stored procedure and identifies the starting script.
This is taking anything up to three seconds from the time the script is executed to the time the record is written to the database - any idea why it should take so long - other database updates seem to happen with no delay.
Running SQL2000 on a Win2003 32 bit server - don't know the other specs.
September 6, 2011 at 6:37 am
Is the jobid indexes?
Do you have triggers?
Do you have a ton of indexes on that table?
Do you have lots of fks (or any of them NOT indexed correctly)?
Waitfor in the code? (yes I've heard of this in prod)
I could answer those if you posted the actual execution plan.
September 7, 2011 at 2:22 am
CREATE TABLE [dbo].[JobLog](
[JobID] [int] IDENTITY(1,1) NOT NULL,
[ParentJobID] [nchar](255) NOT NULL,
[StoredProcedureName] [nchar](255) NOT NULL,
[StartDatetime] [datetime] NULL,
[EndDatetime] [datetime] NULL
) ON [PRIMARY]
ID in an identity field
No indexes
No triggers
table is currently only about 80 records long (still in development so gets cleared down on a regular basis)
no FKs in or out
no contstraints
No waitfors in the code.
But thanks for the suggestions
September 7, 2011 at 5:58 am
Try putting a clustered PK on the identity colum and see if that fixes it. Heap "index" could be the source of your problems.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply