[V4RB/VSTUD] - Odd query performance

[Russ Tyndall]

OS X.4.6, V4RB 2.3b10, but testing queries with Mac Vstudio 2.3.1

Here is interesting situation.  Valentina returns this query very swiftly
(.2 seconds!):

select *   [real query selects 36 fields by name, not using "*"]
from mainrecords, locations, producttypes, geozones, statetable,
producttypesforms, sourcetable
where mainrecords.producttypes_no = producttypes.producttypes_no
and mainrecords.Location_key = locations.location_key
and locations.geozone_state = geozones.geozone_state
and locations.state = statetable.state
and mainrecords.form_id = producttypes.form_id
and mainrecords.source_id = sourcetable.source_id
and mainrecords.discounted = 'N'
and ((mainrecords.Date_from_YYYY >= '1970'
and mainrecords.Date_to_YYYY <= '1980') or (mainrecords.Date_to_YYYY >=
'1970' 
and mainrecords.Date_from_YYYY <= '1980'))
and mainrecords.producttypes_no = '000121'

But, if I add one line (locations.state is indexed) to the end of the query:

***
And locations.state = 'NC'
***

Then, the same query takes 20 seconds to perform!  Correctly returns only
about 10-11 records.

The query returns a variety of varchar, long, string fields.
All the joins are based on fixed-length string fields.  Most of join fields
are indexed.

The largest table listed in query is only 30k.

The Date_from_YYYY and Date_To_YYYY probably should be integers or some
other numerics, but even when I test with such changes, it makes no
difference.  Currently those fields are 4 character strings.

I believe this identical query ran with normal Valentina speed in 1.x.

Does anyone see any obvious problems, explaining why 2nd version of query is
so slow?  In old days, Valentina was sensitive to order of joins, but this
is not true in 2.x, is it?  Besides, I have experimented with order and it
seems to make no difference. (Which makes me very happy!)  Still, I believe
something is going awry.

(Ruslan, if you think this is some parser issue, or whatever, I could easily
send you files to reproduce.)

-- 
Russ Tyndall
Wake Forest, NC