High Performance
Database Consulting
- Build, repair and optimize complex databases
- Work directly with a top-tier database expert
Performance Tuning
Performance tuning is all about speed. How fast can you process your data and get the results you need? For me, this is the most fun part of my work. Using my knowledge and experience to create a 10-fold or even 100-fold improvement in response times. There aren’t many arenas where that is even possible, but in the database world, it truly is.
There are many aspects to improving the performance of a database system. Generally, the first one has to do with hardware. If your database is hosted on a server with insufficient resources, no amount of SQL tuning is going to help. A common problem is lack of RAM. The database engine itself takes a lot of memory, and then the more data that can be cached in memory the better. Hosting in the cloud, of course, makes it much easier to upscale your hardware as needed, although it can certainly be done in-house as well.
Once you’re sure there is memory and disk space to spare, it’s time to look for the low-hanging fruit. Very often there are just one or two queries that are taking the most time by far, and just fixing these will make a world of difference. You can use logging and tracing techniques to find these problem queries, and then examine their execution plans if you need more insight.
Database architecture also comes into play, and it’s not just a matter of ensuring TNF (Third Normal Form). Sometimes big tables need to be broken apart, sometimes old data needs to be archived, sometimes wide tables need to be restructured as thin tables, and sometimes, you have to change to a faster database.
And then there is the SQL itself. Cursor-based (row-by-row) processing must usually be avoided at all costs, and everything written in set logic. Sometimes this requires clever solutions. The use of multi-step logic with temporary tables or Common Table Expressions often produces fantastic improvements in response time. But there are many other details of SQL performance tuning that one only learns from long experience and trial-and-error over the course of many projects.
If you’re having performance issues, or just want to make sure that your performance is truly optimized, please get in touch. I’d love to make your database run faster!
My database is slow to respond. What would you do to fix it?
1. Check the hardware. Make sure there’s enough RAM and disk space.
2. Look for that one query that’s slow and focus on that.
3. Add indexes on join and search fields (where appropriate).
4. Make sure there are no table scans happening on large tables.
5. Rewrite any cursors using set logic.
6. Break down over-large queries into multiple steps using CTEs.
7. Restructure tables if needed.
8. Fine tune the SQL.
Work Samples
Set Logic and Common Table Expressions
I recently had to compare each one of 2.4 million homes against its 200 closest neighbors. My original SQL, designed to analyze one home at a time, was going to take 3 weeks, so I found a way to recode it using set logic and nested CTEs, and voila! The whole dataset ran in one day. This work was done for TaxTrim.com, which I own.
A Combination of Techniques
A snippet of code where I used 3 techniques together: temp tables, breaking the data into manageable chunks, and Dynamic SQL. This work was done for White Mountains Re, a $7 billion international reinsurance firm.