I was at a developer's conference a while back, listening to a developer holding forth, up on the podium. I must admit that my attention was wandering slightly when I was jarred to attention by the phrase "and we saved several SQL joins by doing so". Eh? I was suddenly all ears.

It seems that he was explaining how, by clever use of views and functions, they had eliminated a few 'expensive' SQL joins. In short, he was evangelizing as bad a strategy as you could imagine. Views upon views and table-valued functions gathered up a huge gob of data, constituting an entire customer object, and hurled the whole quivering mass into the application. Most of the data was entirely irrelevant to the process but it was sent anyway; customer history, previous addresses, payment details, the lot.

Leaving to one side the major crime of requesting more data than you need, there was the irritating assumption that a join would always take a significant time. "I'm on the train; I'll be home in twenty joins" I imagined him telling his family, as he started his commute home. He'd just assumed that fewer joins meant faster queries, and if it still seemed slow, well, what more can one do? That's SQL databases for you.

If you get into the habit of checking your timings, you are likely to know that a query join is generally either fine or awful, with only very rare half-measures. If queries with more joins are more likely to be slower, it is just that there is an increased chance of kicking up a bad'un, such as a join that highlights a problem with your indexing strategy. Once you've fixed the bad'uns, the problems generally vanish. Of course, if you like to join tables to unindexed views or TVFs, then you should expect all joins to be as interminable as Franz Shubert Lieder songs.

There are many techniques for timing database routines, and they all have their place. However, some are more important than others. For the developer, I reckon that the routine end-to-end timings for a process, such as instantiating a customer object and populating it with the required data, tells you the basics. It answers the question 'Do you have a problem?' It can't tell you the cause. It could be as broad a problem as a slow network; it could be an immaculately-contrived and beautifully indexed procedure that is doing something very foolish, such as stocking the enormous customer object; it could be as trivial as a missing hint or option in a routine. Who knows? But until you're clear and precise about the symptoms, you can't expect to find a cure.

Phil Factor.