November 8, 2011 at 1:29 am
Lets Say I have a table with 500K of rows
I have an Update a Query that update 30k of the rows
I have a trigger that monitor changes in the fileds by making a join
between Inserted and deleted
Insert Arc (key,oldv,newv,ChangeDate)
Select D.Key , D.sex , I.sex ,GetDate())
From
Deleted D
Inner Join
Inserted I
On I.Key = D.Key
where
D.Sex <> I.Sex
Deleted And Inserted are some kind of a picture of the data,
the Question Is : Does SQL Server Uses The Indexes on The Table to compile the query (obve) in the trigger.
November 8, 2011 at 2:12 am
Hi Gil, Inserted and Deleted tables are maintained by the system. You can not modify the data there or to create index on these two tables.
Regards,
Iulian
November 8, 2011 at 2:18 am
Thanks for the answer but , this information was almost obvious to me
My real Concern is performance
Do I Need to create indexes on the base table in order to improve tirgger performance
November 8, 2011 at 2:18 am
No. The indexes on the base table do not reflect onto the deleted and inserted pseudo tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 2:22 am
So what can be done to boost trigger performance on a large scale update
Does Instead of trigger with cte or temporary table is beter and in the use an update to the base table
November 8, 2011 at 2:26 am
Gil_Adi (11/8/2011)
So what can be done to boost trigger performance on a large scale update
Depends on what's causing the performance problem
Does Instead of trigger with cte or temporary table is beter and in the use an update to the base table
No, probably no better, possibly even worse depending what you're doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 2:35 am
What I had In Mind Is to Create Temporary Tables for deleted and inserted and than create an Index on them for the fields Key and sex ,then Log the changes as in the first query and in the end Update the base table from the Instead of trigger
Will it be faster
November 8, 2011 at 2:42 am
Unlikely, the overhead of creating and indexing the temp table will likely outweigh any improvements, unless you're using the deleted and inserted many, many times.
Test it and see.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 2:56 am
Gil_Adi (11/8/2011)
What I had In Mind Is to Create Temporary Tables for deleted and inserted and than create an Index on them for the fields Key and sex ,then Log the changes as in the first query and in the end Update the base table from the Instead of trigger
You're using an INSTEAD OF trigger? Is that because the updates are being performed against a view? Or are you doing some other processing you haven't mentioned? Otherwise, wouldn't it be better to use an AFTER trigger?
On the more general point, I wouldn't expect 30,000 rows in inserted and deleted to cause a problem unless the optimizer was choosing a nested loops join for some reason. Normally, with this sort of query, the optimizer will choose to hash join inserted and deleted. Have you captured an 'actual' execution plan for the trigger? If so, please share it. If not, perhaps that would be a good idea...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 3:05 am
I still want to know if there is a performance problem or if this is a theoretical question. If the latter, go test, see if the performance is acceptable and, only if it is not, worry about stuff like this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 3:05 am
Thanks for every body
1. Right now it is a theoretical question on wat is the best practice
2. I sugested the insted of trigger just to be able to use temporary tables but as I thought it over
It can be done inside the trigger it self
November 8, 2011 at 3:07 am
Gil_Adi (11/8/2011)
Thanks for every body1. Right now it is a theoretical question on wat is the best practice
The best practice is to write code as simply as possible, without unnecessary complexities and to do performance tests on that code. If the performance is not acceptable, then take any necessary steps.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2011 at 5:44 am
the Question Is : Does SQL Server Uses The Indexes on The Table to compile the query (obve) in the trigger.
No, it does not. Query Compilation has nothing to do with index usage in trigger (or stored procedures as well). Per my understanding, Compilation just verifies syntaxical errors & object dependencies (permissions & availability etc.)
Optimizer decides to use indexes based on their availability & usefulness.
November 8, 2011 at 5:47 am
Does Instead of trigger with cte or temporary table is beter and in the use an update to the base table
I don't think so. It will add overhead.
November 8, 2011 at 5:50 am
Dev @ +91 973 913 6683 (11/8/2011)
Per my understanding, Compilation just verifies syntaxical errors & object dependencies (permissions & availability etc.)
Syntax and object existence is done by the parser and algebriser. That's not a compile.
Optimizer decides to use indexes based on their availability & usefulness.
Generating a plan is what's referred to as 'compilation', so compilation is the point at which indexes are considered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply