|
FOROS PROFESIONALES
WINDEV, WEBDEV y WINDEV Mobile |
| | | | | |
| MySQL and Composite Indexes |
| Iniciado por Daniel Ramirez, 17,may. 2004 06:57 - 3 respuestas |
| |
| | | |
|
| |
Publicado el 17,mayo 2004 - 06:57 |
Hello:
It seems to me that filtering records trough HFilter() is not possible on composite indexes while reading MySQL tables, I would like to know if this is true and in that case, to get some workaround ideas, and if this is not true, maybe to get some examples.
Also, if I call HFilter() on some index (single column one) from almost every part of the code, it takes too much time to complete the instruction (if inside a loop to be really noticeable), but if the HFilter() is called from the ROW DISPLAY part of a table, it take only a fraction of the time to complete (very important time variations, around 1.5 minutes versus 5 seconds). I post this info only to make you all aware of that.
I am working with MySQL 4.0.18 and WD 7.5 206ju.
As always, any comments will be very appreciated. |
| |
| |
| | | |
|
| | |
| |
Publicado el 18,mayo 2004 - 02:29 |
Daniel,
Before going further "using" these Windev-Hfilter-commands....
Take a "day-off" to play-around/work and study with "Queries-editor" of the Windev-Developper in 7.5.
This is valid both for files stored in Hyperfile format or files ie stored in MySQL-format. Especially when you use the Native-accesses.
And sorry to "shock" you maybe but "throw-away" the skills you developped in the past, using these HFilter-commands of Windev.
That was for Windev 5.5 or earlier versions.
I know the Hfilter-commands are (rather) easy to use and they are fast.
But you will find out that the usage of the Windev-Query-editor (to replace your accesses of ranges of records with Hfilter-commands) and the usage of the output of the Windev-Query-editor (=the "Windev-Queries") is even easier and above all again faster in execution, much faster... Try it out.
And especially in the case of MySQL you will see: MySQL will "love-it".
This new approach to get "ranges of records" is different of course
and a bit unknown area in the beginning.
But you will see (Cuando habras cojido éste hilo...) you will wonder why you continued working so long with HFilter.
You will not have to worry at all about how to access the composite keys.
Only, of course, that you defined in the Analysis the adequate-ones!
Because otherwise, just like with HFilter your final accesses will be "poor".
Try this out on a (very) simple Query-example-case first.
Ie You want to retrieve (some fielddata of) the Invoice-records of a Customer.
Of course in the analysis of the file containing the Invoice-records, make sure the customer-Id sits in the invoice-record and make sure this customer-Id is at least a Key-index (usually not unique). Better make a composite-key containing the Customer-Id first, followed by the Invoice-date, and the Invoice-#.
And make sure you (for the future "bigger" queries !!) define a "liaison" between the Invoices-file and the Customer-file!
Now make a Windev-Query with the Windev-Query-editor just on that Invoice file, specifying which fields of the invoice-record you want to obtain as output later when this Query is executed (Ie at least the Invoice-Date and the Invoice-number) And let's call this Query for Windev: "InvoicesQuery".
While making this query make sure you add also the Customer-Id (of the invoices-file) in the Query. And... that you add a "selection"-condition to that Customer-Id-Query-field.
The condition will be that it will have to be "equal to" a Parameter-field called "PCustId".
And (You can) add further to the query some "sorting" specifications:
Sort by Invoice-Date/Invoice-#
Save the "InvoicesQuery".
Before using it into your Windev-code iso of the Hfilter
try this Query out by running it on its own (ù, just like testing a window)
You will be asked to "enter" a valid Cutsomer-Id for the PCustId-parameter...
And You will see the query-output-fields you will/would get later for that customer...
Now in your Windev-code on the place where you wanted to retrieve,
access the invoices of a given customer (=GivenCustomer) with your Hfilter-command you code instead the following:
InvoicesQuery.PCustId = GivenCustomer //To pass your customer-Id to the Query
ResOK = HExecuteRequete(InvoicesQuery)
If not ResOK then
Error("Troubles during execution InvoiceQuery"); RETURN
END
NumberOfInvoices = HNbRec(InvoicesQuery)
//Returns you the number of Invoices fount!
Trace("Customer "+GivenCustomer+ " has "+NumberOfInvoices+" invoices")
HReadFirst(InvoicesQuery)
While Not HOut
Trace("InvDate="+InvoicesQuery.InvoiceDate+...
"Inv#="+InvoicesQuery.InvoiceNumber+" etc.")
HReadNext()
END
This is a very, very, very simple Query only handling ONE file.
Which is and was anyway the limitation of the Hfilter-command!
Image developping later queries combining data via selection-criteria from several "files" (making "jointures")
The code and the methods explained are valid for Hyperfiles and all
other DB-types, ideally accessed via NativeAccess.
If you find the time, measure the time spent in execution of a Hfilter-command
versus a Query. You will be amazed!
(I found always at least a time-win-ratio of at least 4 times faster!)
Check if you can the "busy-time" of your MySQL-System.
(It hardly moved...)
I'm sure You'll see new kind of days starting! Exito amigo!
Lieven De Nys,
Member of the Dutch speaking Benelux-Windev-Developpersgroup. |
| |
| |
| | | |
|
| | |
| |
Publicado el 18,mayo 2004 - 04:10 |
Lieven:
I am now in a hurry, but have read your vast answer and think I have cathed the idea, I will write again tomorrow, but I want you to know that now I owe you a full one month beer supply...
Out of topic:
Your Spanish seems better than my English!
Me tienes gratamente sorprendido!!
Thanks again!
Muchas gracias, compadre! |
| |
| |
| | | |
|
| | |
| |
Publicado el 18,mayo 2004 - 04:12 |
Sorry, should read:
...I have catched the idea... |
| |
| |
| | | |
|
| | | | |
| | |
| | |
| |
|
|
|