We are developing webservices for accessing data in SQL Server. The performance requirement was not met until we started to tune our solution.

TLDR: use table in stead of view

EF

First we added the AsNoTracking() extension from EntityFramework to the DbContext virtual properties. This informs EF to just read the data and not track any changes. Since we’re only using the data read-only we have no need for change tracking. Speed improvement quick-win.

async-await

Next is the loadtest in Visual Studio. The Constant Load Pattern is set to 1. This means 1 thread doing all the requests. But we’ve created an ‘async’ webservice that ‘awaits’ the data from the database. Setting the number to 4 improved the requests/second by just a little.

Then we noticed the huge speed increase when requesting the same id for the second time. This happend when the number of requests exceeded the list of unique id’s and started over with already requested id’s. Is this caching of information in SQL Server?

Table in stead of view

Last change is in the database. Even with an indexed View the data is read slowly until it is requested the second time (and probably cached). We moved to a table with the same data as the view. This made the performance on par. (200+ page/s)

References

Performance Considerations for EF 4, 5 and 6 on msn
Tips to improve Entity Framework Performanc on dotnet-tricks