Entity Framework Query Caching and Automation

There are two ways in which automation can help you maximize Entity Framework’s performance when backing an ASP.NET website.

If you’ve modeled complex data with EF, particularly with extensive inheritance, you know that the first time a query is called there’s a delayed response, which can be up to several minutes. The reason for the initial delay is the time taken to generate and cache the SQL code that matches the query’s LINQ IQuerable code. Future requests for that query draw on the already cached SQL, allowing for a speedy response.

Letting your ASP.NET users experience a several minute delay for a single query, maybe five or more minutes of delay on a page that has a couple of queries, is definitely unacceptable, and maybe even a little cruel. Fortunately, it’s also unnecessary.

A much better approach to is to automate the generation of the cached SQL.

To do this I write a small app that loads web pages exercising any slow-caching EF queries. Some of these pages are simply the same ones that users see. Others are manufactured specifically to pre-cache queries involved in AJAX requests., but is of no interest to users. The app is run periodically as a scheduled task or job, virtually insuring that each slow-caching query is stored in cache before a user comes in contact with it.

A second way automation can used to improve EF performance is to auto generate code that time-logs each statement.

The biggest hurdle to developing cashing is to find the lines of code there are time consuming.

Finding EF bottlenecks can be troublesome, buried in pages of code. Rarely are the locations of slow-caching queries intuitively obvious. The only practical approach I have found is to track broad-swaths of code, maximizing the likelihood of ensnaring the slow-caching in the act. I written a script that allows me to quickly add logging in each line of classes I suspect are involved in the bottleneck with great success.

Show your support

Clapping shows how much you appreciated Perry Tribolet’s story.