The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.

asked 25 Mar '14, 07:57

henginy's gravatar image

henginy
406151827
accept rate: 0%

edited 26 Mar '14, 10:12


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]

permanent link

answered 25 Mar '14, 08:18

Breck%20Carter's gravatar image

Breck Carter
26.6k418576824
accept rate: 21%

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

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?

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

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

permanent link

answered 25 Mar '14, 08:13

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

edited 25 Mar '14, 08:19

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

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.

(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
showing 1 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×238
×60
×8

question asked: 25 Mar '14, 07:57

question was seen: 735 times

last updated: 26 Mar '14, 12:59