Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
10
Erster Beitrag:
vor 11 Monaten
Letzter Beitrag:
vor 11 Monaten
Beteiligte Autoren:
Gianni Spano, Arie, ccc2, Emidev / Firetox, Allard, Fabrice Harari

[WD] How to speed up the loading of records on a table

Startbeitrag von Gianni Spano am 09.01.2017 18:15

Hello to All

I'm checking an application that uses a lot of records.
Imagine you have a list of categories for the products.
Clicking a category, will show you all the products corresponding to its category code.

In some cases, there are categories with over 3000 records and it take some time to show you the complete list.
Is there a function or table setting, to speed up the records loading or if is there a setting in the table to show the first 100 records, while it is loading the rest?

I don't know if i have exposed correctly my needed.

Tips and tricks are welcome.
Thanks in advance

Gianni

Antworten:

Hi Gianni,

there are different ways of doing this :

1. hfilter on a composite key (category + sort order for the products) and a table linked to the file (illimited, NOT loaded in memory). In that case, only the DISPLAYED lines are read

2. a query with the number of records defined

3. a table filled by code with a secondary thread loading the in the background

...

Best regards

von Fabrice Harari - am 09.01.2017 18:49
Hi Gianni,

3000 records should load instantly. So If this is not the case there is something wrong with your coding , I guess.

Are you running a querie? Do you use some kind of filter ? If you explain a bit more what you are doing helps in thinkig about what possibly goes wrong.

regards
Allard

von Allard - am 09.01.2017 18:52
Hello


@Fabrice
Thanks for your suggestions. I will hve a try.

@Allard
Here is the routine used to load a list of products associated to a specific category
that the user select.

My scenario: the user can select a main category and a sub-category.
Depening on its selection (the selection is done selecting a treeview with the list of the main category and (where it is included) its sub-categories.

This is the portion of code used to populate data on the table.


sSql is string
dsMyQuery is Data Source
bRes is boolean
bDati is boolean
bFlag is boolean
sUnità is string
//sCateg is string
//sSottoCateg is string
//sFornitore is string
//sMarca is string
nAliquota is int
sCodArt is string
rPrezzo is real
nFascia is int=1

IF slevel2 "" THEN
HReadSeek (categoriemerci,descrizione,slevel2)
IF HFound (categoriemerci) THEN
bFlag=True
ELSE
bFlag=False
END
END

IF bFlag=True THEN
sSql="SELECT * FROM magazzino WHERE categoria="+categoriemerci.id+" ORDER BY descrizione "
END

bFlag=False
IF slevel3 "" THEN
HReadSeek (sottocategmerci,descrizione,slevel3)
IF HFound (sottocategmerci) THEN
bFlag=True
ELSE
bFlag=False
END
END

IF bFlag=True THEN
sSql="SELECT * FROM magazzino WHERE categoria="+categoriemerci.id+" AND sottocategoria="+sottocategmerci.sottocateg_id+" ORDER by descrizione "
END

TableDeleteAll (TABLE_Magazzino)

bRes=HExecuteSQLQuery(dsMyQuery,hQueryDefault,sSql)
IF bRes THEN
bDati=HReadFirst(dsMyQuery)
IF bDati THEN
WHILE NOT HOut (dsMyQuery)
IF dsMyQuery.unita_misura > 0 THEN
HReadSeek(unita_misura,id,dsMyQuery.unita_misura)
IF HFound (unita_misura) THEN
sUnità=unita_misura.descrizione
ELSE
sUnità="NR"
END
END
sCodArt=dsMyQuery.codice
CalcGiacenza(sCodArt)
// IF dsMyQuery.categoria > 0 THEN
// HReadSeek (categoriemerci,id,dsMyQuery.categoria)
// sCateg=categoriemerci.descrizione
// ELSE
// sCateg=""
// END
// IF dsMyQuery.sottocategoria > 0 THEN
// HReadSeek (sottocategmerci,PRIMARY,[dsMyQuery.categoria,dsMyQuery.sottocategoria])
// sSottoCateg=sottocategmerci.descrizione
// ELSE
// sSottoCateg=""
// END
// IF dsMyQuery.fornitore_abituale > 0 THEN
// HReadSeek(fornitori,id,dsMyQuery.fornitore_abituale)
// IF HFound (fornitori) THEN
// sFornitore=fornitori.ragione_sociale
// ELSE
// sFornitore=""
// END
// END
// IF dsMyQuery.marca > 0 THEN
// HReadSeek (marche,id,dsMyQuery.marca)
// IF HFound (marche) THEN
// sMarca=marche.descrizione
// ELSE
// sMarca=""
// END
// END
IF dsMyQuery.codice_iva "" THEN
HReadSeek (aliquote,iva_cod,dsMyQuery.codice_iva)
IF HFound (aliquote) THEN
nAliquota=aliquote.iva_aliquota
ELSE
nAliquota=0
END
END
HReadSeek (prezzi,PRIMARY,[dsMyQuery.codice,nFascia])
IF HFound (prezzi) THEN
rPrezzo=prezzi.prezzo
ELSE
rPrezzo=0
END
TableAdd (TABLE_Magazzino,dsMyQuery.codice+TAB+dsMyQuery.descrizione+TAB+sUnità+TAB+...
rPrezzo+TAB+dsMyQuery.prezzo_acquisto+TAB+gnGiacenza+TAB+...
nAliquota+" %")
HReadNext (dsMyQuery)
END
END
END



To speed up the loading, i have commented some lines of code, but the result is the same.

Tips and tricks are welcome.

Thanks in advance

Gianni

von Gianni Spano - am 09.01.2017 19:34
Gianni,

try using TABLE_Magazzino..DisplayEnabled = False at the beginning and set it to True again at the end. This will avoid redisplaying the table on EVERY TableAdd-line.

Another thing you can do is creating an array of structures . This array is holding your data. And then bind your table to this array.
Use FileToArray() to copy the query-data into the array and use TableDisplay() to show the data, This is all very fast, because it is done on the WD framework level.
You can also filter the data in between and remove all unwanted records from the array. Sound a bit strange, adding records in the first place and removing them afterwards. But array-handling is very fast!
I even use the Serialize() with the psdXML option on every row of the array (3000 in your case) and use the result for a search option (as the LIKE option in SQL). The delay is almost unnoticeable, even with 1000+ records.
Requires a bit more coding though,

von Arie - am 09.01.2017 20:31
Arie

Thanks for your suggestions.

:-)

Gianni

von Gianni Spano - am 09.01.2017 20:52
Gianni,

you also commented out a few portions.There you do extra HReadSeek to find additional info. This will slow down the loading significally. It is far more efficient to add these child-tables to the query and let the HF engine do the work for you. It's designed to do things like that ;)

I can also recomment the OOP-project of Peter H. It uses FileToArray as well.
http://repository.windev.com/resource.awp?file_id=91;exemple-pour-utiliser-une-strategie-poo

von Arie - am 10.01.2017 19:53
hello

the probleme is in the coding
you read 3000 lines and for each line you do 3 read then a total of 9000 read
the SQL of HF is reading 500 line per second (hread on file are more better 1500 to 3000 line per seconde but SQL HF is more slower)

then for your code = 9000/500 = 18 secondes
if you made this with one query the time is 3000/500 = 6 secondes

imagine this with 20000 or 100000 line to read !!!!!
for example you code is possible with one query with join on other table because for each table you have one information

sSql="SELECT * FROM magazzino
LEFT JOIN aliquote ON aliquote .codice,nFascia = magazzino.codice,nFascia
WHERE categoria="+categoriemerci.id+" AND sottocategoria="+sottocategmerci.sottocateg_id+" ORDER by descrizione "

and you do that for each table you seek in the code then you have one query with all data and 3000 read instead of 9000
and for the original code 3000 read instead of (3000*7=27000)

i do this for one client that his code made 40 secondes to charging a planning : with one query the planning is charged in 2 secondes now

von Emidev / Firetox - am 10.01.2017 22:19
Thank you to all for your support and suggestions.
I appreciate it.

Regards
Gianni

von Gianni Spano - am 11.01.2017 07:38
Hi Gianni,

try to do most of the work in the query (sql) .

von ccc2 - am 11.01.2017 15:52
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.