Just a quick post to point out that Red Gate has just put out an e-book of 45 Database Performance Tips for Developers. It’s free. And to sweeten the deal, they’re bundling it with a 14 day free trial licence for their SQL Prompt product – a most excellent tool, and one that I under-use.
There are tips on ORM, basic T-SQL, indexing and database design. Each tip is just a few lines, in big print, so it won’t take long to read. It will, however, give you lots of ideas of things to do to make your apps better.
My favourite, and one that I’ve used extensively in the past, is number 8:
For small sets of data that are infrequently updated such as lookup values, build a method of caching them in memory on your application server rather than constantly querying them in the database.
I remember using this idea in a previous role. We had a utility application that made frequent round trips while processing through a list of jobs. These round trips were selecting very basic reference data (Process IDs, document type codes, workflow rules, that sort of thing); the problem is that it was doing all these checks for each document individually, and that caused half a dozen database round trips for each document. By re-writing the application to cache this data (which, after all, didn’t change day-to-day), I was able to take a 2.5 hour process down to about a minute, meaning that the work queues could be prioritised much more effectively.
I suspect that the whole application could have been transferred to a single server-side scheduled task, but that idea was a step too far…
[disclosure - I'm a "Friend of Red Gate", but I've not been asked to post this]