How can your service be cost-effective, when we know our applications so much better than you do?

Specialization works, and practice pays off. For the first few years of my work in performance and tuning, I assumed there were major classes of problems where I could offer only peripheral advice, but I have learned that the functionality of an application (which I will never understand as well as the application developer) and the performance of that application (where I have the advantage of much more focused, specialized experience) are surprisingly decoupled. For example, I read any given SQL query as a clear spec for what rows of which tables that application requires at that point in the code, assuming we do nothing other than tune the single query. I do not need to know why the application needs those rows, or even what those rows represent, from the business perspective, to rapidly solve the SQL tuning problem with safe SQL transformations, hints, or index changes that are guaranteed to get the same rows with alternate paths to the data (execution plans). These alternative paths to the data usually deliver speed improvements from a factor of 2 to a factor of 1000s. When poor performance comes (as it does surprisingly often) from a few slow queries, these simple, safe query transformations offer extremely cost-effective improvements, because you can fold them into your application code with a few tiny changes that are guaranteed not to harm performance or functionality elsewhere in the application.

Most developers practice SQL tuning on the queries in their own code, while some help a few colleagues tune their SQL, as well. I was the buck-stops-here end of the line for the most complex SQL tuning problems for hundreds of developers, for years, for complex, diverse, real-world business applications in Oracle Applications Division and at TenFold Corporation, so I doubt that anyone has had a richer set of real-world problems to practice on. In the course of solving these problems, I built and patented (USP#5761654) a powerful, systematic approach to the problem that handles (with practice) all routine SQL tuning problems in minutes. I also discovered dozens of special-case problems that occasionally pop up in applications SQL. Some of these required days or even weeks to solve the first time I saw them, but when I see them again, I resolve them orders of magnitude faster, because I recognize the problem and already know the solution template. At this point in my experience, I almost never find a class of problem that I have not seen before, though when I do, I usually find the solution template quickly, because I borrow from previous experience with related classes of problems.

What’s the key to performance and tuning?

Solving the right problems. Any given real-world system has hundreds of imperfections that (theoretically) affect performance. Almost all of these, however, affect performance to a tiny degree, well under a percent, often under a hundredth of a percent, so they are not worth the effort (and risk, occasionally) to fix in a production system. The real key to improving performance is to find the few problems that matter. Sometimes, these are obvious, and you already know what the problems are, but often, finding the right problems to fix involves systematic data collection and analysis at all layers of the system. Performance consulting services often focus on simply identifying theoretical imperfections, with no effort (or even knowledge how) to measure which of these imperfections matter. The result is typically weeks of futile, often expensive, often high-risk changes to the system before they stumble (with luck) on the few fixes that make a difference.

The SingingSQL emphasis appears to be on database tuning, especially SQL tuning. Is that all you’re good for?

I emphasize the database because that’s where the key problems usually lie. I didn’t even get my start there, but, through experience, I found that solving database performance problems, especially SQL tuning, is usually much more cost-effective than dealing with performance imperfections outside of the database. However, for thirteen years I have been responsible equally for problems inside and outside of the database, and I have extensive experience diagnosing and resolving (usually solo, occasionally in collaboration) problems across the whole spectrum, wherever they happened to arise in those thirteen years, including, besides vast SQL tuning on Oracle™, Sybase™, DB2™, and Microsoft SQLServer™, including queries with multiple, complex subqueries and views, and up to 115-way joins:

• Bottlenecks on the database writer, from an operating-system glitch,
  
• Complex bottlenecks in RDBMS latches,
  
• Locking bottlenecks for both inserts and updates on both Oracle™ and DB2™,
  
• Slow inserts in Sybase™,
  
• Slow commits in DB2™,
  
• Hidden cache insufficiency in DB2™,
  
• Extremely slow parses for many-way outer joins in DB2™,
  
• Slow parses for many-way star joins,
  
• Well-tuned queries that we could simply eliminate through application modifications,
  
• Well-tuned queries that returned many times more rows than the application really needed, that we could replace with much faster queries returning fewer rows,
  
• Slow client-side and middle-layer software, in many forms,
  
• CPU-bus bottlenecks,
  
• Delays in the network software layer between the RDBMS and the client application, in both Oracle™ and Microsoft SQLServer™,
  
• Delays in the network software layer between the application server and the WebServer,
  
• Latency delays in the physical network,
  
• Bandwidth delays in the physical network,
  
• Delays from a broken network interface,
  
• Flawed swapping algorithms on two different operating systems that effectively wasted over 30% of the system memory,
  
• Imbalanced physical IO.
  



Contact Home