August 23, 2016 at 6:34 am
i have an data update script, where i create a temp table to populate with data of a table based on some joins and logic i want to update.
I then join on that table to the original table based on its keys. (201110) records the base table has a trigger that writes to an audit table on inserts/updates to that table.
I am seeing something strange I did a count of the table and see 201110 records were updated, but when i do a count of records in my audit table for those records
(select count(*) form audit where table name = x) i see 1000300 (rerun) 1000358 (rerun 1000390).... so the audit records is catching up.
my question is if i already have 201110 records in the base table (x) updated why is the audit count "Catching up" i thought that on an update the trigger would write to the Audit. 1 to 1 but the Audit is catching up?
This update is within a transaction and try catch does that have anything to do with it?
August 23, 2016 at 7:23 am
Lee Hopkins (8/23/2016)
i have an data update script, where i create a temp table to populate with data of a table based on some joins and logic i want to update.I then join on that table to the original table based on its keys. (201110) records the base table has a trigger that writes to an audit table on inserts/updates to that table.
I am seeing something strange I did a count of the table and see 201110 records were updated, but when i do a count of records in my audit table for those records
(select count(*) form audit where table name = x) i see 1000300 (rerun) 1000358 (rerun 1000390).... so the audit records is catching up.
my question is if i already have 201110 records in the base table (x) updated why is the audit count "Catching up" i thought that on an update the trigger would write to the Audit. 1 to 1 but the Audit is catching up?
This update is within a transaction and try catch does that have anything to do with it?
Hi and welcome to the forums!!! You are going to need to share a lot more details here. We have no idea what your tables or triggers are like. And from what you posted I am not at all understanding what the issue is here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2016 at 7:38 am
Ok will do.
I have a table TagId with keys of Location and IDval
i have an audit table with keys tablename, Location, IDVAL
the table TAGID has a trigger that writes the Table Keyvalues to the audit table on a UPDATE or INSERT.
The tagID table has million plus records
I have a query that joins tagid and a number of other tables to get a list of TAGID records that need to be updated
and populates table (below)
declare @tagid table (location char(4) not null, IDVAL decimal(14, 0) not null;
I then do a update to TAGID x joined on to @tagid x1 where x.location = x1.location and x.IDVAL = x1.IDVAL
there are 200000 +- records in @tagid that will be updated.
This is all in a begin tran, Try catch wrapper.
so this is taking a very long time to run, to see what was happening wrote a query
Select count(*) from TAGID where dateupdated = getdate()
I am seeing the 200000 records,
but when i query the Audit table Select count(*) from audit where tablename = TAGID i am getting an increased count 5, 10, 40,1000----- until the audit count matches the tagid count (200000)
My question is i thought that it would be a 1 to 1 since the trigger is on the update/insert.
August 23, 2016 at 7:50 am
Can you share the ddl for the tables and the trigger as well as your update statement?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2016 at 8:55 am
It's only going to be one to one if the initial insert is inserting a single row at a time. You should then see it that way. However, if the initial insert is doing a set of operations, then the trigger will fire to load that set into tempdb as part of it's process and from there into your secondary table. If it's really slow to do the first operation, it's going to be really slow to do the second, possibly even slower.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 24, 2016 at 4:09 pm
Lee Hopkins (8/23/2016)
i query the Audit table Select count(*) from audit where tablename = TAGID
Let's make sure:
any chance you have (NOLOCK) on this query?
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply