Performance tuning EF, ASP.NET and loadtest

Web-Services-IconWe 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

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s