Databasen - Revisited

[Peter Corlett]

On 18 Oct 2006, at 19:20, Matt Sergeant wrote:
[...]
> Sorry, but when it comes down to performance, squeezing out every  
> last drop is not a hack. Do you consider a schwarzian transform a  
> hack too?

Evidently your priorities are to squeeze as much as possible out of  
hardware that is clearly inadequate for the task. Now, while I  
appreciate tight code and a good hack, a line has to be drawn  
somewhere. I just happen to be of the opinion that the proffered  
technique crosses that line.

Anywhere that said that I had to compromise the database integrity  
because they were too cheap to buy a the right kit also falls under  
the list of companies I don't think I'd like to work for. It's not  
reducing bloat, it's sheer penny pinching.

>> Any RDBMS worth its salt will skip the index if it's clearly  
>> faster to do a
>> full table scan.
> You prefer to force the optimiser to have to examine its choices  
> than give it none?

"Examine" is probably too strong a word when it merely has to see  
that the search set is sufficiently small that O(N) is smaller than O 
(logN). It'll take a fraction of the time it took to parse the SQL in  
the first place, and that's only done once when you prepare the  
query. You do prepare queries rather than re-parse them every time,  
right?

> There's a reason databases provide extended syntaxes to ignore the  
> optimiser choices and force use of an index - optimisers aren't  
> that smart.

Nah, but sometimes they're still smarter than a DBA, just because the  
computer can exhaustively search the choices rather than rely on  
intuition. The DBA will probably wish to look at the optimiser's  
choices and nudge it if it's making bad ones, but again, it's often  
Good Enough if the schema was designed by a DBA rather than a rabid  
crack weasel.

> (and yes, this is another one of the reasons why I think ORMs are  
> bad).

I haven't really used them sufficiently to have a strong opinion on  
whether they're particularly optimal, but my limited experience of  
them suggests that they serve a definite need.

> It was a very specific example to try and get at very specific  
> knowledge (fitting a table into a page). If you can think of a  
> better way to get at that knowledge please let me know.

It's not useful knowledge - we're not using 4MB 68040-based boxes any  
more. The main performance drop-off occurs when your working set is  
larger than your RAM, rather than the CPU cache size or page size.

Even the usually-extortionate PC World will do you a gig of the stuff  
for 65 quid. How much DBA can you buy for 65 quid?