RavenDB Vs SQL - A follow up

The fight they were all waiting for

This is a following on post from RavenDB is it all it’s cracked up to be. A few have commented that to provide any meaningful interpretation of the load test they needed to know more about the SQL implementation which is fair enough. So, here we go.

The load test was executed against our SQL Implementation currently driving the search behind a leading estate agent property web site in the UK, with no performance problems. We naturally couldn't run a load test against the production environment but setup a staging environment which mirrored the production environment in terms of application (.Net 4 Web Application running on IIS 7.5), codebase, Operating System and Server Configuration.

The load test executed between SQL and RavenDB was against the same VM, Web Application and Operating System. The only difference was the data stores and the search provider. The test ultimately proved the differences in performance between our current implementation between RavenDb and SQL i.e. a real world, practical example of one search provided vs another. The load test was setup using browser mob and we ran a ramp test scaling from 0 to 1000 users. I chose a ramp test to assess where the saturation / breaking point was in the two technologies

Software / Hardware

OS	Windows Server 2008 R2 x64
Server Specifications	VM, dual core 2.40 GHz, 4 GB RAM
Web Server	IIS 7.5, .Net 4
SQL Database	SQL Server 2008 R2
RavenDB Build	RavenDB-Build-573
Front End	ASP.Net Web Form switching search providers based on query string parameters.
Load Test Configuration	Ramp test scaling up from 0 to 1000 Concurrent Users. Two scripts were created: one to execute an Attribute Search and one to execute a Proximity/Spatial Search. The distribution between the two tests was 50/50. Each script also passed in randomised search parameters for both the attribute and proximity search. Each user executed single get request with no think time between request. A single transaction was 1 get request which didn't timeout (30 seconds) or return an error.

Load Testing

Another meaningful graph is the response time between the two tests.

RavenDB - Response Time

SQL - Response Time

These two graphs illustrate the average response time between one of the scripts for both SQL and RavenDB. What this illustrates is that the RavenDB scaled well with concurrency serving request in under 12 seconds, even with 1000 concurrent users.

So your SQL implementation is poor then? 

Well from a pragmatic perspective, no. The web site works well and the client is happy that customers can use it to find and buy properties, shelling out exorbitant fees into the hands of greedy estate agents. From a technical perspective we had to query the data from a SQL Big Table, and didn't have the luxury of designing a normalised model from scratch. This I think is interesting from the perspective of real world challenges we face in creating systems and have to adapt to challenges and obstacles in our path. We did have to work on the SQL stored procedure to krank out the performance we needed and our resident SQL guru had to step in and flex his muscles. This best expressed in an email sent to me with the subject line, "This is why your SQL is slow".

If anyone is interested to check out the SQL code I've uploaded it here. The RavenDB code was really straight forward working against a Domain Model and the defined index. The only complexity was of course limitations in the API but creating native Lucene queries worked around this. (As described in blog post)

So is RavenDB better than SQL?

In this scenario, yes. However I do realise that database design varies for every project so it would be a wild and unwise accusation to blanket this statement across every SQL Implementation Installation in the world ;-) Also there are a number of optimisation tricks suggested by colleagues (following on from the post) and the readers of the post. Thanks for all the technical insight and I will endeavour to so some more tweaks, changes and more tests time, virtual machines and browser mob credits permitting.

The aim of the previous post was to create a like for like test – keeping as many variables the same - against a real world solution, whilst investigating RavenDB.

And in this case, RavenDB put SQL doooown!