|
| [WD22] Optimizing data search speed |
| Iniciado por guest, 27,dic. 2017 17:48 - 3 respuestas |
| |
| | | |
|
| |
| Publicado el 27,diciembre 2017 - 17:48 |
Greetings,
I created a report that displays Items that were not sold during the selected period.
This is my current code:
HFilter(STOCK,Store_Number,COMBO_STORE.COL_Store_Number,COMBO_STORE.COL_Store_Number)
HReadFirst(STOCK)
nRecRead is int
nTotal is int = HNbRec(STOCK)
WIN_WAIT.PROGBAR_WAIT_WINDOW = 0
WIN_WAIT.PROGBAR_WAIT_WINDOW..MaxValue = nTotal
HReadSeekFirst(ITEM,Item_Number,STOCK.Item_Number)
WHILE HOut(STOCK) = False
nRecRead += 1
WIN_WAIT.PROGBAR_WAIT_WINDOW = nRecRead
IF STOCK.Quantity <= 0 THEN
HReadNext(STOCK)
CONTINUE
END
IF COMBO_GROUP..DisplayedValue <> "" AND ITEM.Group_Number<> COMBO_GROUP.COL_Group_Number THEN
HReadNext(STOCK)
CONTINUE
END
HReadSeekFirst(QRY_Items_Sold,"Item_Number",STOCK.Item_Number)
IF HFound(QRY_Items_Sold) = False THEN
HReadSeekFirst(QRY_Items_Procured,"Item_Number"",STOCK.Item_Number)
IF HFound(QRY_Items_Procured) = False THEN
HReset(TEMP_SS)
TEMP_SS.Item_Number = STOCK.Item_Number
TEMP_SS.Item_Barcode = STOCK.Item_Barcode
TEMP_SS.Item_Name = STOCK.Item_Name
TEMP_SS.Item_Type = STOCK.Item_Type
TEMP_SS.Quantity = STOCK.Item_Quantity
TEMP_SS.Purchase_Price = STOCK.Item_Purchase_Price
HAdd(TEMP_SS)
END
END
HReadNext(STOCK)
END
HDeactivateFilter(STOCK)
HClose(TEMP_SS)
What the code does is that it takes the Item in the STOCK and checks if there were new procurements or sales for that Item in given time period.
If the Item was not found in the queries, it is written in temporary file that the report will read data from.
This code works, but the problem here is that it takes a lot of time to finish searching for all records that match the criteria.
These two HReadSeekFirst functions inside While HOut rerun on each record of STOCK and that slows down the performance.
How can I optimize this? Is there any other better way to do it?
I hope that my explanation was clear and understandable.
Kind Regards,
Rasta |
| |
| |
| | | |
|
| | |
| |
| Publicado el 27,diciembre 2017 - 18:27 |
| SQL Queries. They will almost always be faster than a bunch of hFunctions. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,diciembre 2017 - 12:27 |
Hi.
You are scrolling through the complete file so that HFilter checks whether or not a record meets the query conditions.
If your HF is in HF/CS then create a query that runs the search on the server and returns only the results that meet the query. This will allow successive queries to make use of the HF cache and be faster.
Then you can use a FOR EACH loop to browse the records returned by query.
Rubén |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,diciembre 2017 - 10:18 |
Rasta,
create a query on the STOCK file including an LEFT OUTER JOIN to the ITEMS_SOLD file and a LEFT OUTER JOIN to the ITEMS_PROCUREMENTS file.
It will give you NULL-values for both joined files if there is NOT a corresponding record.
You can then loop trhought the result set and test for these null-values.
Also a "NOT IN" query will work.
SELECT * FROM stock WHERE item_number NOT IN ( put the code of our QRY_Items_Sold here....)
You have to try for yourself which option is the fastest. It depends on your situation.
Not to mention that you need proper indexes on all your files. |
| |
| |
| | | |
|
| | | | |
| | |
|