At 06:20 PM 11-08-02 +0000, you wrote:

>I have a pretty heavy query which relies on a 'greater than' in a
>select
>and it seems to take forever (> 20 secs). If I leave out the '>' the
>query executes in less that 1 second. I did a search on the web and
>found bug 223060 in firebird which says there is a known bug when
>using > in a select. I was wondering if there is any fix for this
>yet?
>
>The query is:
>
>select L.LOCCODE, L.LOCNO, T.ZIPCODE, R.VERSIONNO, R.ROUTEFLAG001,
>R.FLOCNO,
> TH.TOURNAME, TH.SORTFLAG001, L.LOCBARCODE,
> T.OWNERNO, O.NDITYPE
>from TBROUTE R, TBTOURHDR TH, TBTOUR T, TBLOCATION L, TBOWNER O
>where R.FLOCNO=T.LOCNO and L.LOCNO=T.LOCNO
>and O.OWNERNO=T.OWNERNO
>and TH.PRINCIPALNO=T.PRINCIPALNO
>and TH.OWNERNO=T.OWNERNO
>and TH.LOCNO=T.LOCNO
>and TH.TOURNO=T.TOURNO
>and R.ACTIVEFLAG='Y'
>and T.ACTIVEFLAG='Y'
>and R.OLOCNO=2
>and T.COUNTRYNUM='276'
>and T.ZIPCODE >= '36250'
>
>If I change the last line so it is ='32650' it runs very much
>quicker.
>
>Note: Even the simple query:
>
>select *
>from tbtour
>where countrynum='276'
>and zipcode >= '32650' exhibits the same behaviour.
>
>
>I believe I have the necessary indexes created on the various
>tables. In the case of tbTour, I have a compisite index on
>tbzipcode, tbcountry.
>
>Would anybody have any ideas/suggestions?
>
>My environment is Linux, Suse 7.3, firebird classic server v1, using
>C API.

To determine whether bug 223060 is biting you, your test should be whether
there is any difference between "> '36250' " and ">= '36250' ". The
condition under which this anomaly is reported to occur is when all or most
of the values in the column are identical.

This is much more likely to be an index issue, at first look. Given that
you have only a composite index available and it includes a "country"
column, you possibly have a very "noisy" index here: first, it is
character, so the engine has to do a lot of business by evaluating each
character in turn; and second, since postal code formats and sizes vary
from country to country, it's likely to be an apples and oranges
comparison. Consider for example comparing 'NR31 2BN' (a UK postcode) with
your parameter '36250'. The comparison is done strictly on the ascii code
of each character...making it far more complex to evaluate than the integer
comparison reported by Ivan.

Looking at your description, you say this index is on [tb]zipcode and
[tb]country. But you don't have a column "country" in your query. If this
isn't a typo, and you have both "country" and "countrynum" columns in your
table (with or without an index on "countrynum") then the plan is likely to
be pretty confused, given that "countrynum" is in the WHERE clause.

First place to start looking is in the plan. Second is to consider whether
a ">" or "<" comparison on that zipcode column is reasonable. The solution
may well be to add a single-column index on zipcode and to drop the other
one if the "country" element isn't used for anything.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________