Reset all identity columns fast?

[Former Member]

In addition to deleting and inserting test data, we need to call sa_reset_identity for some of our integration tests and it takes about ~0.5 seconds for each table. We frequently run many such tests for many tables and it sums up to a big total. Is there a way to do the same thing faster? (bonus: why does it need 0.5secs?)

Update: TRUNCATE did it.

[Breck_Carter · Accepted solution]

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]

[Breck_Carter · Accepted solution]

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 🙂

[VolkerBarth · Accepted solution]

something for Volker to use to warn others off 🙂

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?

[VolkerBarth · Accepted solution]

Besides that - at least our conclusions to henginy's question are rather similar, aren't they? (Better than the opposite, methinks...)

[Former Member · Accepted solution]

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.

[VolkerBarth]

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:

Side effects

Causes a checkpoint to occur after the value has been updated

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)...

[Former Member]

Thanks. I checked the docs, but missed that!

[VolkerBarth]

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):

If the table contains a column defined as DEFAULT AUTOINCREMENT or DEFAULT GLOBAL AUTOINCREMENT, the truncation operation resets the next available value for the column.

[Former Member]

..so it will call that procedure too. I get it.

[VolkerBarth]

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...

[Former Member]

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.

[Breck_Carter]

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.