Fully Independent

There was a question on the JoS forum about the use and viability of SQL Hints. SQL hints are pretty much what the name implies. They are guides inserted by the developer that provide additional direction to the database on how to execute a particular SQL query. One of the big fallacies of SQL that is pushed across to students is that SQL code is portable between DB, so that what you write for one system can be easily ported to another. Whilst theoretically true in the white glove teachings of academia, in practice the reality is far different.

In the real world, each DBMS has its own quirks, limitations, features and best practices. The situation is analogous to what occurs with browser support of web technologies, only on a scale a million times removed but I’ll talk about this another time.

The other fallacy closely connected to this is that SQL allows us to concentrate on the high level logic and not worry about how a particular DBMS is operating under the hood. A quick chat to any experienced DB practitioner will quickly divest the innocent inquisitor of their naivety.

This brings us on to SQL hints. The general consensus amongst the respondents to the JoS question is that, though hints should not be used at all in a production environment, the DB does at times seem to make some whacky decisions.

Let’s think about what is going on and why. Firstly, SQL hints are similar to training whips. They say to the DBMS “You better do exactly as I say!”. And so the DB does, and for the early lifetime of the application, all is well. However, later on, the situation has changed. The amount of data to search has changed and the DB is thinking, with a bit of independence, “Hmmm, maybe this isn’t the best way as it’s going to take more time”. However, it realises that it’s been given an order and like a slave to an inflexible master, it does as it was told.

The problem with SQL hints is that they operate independently of local knowledge. As long as everything is as it was when the hint was added, things are rosy but as soon as any thing changes, they can force the DB into working along an inefficient execution plan.

Most DB in production operate using some sort of analysis based on the data set and tools available to dictate the course of action. Unfortunately, they can’t analyse the whole data set at execution time (as if it has done that, then it might as well just return the results) and so they analyse “statistics” data that provide a reflection of the full dataset.

This is usually the cause of the problems which lead developers to use hints in the first place. If the statistics are stale, and no longer reflect what is actually in the system, then any analysis based on these statistics will be flawed.

However, with that said, there are often situations in which the developer can be fairly confident that they can beat the DB because they have knowledge of the requirements behind the query.

For example, in Oracle, if you want to get rows N to M of a particular result set, you would generally do something like

1. Select * from (
2. select a.*, ROWNUM rnum
3. From ([result set]) a
4. Where rownum <= :M
5. )
6. And rnum >= :N

In this scenario, the following query would perhaps be better:

1. Select * from (
2. select /*+ FIRST_ROWS */ a.*, ROWNUM rnum
3. From ([result set]) a
4. Where rownum <= :M
5. ) And rnum >= :N

The reason we can be assured that this is better is that the situation in which something like this would likely be deployed is for paginated reports. We are want the information for the pages as early as possible so that we can pass them to the user as soon as possible.

However, my thoughts are that it is better to use hints sparingly. Often, it’ll be better to re-examine what and why you are doing a particular piece of logic before trying to get the DB to run that logic as you think is suitable.

Eric’s recent post regarding the savings in moving to a standards driven design contains a great paragraph:

What they will see, though, is a faster site. Okay, actually, that’s what their users will see, but that’s the entire point. Suppose I said I could sell you a product that would make your Web site twice as fast as it is today. How much would that be worth? Maybe not much for a fandom site, but even for a small business trying to sell its products online and distinguish itself from competitors, a 2x speed booster would probably be worth buying.

It’s worth stating this again. Whilst we can point to improved flexibility, maintainability and development costs, these are really developer benefits. When presented with a solution, the client generally doesn’t care what was used or how the solution was achieved as long as the product met their needs. When we go out to buy a fridge, we generally aren’t interested in the mechanics of the system, regardless of how architecturally clean or well designed it is. However, we do care about the fridge having lower running costs, operating frost-free and fitting in our kitchen.

Similarly, the client can instantly grasp the significance of some of the other benefits of semantic HTML/XHTML:

• The site will be X% faster in loading
  Eric said it perfectly. Once the client hears the words “we can make your page load twice as fast”, you’ll be hearing a whoosh as they reach for their cheque book. Abstracting out presentational information into CSS such as font and colour information means that the page size will be reduced as you remove the duplication of information. What’s more, the css file will cached by the client and reused for each page load.
• The redesign will save us $X a year
  When Doug presented a redesign of Microsoft, he found out that it would save 329 terabytes of bandwidth a year. Whilst obviously that was a special case, it does show how cleaner markup can provide actual noticible bandwidth savings which translate into monetry savings.
• More people will be able to access the site
  Semantic HTML/XHTML pages are generally more accessible. More accessible to vision impaired readers, more accessible to people with non Wintel hardware which includes not only platforms such as Macs and Linux Systems, but also smartphones and PDAs. What’s make’s this even important is that many of the people using such platforms are also likely to be “early adopters” and “trend-setters”. They have disposable income, are willing to try out new products and are vocal members of the community. Even if that is taken out of the equation, the basic rule is that the more people that are able to visit a usable version of your site, the more prospective customers with positive impressions.
• The site will be easier to maintain and turn around
  OK, so I guess what I said earlier wasn’t true; maintainability is important but not in the way it is usually presented. The client generally doesn’t care about the ease of development. However, they do care when you say that we can turn around new changes and add features/redesign sections faster. For the customer, those are the gains that are meaningful.

Whenever someone pays for a service, whether it is a fridge or a website, they are looking for a product which satisfies some need or deficit; that is they are seeking to gain an increase in utility. The important thing to realise is that the needs of the customer is different from that of the developer. It is only by correctly identifying what the client wants that you can begin to provide a solution.