|
In addition to deleting and inserting test data, we need to call Update: TRUNCATE did it. |
|
I'll go for the bonus point: sa_reset_identity() has a checkpoint as a side effect. [GuessworkAlert] The reason for that? Probably because a checkpoint is required to force the change to actually appear in SYSTABCOL.max_identity, which is what you (probably) want when you call sa_reset_identity(). [DeepGuessworkAlert] I am guessing that putting the call inside an event, and using TRIGGER EVENT to fire-and-forget the event multiple times, MIGHT help... but the probability is low, about the same probability as it MIGHT crash the engine having multiple checkpoints in progress at the same time... the highest probability is that it will have NO REAL EFFECT because the engine will single-thread all the checkpoints. [/DeepGuessworkAlert] True relief probably requires a change to the engine. TRUNCATE TABLE might do the trick if the tables are empty. [/GuessworkAlert] 1
This forum needs a PREPARE TO ANSWER statement (like PREPARE TO COMMIT), or maybe a READ WITH HOLD, intent-to-answer-lock, something like that... something for Volker to use to warn other people off :)
(25 Mar '14, 08:22)
Breck Carter
Replies hidden
1
or vice versa:) Well, it's a wiki, not a RDBMS - with SQL Anywehre, writers would surely block writers:) FWIW, I would guess that several "parallel" implicit checkpoints (as issued via a bunch of parallel events) would still lead to serialized checkpoint execution, as John has told here in detail: What are all the bad things which happen when a CHECKPOINT occurs?
(25 Mar '14, 08:26)
Volker Barth
Besides that - at least our conclusions to henginy's question are rather similar, aren't they? (Better than the opposite, methinks...)
(25 Mar '14, 08:27)
Volker Barth
Thank you too, Breck:) After reading Volker's answer, I was also thinking on whether TRUNCATE would do it. I'll give it a try.
(25 Mar '14, 08:32)
henginy
|
|
I'd think it has to do with the fact that sa_reset_identity() does an automatic checkpoint, so you will have a series of checkpoints when doing a series of such reset calls... - cf. the docs:
The need for the implicit checkpoint might have to do with the fact that the system catalog stores the maximum value within the syscolumn.max_identity (or systabcol for SA 10 and above) columns, and these are only modified (like other catalog metadata) by a checkpoint, as Glenn has explained here: What causes syscolumn.max_identity to be updated? If that is still correct, then I would suspect there is no faster way - unless you might want to suggest an enhanced sa_reset_identity procedure that could reset several tables in one go (i.e. with only one checkpoint call)... Thanks. I checked the docs, but missed that!
(25 Mar '14, 08:30)
henginy
Replies hidden
1
Hm, my reasoning would not tell why a TRUNCATE TABLE (as suggested by Breck) seems to be able to reset the max identity value without a checkpoint (only with a leading and trailing commit):
(25 Mar '14, 08:32)
Volker Barth
Comment Text Removed
..so it will call that procedure too. I get it.
(25 Mar '14, 08:35)
henginy
Comment Text Removed
Comment Text Removed
I am not sure - possibly a TRUNCATE TABLE can do "magic things" we are not aware of... But you can certainly find out yourself whether this will speed up your reset-procedure...
(25 Mar '14, 08:41)
Volker Barth
1
Right, I can't jump to that conclusion. Well, I've tried further and my tests' speed is great now, although I don't know how. Even the 'slow' truncate as in Breck's book seems to do the job. I'll test some more and publish the results here.
(25 Mar '14, 08:51)
henginy
1
If the table is actually empty, I'm guessing all forms of TRUNCATE will be fast. So will DELETE, but it doesn't reset the autoincrement columns.
(26 Mar '14, 12:59)
Breck Carter
|
