Three not so unusual pitfalls when it comes to db access

This is a short post about three recent problems we had, which all are related to data access in common scenarios.

1. Don’t pop the popcorn’s more than once. I threw in some db functionality in a project and handed it over to another programmer. Most calls was inexpensive and fine, but one web page was extremely slow. It turned out I had adviced functions that returned lazy loaded data, and when my collegue was doing some advanced reporting with LINQ queries, the db calls was made for each and every internal loop iteration. Known as the “Select N+1 – problem”. We solved it simply by changing lazy loading to fetching the data we needed at once, which meant our LINQ was all the time doing it’s work to in-memory data. A more efficient option would probably be to plan our work carefully and write very efficient advanced (and long) SQL queries. However that would take us more time to create, be harder debug and probably harder to maintain. Useful tools : PetaPoco, a fast and tiny O/RM with easy to use functions both for lazy loaded (Query) and prefetched data (Fetch).

2. Don’t be trigger happy with your Sql CE connections. I did not understand why one particular page took 1 second to load. I tried out a profiler to check out where the bottlenecks were and found that the page was opening and closing the db ten times and each open took 1/10th of a second. Apparently a known fact for CE database’s. The solution was simply to keep the connections open. Useful tools : jetBrains dotTrace profiler.

3. Look out for Ajax cache mishaps. We had a problem with a jQuery control that wasn’t reloading data after an update. It worked perfectly in Firefox and Chrome, but in IE8/9 the data wasn’t updated until we closed the browser and reopened the page. It took us some time to realize the IE8/9 ajax got Http 304’s (Not Modified) and for that reason wasn’t changed. (Firefox and Chrome got Http 200’s – I don’t know why they differed, someone?). The simple fix was to add a cache:false to our Ajax call. Useful tools : Firebug Net and IE Developer Network capture.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your 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