September 3, 2018 at 12:32 am
Hello,
We have a lot of sql server unit tests which works like following:
1. Begin tran
2. Setup testdata
3. Run some tests
4. Rollback
5. Run next test
This works OK, but takes a bit of time, due to constant rollbacks. Is there some specific techniques one can use to improve the performance of the this kind of "workload"?
September 3, 2018 at 3:12 am
I'd change it. I don't know a good way to optimize rollbacks other than keeping the transactions as small as possible. Instead of rollbacks, can you truncate the test tables or delete the test data? Those processes can be directly optimized.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 3, 2018 at 4:02 am
Grant Fritchey - Monday, September 3, 2018 3:12 AMI'd change it. I don't know a good way to optimize rollbacks other than keeping the transactions as small as possible. Instead of rollbacks, can you truncate the test tables or delete the test data? Those processes can be directly optimized.
This only works when the existing data is not altered (Updated / Deleted) and then it is neccesary to 'tag' your testdate, which is not always practical.
For repeated testing I use the following construction :
--
-- If a transaction is still pending. Get rid of that transaction.
--
BEGIN TRAN PROBEER
ROLLBACK TRAN PROBEER
BEGIN TRAN PROBEER
select 'Some reading process in a multitude of tables.'
exec SP_Timing 'Start of the Mutations'
select 'Mutations to existing data. (Often updates and/or deletes.)'
exec SP_Timing 'Finish of the Mutations'
select 'Testcoding.'
ROLLBACK TRAN PROBEER
I often use this setup, when developing, the ROLLBACK is to make sure I can reuse the testset (or data).
I often need multiple runs to make very sure that the coding is running correctly and efficiently.
The timing routine is a homebrew routine which registers in a tempdb table a variation of information. (Time, amount of logging etc.)
Timing can be read from another connection. Sometimes I time the ROLLBACK, but this is a bit more tricky.
Sometimes I postpone the ROLLBACK to inspect the result and timing.
For me the amount of timing needed for the ROLLBACK is sometimes a problem. So the same question from me.
Suggestions are welcome.
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply