Triggers and Indexes
-
Hi,
I have a query about how a trigger uses indexes on a table.
Basically I have a single trigger on a table which contains about a 2.1M rows. Inserts into this table are pretty slow, this can take up to 3 seconds!! There is a primary key on this table.
When I remove the trigger the inserts are done in an instant. I therefore replaced the trigger,commented out each line of code in the trigger and re-inserted a record into this table each time un-commenting a line of the trigger until I found a problem.
Using this process of elimination I identified this part of my trigger as the one that produced 6000-7000 reads in SQL Profiler each time an Insert was executed.
DECLARE @NoStages int
SELECT @NoStages = MAX(Stage) FROM JB_JobStages WHERE JobID = @JobID
The thing that concerns me is that if I execute this query in the Query Analyzer it only takes 30-40 reads when viewd through the SQL Profiler.
Note that there is an index on the JB_JobStages table that the query hits perfectly when executed in the Query Analyzer.
My question is, is there a possibility that when the query is executed in the trigger it is not hitting the index? If so how can I check and what can I do about it?
Thanks for your thoughts.
Kevin
Windows 2008 Server | SQL Server 2008
-
That code looks wrong to me.. Can you post the whole trigger code?
-
Yes no problem. As I mentioned everything up to the part called "Find out how many stages this job has" costs nothing and is comment in. Everything after the query in question is commented out during my testing.
CREATE TRIGGER [SetLatestStatus] ON dbo.MessagesIN
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @AType nvarchar(5)
SELECT @AType = AType FROM INSERTED
IF @AType <>'P'
BEGIN
--Declare all variables
DECLARE @StageID nvarchar(50), @RefID nvarchar(50),@CompanyID int,@LoadDate datetime, @LoadID nvarchar(50),@MessageDate datetime,@MessageTime datetime
DECLARE @Status nvarchar(30),@StatusCode int,@StatusExtra nvarchar(4),@POD bit, @PODRef nvarchar(50)
DECLARE @PODRef2 nvarchar(50),@PODBy nvarchar(50),@PODDate datetime,@Conformed nvarchar(1),@Conformance nvarchar(20)
DECLARE @ConfReason nvarchar(30),@ConfCustRef nvarchar(20),@SignatureData nvarchar(4000),@Vehicle nvarchar(50)
DECLARE @Notes1 nvarchar(30), @Notes2 nvarchar(30), @Notes3 nvarchar(30), @JobID nvarchar(50),@ActualDateTime datetime
DECLARE @CustomerInformed varchar(1), @DateInformed datetime, @TimeInformed datetime, @CustomerName varchar(20), @InformedBy varchar(8), @ConfOurRef varchar(20)
--Assign variables from the newly inserted record
SELECT @StageID=ID, @RefID=RefID, @CompanyID=CompanyID, @LoadDate=LoadDate, @LoadID=LoadID, @MessageDate=MessageDate,
@MessageTime=MessageTime, @Status=Status, @StatusCode = StatusCode, @StatusExtra=StatusExtra,
@POD=POD, @PODRef=PODRef, @PODRef2=PODRef2, @PODBy=PODBy, @PODDate=PODDate, @Conformed=Conformed,
@Conformance=Conformance, @ConfReason=ConfReason, @ConfCustRef=ConfCustRef, @Notes1=Notes1, @Notes2=Notes2,
@Notes3=Notes3,@CustomerInformed=CustomerInformed, @DateInformed=DateInformed, @TimeInformed=TimeInformed, @CustomerName=CustomerName,
@InformedBy=InformedBy,@ConfOurRef=ConfOurRef, @SignatureData=SignatureData,@Vehicle=Vehicle, @JobID=JobID, @AType=AType, @ActualDateTime=ActualDateTime
FROM INSERTED
DECLARE @NoStages int, @CurrStage int, @PickUpExist int
--Find out how many stages this job has
SELECT @NoStages = MAX(Stage) FROM JB_JobStages WHERE JobID = @RefID
--Find out what stage the newly inserted status is for
SELECT @CurrStage = Stage FROM JB_JobStages WHERE StageID = @StageID AND JobID = @RefID
--Find out if a Picked Up status already exists in the JB_LatestStatus table
SELECT @PickUpExist = StatusCode FROM JB_LatestStatus WHERE RefID = @RefID
--Proceed or not?
IF (@CurrStage IS NULL AND @StatusCode = 0) OR ((@PickUpExist IS NULL OR @PickUpExist = 0) AND @StatusCode=1) OR ((@CurrStage = @NoStages) AND @StatusCode >=2)
BEGIN
IF EXISTS (SELECT RefID FROM JB_LatestStatus WHERE RefID = @RefID)
BEGIN
UPDATE JB_LatestStatus
SET CompanyID=@CompanyID, LoadDate=@LoadDate, LoadID=@LoadID, MessageDate=@MessageDate, MessageTime=@MessageTime,
Status=@Status, StatusCode=@StatusCode, StatusExtra=@StatusExtra, <A href="mailto
OD=@POD">POD=@POD, <A href="mailtoODRef=@PODRef">PODRef=@PODRef,<A href="mailto
ODRef2=@PODRef2">PODRef2=@PODRef2, <A href="mailtoODBy=@PODBy">PODBy=@PODBy, <A href="mailtoODDate=@PODDate">PODDate=@PODDate, Conformed=@Conformed, Conformance=@Conformance,ConfReason=@ConfReason, ConfCustRef=@ConfCustRef, Notes1=@Notes1, Notes2=@Notes2, Notes3=@Notes3,
CustomerInformed=@CustomerInformed, <A href="mailto
ateInformed=@DateInformed">DateInformed=@DateInformed, TimeInformed=@TimeInformed, CustomerName=@CustomerName,InformedBy=@InformedBy, ConfOurRef=@ConfOurRef, SignatureData=@SignatureData, Vehicle=@Vehicle, AType=@AType, ActualDateTime=@ActualDateTime
WHERE RefID = @RefID
END
--If it doesn't exist then insert a new record
ELSE
BEGIN
INSERT INTO JB_LatestStatus
(ID,RefID,CompanyID,LoadDate,LoadID,MessageDate,MessageTime,Status,StatusCode,StatusExtra,POD,PODRef,PODRef2,
PODBy,PODDate,Conformed,Conformance,ConfReason,ConfCustRef,Notes1,Notes2,Notes3,CustomerInformed, DateInformed, TimeInformed,
CustomerName, InformedBy, ConfOurRef,SignatureData,Vehicle,JobID,AType,ActualDateTime)
VALUES
(@StageID,@RefID,@CompanyID,@LoadDate,@LoadID,@MessageDate,@MessageTime,@Status,@StatusCode,@StatusExtra,@POD,
@PODRef,@PODRef2,@PODBy,@PODDate,@Conformed,@Conformance,@ConfReason,@ConfCustRef,@Notes1,@Notes2,@Notes3,
@CustomerInformed, @DateInformed, @TimeInformed, @CustomerName, @InformedBy, @ConfOurRef, @SignatureData,@Vehicle,@JobID,
@AType,@ActualDateTime)
END
END
END
SET NOCOUNT OFF
Windows 2008 Server | SQL Server 2008
-
Can you briefly explain what you're trying to achieve in this trigger.
Also do you expect this to work if you insert multiple rows at the same time??
-
The table is a list of status's relating to a job. Note that a job can be split into several stages.
When a record is inserted into this table I need to update or insert into another table which holds this particular jobs 'latest status' (I do an insert of an update depending on if a record exists in the other table) . The latest status being the record that was just inserted.
The trigger does a series of business logic checks to see which stage of a job this status change belongs to and what the current status of the job is and then decides whether to do that update or insert on the latest status table or to just leave it. The jobs overall status won't progress in some circumstances depending on which stage the status change belongs to.
Hope that makes some sense.
Records get inserted one at a time as the status of a job changes.
Windows 2008 Server | SQL Server 2008
-
Can you see the execution plan of the trigger and find what part of it is causing the slowing down?
Also I would strongly suggest that you encapsulate the whole trigger in an if (Select count(*) from Inserted) = 1
begin...
else
rollback tran
too make sure that this doesn't cause bad data on a multi row insert
-
I just discovered that I could do that! I took my trigger and put it into the QA as if it was a series of queries I was trying to execute then looked at the execution plan.
I was able to see that the SELECTS were hitting the indexes, althouh the select to get the current stage is hitting the index for the SELECT to get hte no of stages.
Not sure where to go from here though. It's not causing me a major major problem, it just concerned me.
Thanks for the tip about encapsulating the code. I'll investige! Oh, and thanks for your efforts too.
Windows 2008 Server | SQL Server 2008
-
check if you can rewrite the query to use the index in the best way possible. if you can't then see if you can't change the indexing strategy.
-
Well this trigger is just plain wrong if multiple rows can be affected in one shot!
The Trigger can probably be written in just two statements 1 update and one insert! and no variables should be needed either.
Are you sure that there is only one row insertion at a time?
* Noel -
Tx noeld.. didn't have the heart to try to rewrite that one...
-
I could guess a bit but to really do the rewrite I would need the DDL for the tables
The update will the same join than the insert exept that it will check for presence and the insert will check for absence.
I am sure you knew that but the info goes for the poster
* Noel -
Yup... getting tired here. Too many questions answered and not enough good sleep
. -
What about things like set options that are active when you are running in QA vs the environment modifying the table causing the trigger to execute?
Also, perhaps the use of a local variable is causing the optimiser to not use the index? Is the variable of the same type (I haven't read through all of your code) as your field? (Learnt the importance of that from Remi in a post a couple of weeks back!)
Other than that, as suggested, try to rewrite with fewer statements - perhaps using some case statements, etc
Cheers
-
Yup... got bit by that one a few times before I realized how the optimizer works...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply