[V4RB/VSTUD] - Odd query performance

[Russ Tyndall]

On 5/2/06 2:56 AM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

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

Sent. This is mantis 0001575.
 
> 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 ...

My queries are a mess.  Most of them I wrote years ago when I was just
learning Jet, and could not even spell S-Q-L.  :-)

Then (thankfully!) I moved to V4RB 1.x --- but I had to rework queries again
and large parts of application to deal with parser "order issues".  Now, I
am trying to migrate to 2.x, but am having to work "back out" of 1.x
workarounds!  But I am glad to do this.

Also, I want to learn fully about ObjPtrs, Binary Links, etc. but the more
research I do, the more it appears that the best thing to do is trash
everything and start all over.  But, I am still reading and trying to
understand docs.  And, I am getting satisfying speed just using "plain"
features of Valentina.  One thing at a time.

Vstudio is making this entire process very much easier, for me at least.

Russ