[WD12] Query optimizer

Hello to you all,

Now I got an answer to my last post (thank you Fabrice) I'm trying to use the automatic query optimizer. I've the following query and there is no composite key in the file I'm using:

Quote
SELECT * FROM HF_ADM_Transacties
WHERE HF_ADM_Transacties.Boekjaar = {iBoekjaar}
AND HF_ADM_Transacties.Dagboek = {sDagboek}
AND HF_ADM_Transacties.Code = {sNummer}
AND HF_ADM_Transacties.Regel > 0
AND HF_ADM_Transacties.RegelCode = 1
AND HF_ADM_Transacties.AfboekRegel = 0
AND HF_ADM_Transacties.Periode >= 0
ORDER BY Regel ASC

I thought the query optimizer should suggest a composite key of the following fields:
Regel / Boekjaar / Dagboek / Code / RegelCode / AfboekRegel / Periode

But he doesn't! What can I do wrong or is this right?

Hi Louis...

isn't it proposing anything?

Anyway, in this case, the composite key should have the following order:
Boekjaar / Dagboek / Code / RegelCode / AfboekRegel //// Periode _OR_ Regel

The = conditions should always be first as they ar the most discriminating...

The order of the variables for the = conditions matter only if one of the criteria is strongly more discriminating than the other (date is statistically more discriminating than a boolean, as there will be less answer of a specific date than of true or false (50% mos of the time). That order is something where YOU can do better than the query optmizer anyway...

There is no need to put several > or >= inside a composite key, as that doesn't allow for a more optimised access anyway

Best regards

Hello Fabrice,

He didn't advice anything indeed. I've tried several other situations and sometimes he gives an advice sometimes not.

I thought in this example to place the 'Regel' in front of the compostie key because the ORDER BY. If I understand correctly that isn't important? Is the order take place after the selection? So only the WHERE-clause defines the composite key?

Hi again...

obviously, I have no way of knowing exactly how the HFCS engine (or any other) optimization has been implemented exactly... So all my answers are speculative here. But I certainly hope that the sort by would be done afterward in a case like this one:

If you conditions return 100 record and yo have to sort them, it is much faster than going through 300000 records in the proper order and EXCLUDE all the records not matching the conditions.

Of course, the best case is always when condition AND sort order are on the same variables, but when it's not possible, and if your conditions are selective enough, then it's clearly better to have a key organized that way.

And if YOU are providing such a key and NO key on the 'order by', then the engine should use it to optimize the access the way YOU want.

Best regards

Hello Fabrice,

Thanks for your time again. Whenever you come to Meer in Belgium to our WDG I'll buy you a drink :cheers:

Hi Louis,

I'm not too eager to accept any optimizations suggested by the query wizard:
- extra keys require more overhead on normal file operations, so you have to balance the time needed for the execution of a query against the time needed to maintaining all the extra keys.
- extra keys also need data restructuring if the app is already deployed.

So if a query is executed very frequently it may pay to optimize your keys, otherwise accept some extra processing time.

Another option might be the use of the new databinding feature and create an array of structures and fill it by programming in an optimised manner. Haven't tried that yet.

Regards,
Piet

p.s. I received an email from pcsoft containing a technical article about databinding, did you?

Hello Piet,

Thanks for your time. I understand the time to write the indexes will increase at the number of composite keys and indexes. I try to use the less possible, and though have a good performance (try to get the right balance).

Yes I've got that mail as well, but I use WD12 at the moment.

Another tip: instead of "select *" you can name the required columns "select col1, col3, col8"

1. Less information will be returned, which causes less network traffic

2. if where-columns AND the requested data-columns are ALL part of an index, the hfcs-engine doesn;t need to do a lookup in the data-file, for each record. All information can be found in the index-file only. This can speed things up, depending on your situation.
So sometimes it is wise to add an extra column to an index (at the end!), even if you don't use it in search-criteria.

But there is no golden rule, covering every situation. It depends...

Arie

Hello Arie,

Thanks for your explanation.