September 3, 2008 at 8:50 am
I created an insert trigger that updates based on records in the Inserted temporary table. It works okay except that it seems to scan the entire table instead of just the inserted records. Is there a way to stop the entire table scan? The statements are just taking too long to run.
Below is a code example. I have 4 of these update statement to run in the same trigger.
update table1
set field1='some value'
where field1 is null and field2 like '%some other value%'
and field3 = (select distinct field3 from inserted)
Any advice is appreciated!
September 3, 2008 at 10:07 am
Do you always have to do all 4 updates, or are you only doing 1 and the other 3 get ignored?
September 3, 2008 at 10:10 am
I'd like to see the rest of the trigger, there's probablye a way to only do 1 update instead of four. Your query should look like this :
update T1
set field1='some value'
FROM table1 T1 inner join Inserted I On T1.field3 = I.Field3
where /*T1 or I*/.field1 is null and /*T1 or I*/.field2 like '%some other value%'
Make sure Field3 is indexed, and that should eliminate the scan.
September 3, 2008 at 10:17 am
This line:
field2 like '%some other value%'
Using a leading wildcard means an index normally will not be used.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 3, 2008 at 10:25 am
Good point, but I'm assuming that this search is done in the inserted table (which is always scanned anyways)... but he must keep this in mind.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply