At one site, I managed to verify that one of these two queries ran 10 to
100 times faster than the other:
select * from customers
where country = 'US' and
state = 'TX' and
City = 'Dallas'
select * from customers
where state = 'TX' and
City = 'Dallas'
I expected the first one to run much faster and it did. Why? Because
there was an index with a compound index key, namely country, state, and
city. In the second case the poor optimizer was faced with walking the
index instead of a quick lookup, as it could do in the first case.
But the clever programmers had used the second form, because they knew
that
all the customers were in US, and they wanted to keep things simple for
the
DBMS.
Not only were they running way too slow, but also, they had written code
that could break when the user community decided to extend the customer
base
to other countries.
This is just one example out of hundreds of possible examples, where the
programmers code strangely in order to speed things up, and instead they
slow things down.
|
