SqlJuxt – Speeding up the tests

SqlJuxt is coming along nicely. I have a great test pack in the project that tests through the top level API. I have spoken before about the value of this (gives you freedom to change your implementation etc) so I won’t harp on about it again. The problem is that the tests take 2 minutes 40 seconds to run, clearly too long. Especially when you consider that number is going to keep on growing as I add more tests.

The first step to debugging why the tests were taking so long was to turn on timings inside the Resharper test runner. This gives you a break down of how long each test takes. From that dialog I could see that all of the tests that created 2 databases for comparison were taking around 6 seconds. I then put timing around each line in one of these tests and found that the whole test ran in around 0.3 seconds and the test clean up took the other 5.7 seconds. This lead me to realise that it was the dropping of the database that was causing the slowdown.

To prove this theory I commented out the drop database code (which if you remember is in the dispose method in the DisposableDatabase type). I ran the whole test pack again and it ran in 12 seconds. Wowzer! Clearly this is not a solution as each test run will leave behind lots of databases.

I noticed that after the tests have finished if you drop a database then it happens pretty much instantaneously, but it takes ages when it happens as the last step of the test. This lead me to realise that something in my test was hanging on to the database connection causing it to have to rip the connection away. I examined my test code and I definitely close down the SqlConnection so this left me stumped for a little while. Then I got on to reading about connection pooling. Then it twigged that ADO .Net turns on connection pooling by default. What this means is that when you close a sql connection the connection doesn’t get completely destroyed. Instead it gets put into a sleeping state. This is to speed up the process of getting a connection next time you want one. In nearly all applications this is what you want as you are going to be continually connecting to your database to make connections and you do not want to keep on going through the overhead of the handshake to set up a connection. However in my scenario this is not what I want. I am making a database connection, running in a script to setup a schema, comparing that schema and then dropping it again and at that point I no longer need the database. The solution to my issue was to simply turn off connection pooling. This means that when you close the connection it is completely gone so when you drop the database it drops pretty much instantly as it doesn’t have to rip away any existing connections. To turn off connection pooling you simply add “pooling=false;” to the connection string. I did that and ran the tests and all of the tests now run in 12 seconds. Quite an improvement, its like I found the turbo button!!

I just want to reiterate that I am not advocating turning off connection pooling for everyone. It is defaulted to on for a reason (its probably what you want), its just in my specialised scenario it was causing a massive performance overhead.

It is quite a win having found that as now I can run my whole integration test pack in 12 seconds it makes it much easier to refactor.

Check out the full source code at SqlJuxt GitHub repository.