Sorry, should have provided this info:

The testing of this query was running against a Firebird 1.0 server.

Preparing the query against a Firebird 1.0.3 server produces the same
plan (I have not tried running it, as I don't want to slow down our live
server).

Interestingly enough, running it against our FB 1.5 RC 4 test server
produces various results as follows:

Version 1: Using (Table1.ForeignKey2 >= 962) or (Table1.ForiegnKey2 <=
973)
Result: Plan had no Naturals in it, performance good, however, not all
the Ids are sequential so I would still need to use this and option 3.

Version 2: Using (Table1.ForeignKey2 in (962, 963, 964, etc)
Result: Used a Natural in the plan, performance not so good, but seems
better than ver 3.

Version 3: using lots of Ors
Where
((Table1.ForeignKey2 = 962) and (Table1.Status = 8) and
(Table4.ForeignKey1 = 170))
or
((Table1.ForeignKey2 = 963) and (Table1.Status = 8) and
(Table4.ForeignKey1 = 170))
Or (etc...)
Result: Is still very slow. For some queries, against FB 1 we have found
that this speeds things up..

Version 4: using lots of Ors but not repeating the whole clause
Where
((Table1.ForeignKey2 = 962) or (Table1.ForeignKey2 = 963) or etc))
and (Table1.Status = 8) and (Table4.ForeignKey1 = 170)
Result: Is also very slow...

-----Original Message-----
From: Leyne, Sean [mailto:sleyne@...]
Sent: Tuesday, 29 July 2003 1:16 p.m.
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Query tuning help


Colin,

> Now this plan uses no Natural scans, does a couple thousand indexed
> reads and returns fairly quick...
>
> But what I want is:
> Where (((Table1.ForeignKey2 >= 962) or (Table1.ForiegnKey2 <= 973))
> and (Table1.Status = 8) and (Table4.ForeignKey1 = 170))
>
> As soon as I add this it puts in a Natural Scan and takes hours....

Please confirm which version of FB you are running?


Sean


To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/



######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################