[V4RB/VSTUD] - Odd query performance

[Ruslan Zasukhin]

On 5/1/06 11:10 PM, "Russ Tyndall" <fitzbew at nc.rr.com> wrote:

Hi Russ,

> 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?

Yes we have fix this

> 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.)

Yes, send me files.

This issue again push us to think about log of time of EACH STEP during
execution. Such feature can help to Valentina developers see where queries
eat time and try optimize them ...

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]