I have integration tests that require test database to function.
Since tests should generally be independent, I reset the database in the beginning of each
test.
I can not use RESTORE, since some parts of architecture (which I do not control) are caching the connections, and will fail with connection lost on next call.
Right now I am creating a snapshot, then calling DELETE + INSERT on each table to synchronize data with snapshot. However, it takes 1 second per reset which is way too much (150 tests = 150 seconds). I have a lot of tables, but they are almost empty so there is no reason for it to be so slow.
So how can I replace database with its previous version in less than 1 second without losing connections?
My next idea would be to add some kind of change tracking since each test only affects some tables, but it would make reset code even more complicated.
UPDATE: I added SET STATISTICS TIME ON and I am getting
SQL Server parse and compile time:
CPU time = 327 ms, elapsed time = 343 ms.
for my reset SP. I assume it is due to ALTER TABLE ... NOCHECK CONSTRAINT ALL calls in the beginning of the SP. I wonder if it is possible to suppress recompilation in this case.
Best Answer
Run each of your tests inside of a single transaction... at the end of each test,
ROLLBACKthe transaction.