February 18, 2016 at 6:35 am
I have a stored procedure which applies data from one table to another, then deletes the table (the transactions, not the one just updated)
it goes something like this:
UPDATE table A
SET flag to processed
FROM table A
JOIN table B
PRINT @@ROWCOUNT
then
INSERT INTO A history
SELECT * FROM Table A
WHERE flag = processed
PRINT @@ROWCOUNT
DELETE table A
WHERE flag = processed
PRINT @@ROWCOUNT
DELETE B
PRINT @@ROWCOUNT
the final statement is executed before the first three!
what is going on here?
February 18, 2016 at 6:39 am
Not possible. SQL executes statements strictly sequentially when they're in the same batch (which a procedure is).
Sure another process isn't somehow deleting B? Or is the insert into B failing perhaps?
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
February 18, 2016 at 6:59 pm
Can you see how many rows are actually updated by the first statement?
_____________
Code for TallyGenerator
February 19, 2016 at 3:04 am
Seggerman-675349 (2/18/2016)
it goes something like this:
Please post the *exact* code. With the "something like this" code, I was unable to reproduce the issue.
February 19, 2016 at 4:00 am
can't - it's considered intellectual property and my client company is very sensitive about that, even though we're hardly state of the art in the area I work in
the snippet was from a stored procedure - and the delete had been in another stored procedure which called that one - I just wanted assurances that moving the delete statement inside the stored procedure would fix the problem
something was done to speed this entire process up about a week ago and a procedure running fine for the last 2 years is starting to have issues like this
February 19, 2016 at 4:54 am
Speeding up changing results implies things were running on two sessions. Within a single session, SQL executes statements strictly in sequence. So if you have code of:
SELECT <something>
INSERT <Something else>
EXEC AProcedure
DELETE <something>
the delete will not start until the execution of AProcedure has completed, and AProcedure won't start executing until the insert has completed
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
February 19, 2016 at 5:15 am
I suggest that you set up a trace/extended events session, or add a (temporary) trigger to the table to track deletes (but note that a trigger will not catch TRUNCATE TABLE statements).
Unless your code expliitly forces asynchronous execution (which by the way is quite hard to do in pure T-SQL!) the statements will execute serially, which means that there has to be some other process that removes the data.
February 19, 2016 at 6:09 am
thank you everyone (especially Ms Shaw) for what was a really basic question - and not mocking me for it
the answer was that a change was made to the system about a week ago and the file coming in was the last one created on the old system - which of course would not match because the records get deleted as soon as they are processed
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy