Re: Improving performance of a query

> > Carlos wrote:
> > SELECT * FROM SSIRRA where
> > (YEAR = 2004 and CUSTOMER = 0000000004 and CODE = 00 and PART >= 00)
or
> > (YEAR = 2004 and CUSTOMER = 0000000004 and CODE > 00) or
> > (YEAR = 2004 and CUSTOMER > 0000000004) or
> > (YEAR > 2004)
> > [snip]
> >
> > ah, the positional query. You can always rewrite this query in the
> > following form:
> >
> > (YEAR >= 2004) and
> > (YEAR = 2004 or CUSTOMER >= 0000000004) and
> > (YEAR = 2004 or CUSTOMER = 0000000004 or CODE >= 00) and
> > (YEAR = 2004 or CUSTOMER = 0000000004 or CODE = 00 or PART > 00)
>
> Unless I'm not seeing something, I don't think that's a correct
> reformulation in general. If customer < 4 and year > 2004 the original
> clause would return true but the reformulation would return false
since
> (year=2004 or customer >= 4) would be false.

You are correct, you also have to exchange '=' with '>' to exchange
'and' with 'or'.

Correct answer is:
> > (YEAR >= 2004) and
> > (YEAR > 2004 or CUSTOMER >= 0000000004) and
> > (YEAR > 2004 or CUSTOMER > 0000000004 or CODE >= 00) and
> > (YEAR > 2004 or CUSTOMER > 0000000004 or CODE > 00 or PART > 00)

It's easy to get tripped up here: the basic problem is how to get the
next record based on a multi part key. My ISAM bridge can write them
either way but the 'and' major form is always faster ;).

MErlin