Hi Stewart,

I believe you have a problem with the optimiser. Maybe your query will be
must faster if you remove all of your indexes from the table, and use an
index just in ZipCode. Of course this method could speed up this specific
query, and put another queries slower.

The best way to check this is to check the PLAN that it's used. If you find
the word 'NATURAL' in the plan, you will know that a index is not used.

Post the plan for your query here.

Artur

----- Original Message -----
From: "stewartbourke" <stewartbourke@...>
To: <ib-support@yahoogroups.com>
Sent: Sunday, August 11, 2002 7:20 PM
Subject: [ib-support] Firebird bug 223060 / greater than in selects


> 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.
>
> Thanks,
>
> Stewart Bourke
>
>
> Yahoo! Groups Sponsor
> ADVERTISEMENT
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
>
>