Query Speed

[Dave LeYanna]

I have noticed that the Query Genie does not take full advantage of available indexes in the production CDX.

For example if my file has a first name index and a lastname index and I do a query on first and last name using the genie it scans the entire file until a record that matches is found or it runs out of records (which could be a long time in some cases).

Is there a way to help the geni or is this an "add on" waiting to happen?

I come (am comming) from a FoxPro environment and the system does seem to be much faster in this regard using the SQL SELECT syntax but a program can still be faster.

djl

djl

[Selwyn Rabins]

RE: Query Speed

the query genie just generates a filter expression.

the issue is whether the filter expression that the genie generates is optimizable.

in order to answer that question, you need to show us what filter the genie generated for you.

i just did a test on a 100,000 record table. i used the genie to generate a filter for lastname = "smith", "jones" or "king". the filter it generated was:

(Lastname = "jones" .or. Lastname = "smith" .or. Lastname = "king" )

and then, since i had a lastname index, a5 did the query in just a few seconds, and returned 200 or so records.

[Dave LeYanna]

RE: Query Speed

I had to "and" the value for the first name and last name.

I was looking for a specific name in the table.

I guess for a filter to work you would have to scan all the records.

It takes about 15 seconds in FoxPro for that query and less than 1 second using a prg that "seeks" the lastname value then loops looking for the first name. The table is ordered on last name. If one does this kind of thing often then writing a little prg in Alpha with a Xdialog input would probally get somewhere near the same results as the FoxPro program.

BTW the query in Alpha V.5 takes 104 seconds with the geni.

I tried the "order by" tab but that seems to be applied to the output. I thought that it may set the indexes for the origional scan.

[Dave LeYanna]

RE: Query Speed

I'm sorry, I forgot to mention that I am testing with a copy of our production table of about 700,000 names. The record is way long and not very optimized. This is a legacy system that is being replace a little at a time.

djl

[Selwyn Rabins]

RE: Query Speed

a5 does NOT have to scan all records to do a query. i am not sure how you would arrive at that conclusion.

Lightening Query Optimization in A5 should be very similar to Foxpro's query optimization.

You could have a table of millions of records, and a "seek" (foxpro's term) , or a "find" (a5's term) which positions the record pointer at a specific record in an index will be essentially instantaneous in a5v5. once you have positioned the record pointer, looping through records that match a particular index key will be very quick.

i still need to see the query that the genie generated for you.

[Dave LeYanna]

RE: Query Speed

---------------------
a5 does NOT have to scan all records to do a query. i am not sure how you would arrive at that conclusion.
---------------------

I see the counter in the status area in the bottom od=f the window go through all of the records giving the count and the % done. When it reaches 100% the result set shows. It scans all of the records.

---------------------
Lightening Query Optimization in A5 should be very similar to Foxpro's query optimization.

You could have a table of millions of records, and a "seek" (foxpro's term) , or a "find" (a5's term) which positions the record pointer at a specific record in an index will be essentially instantaneous in a5v5.
---------------------
I find that this is indeed corect. It is essentially instantaneous if the table is ordered on the index for a field that you are finding a value on.


---------------------
once you have positioned the record pointer, looping through records that match a particular index key will be very quick.
---------------------

That is what I thought but I havn't written a little procedure to test it. In fact, I'll have to look at the documentation to see how to start.




---------------------
i still need to see the query that the genie generated for you
---------------------

Filter Expression
(RLFIRSTNAM = "David" ) .AND. (RLLASTNAM = "LEYANNA" )

Order Expression
RLFIRSTNAM+RLLASTNAM

There are the following indexes available in the production .CDX

name of index "mtaglastna" expression UPPER(rllastnam)
name of index "mtagfirstn" expression rlfirstnam




I hope I have provided the information you have requested.
Thanks for your help.
djl

[Selwyn Rabins]

RE: Query Speed

i just recreated a table with 20,000 records using the same fieldnames and indexes that you have and did a query. a5 used LQO, and did not have to scan all records.

to figure out why LQO is not kicking in for your table, i would have to see the files. was the .cdx created by foxpro? it is possible that a5 is not recognizing it.

if you go to the index builder in a5 and rebuild these indexes, and then redo the query, does a5 then use LQO.

with regard to how to do a find, and then process records in xbasic, here is how you could do it.
assume that the table is called "customer"
the index tag is called "lastname".
you want to scan for "smith"

t = table.open("customer")
i = t.index_primary_put("lastname")
t.fetch_find("smith")
while t.lastname = "smith"
'do whatever you want to each record here
t.fetch_next()
end while

[Tom Cone Jr]

RE: Query Speed

Selwyn and Dave,

Pardon me for jumping in...

I think the issue Dave is seeing is because he does not yet have an index built on lastname AND firstname. He has one on lastname, and one on firstname. His query however is for a specific lastname AND a specific firstname.

The solution may be simple, why not build an index on Lastname & Firstname, so that the query can use it?

If I'm off base, please, will somebody tell me to butt out.

-- tom

[Dave LeYanna]

RE: Query Speed

Thanks Tom;

I'm aware of this kind of solution but this is an old system written in FoxPro with over 65 programs that refer to index names by number and the .CDX already has 35 indexes in it. Very Ugly.

Some optimizing engines could use the last name index to find all the last names that are correct and then use the firstname index to see which records in that index are in the first result set. Because the record sizes are smaller for indexes and the can process the index sequencially (pardon the spelling) it dos not have to transvers the entire second index, onlt the part of the index that has the correct value for first name. For example if there were 300 "David"s in the first name index and the first "David" was the 500th record, it would skip to the 500th record to get to the first "David" look at the record number that that one is pointing to, see if that record number is in the first result set of correct last names and process the next 300 records in like manner. If the Names database has 600,000 records it only had to look at 300 of them to see if any match for first name instead of all 600,000 if an index was not used.

All behind the curtain, mind you...

djl

[Dave LeYanna]

RE: Query Speed

I'm working on reindexing the production CDX in Alpha5v5. It's taking a long time but thats because of having 35 flippin indexes in it. (Not my design, but I can't change it right now...)

I thank you for the code snippit.
You are being most helpfull


djl

[Tom Cone Jr]

RE: Query Speed

Dave,

Beware of using a different definition for the index key tags than you will be using in the query. As you know, there's a big difference between a character field and a trimmed character field. -- tom

[Selwyn Rabins]

RE: Query Speed

tom

a5's LQO does not require an index on "lastname + firstname".

if you have one index on lastname
and another index on firstname, then a query

lastname = "smith" .and. firstname = "john" is optimizable.

[Dave LeYanna]

RE: Query Speed

Well...

The entire cdx is reindexed while in Alpha and there is no difference in the speed. 104 seconds.

I guess the next step is to actually CREATE the CDX in Alpha and try it that way.

I'll keep you posted.

djl

BTW,
I saw the post to Tom about two seperate indexes and I am encouraged.

[Tom Cone Jr]

RE: Query Speed

Did not know that. Thanks.

-- tom

[CALocklin]

RE: Query Speed

I've read this with interest. I'll be interested to know the outcome.

I noticed that the last name index was using UPPER:
name of index "mtaglastna" expression UPPER(rllastnam)

Could this be causing an issue with LQO?