This week, we’ve been working with SQL DBA Kate Luxemburg, who has 20 years of DBA experience going all the way back to the early days of Ingres. Kate had a 90-minute braindump yesterday with all sorts of useful tidbits—it’s a great opportunity anytime you can learn from someone with that much experience. Here are some take-away messages.
Think of DB programming as visualizing rectangles
Application programming and DB programming are two different skills. All DB results are in the shape of a rectangle, whether it’s one scalar result or a large recordset. DB programming is strictly about managing rectangles. The smaller the rectangles you work with, the faster your query will execute.
Restrict, then project
Large SQL statements produce large rectangles, and then filter out data which doesn’t need to be there. It’s sometimes better to start with three or four small rectangles (restrict), and join them all together at the end of a query to return to the calling command (project).
The very top of your query should define the primary recordset you want, and place that into your intermittant table structure (ITS—either a temp table or table variable). Use this recordset in subsequent joins and queries to reduce the amount of filtering necessary.
When restricting, note that the WHERE clause is processed before the joins are. Make smaller rectangles by using the WHERE clause, and the joins will perform better.
Table Variables or Temp Tables?
Think about the area of your rectangles. Temp variables are great for smaller rectangles, but at some point, your memory will fill up, causing your table variables to be paged to the disk via the OS. This point is different on every system, so some testing will be involved. Using temp tables and a properly sized tempdb is more efficient than OS memory paging.
What’s the easiest way to measure the performance of two queries? Use “set statistics io onâ€, which provides a great deal of information regarding the amount of IO it takes for a query to complete. The less IO required, the better your query will perform. Follow the link for more info on this setting.
Don’t use either unless you have to
In some cases, you can replace an ITS with a select case statement, and doing so will increase performance.
