PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WINDEV 23 → [WD2X] Performance of HF SQL Server
[WD2X] Performance of HF SQL Server
Débuté par Michael Drechsel, 22 juil. 2016 11:46 - 13 réponses
Posté le 22 juillet 2016 - 11:46
Hi,

I have a stopwatch in my application before I call HEXECUTEQUERY() and after Tabledisplay() I stop the watch.

I found a funny behaviour (local or over the internet, different databases, all the same)

The first call is quit fast (1-2 seconds), the next calls taken 5-6 seconds.
Whats the reason ?

IMHO HF SQL should be faster after the first call (cache).

Any ideas ?
--------------------------------------

Mmh, it looks like that the Problem is "tabledisplay". It tooks 5 times longer after the first call.
Posté le 22 juillet 2016 - 17:24
Hi Michael,

do you use MyTable..DisplayEnabled = False before and MyTable..DisplayEnabled = True after TableDisplay()?
Posté le 23 juillet 2016 - 06:09
Hmmmmm,

I have also observed this behavior that is after first call to TableDisplay the consecutive calls to TableDispaly seem to be slower... but I thought it is only me as no one has ever reported this here.

In my case this seems to happen even when I am using HF SQL Classic.

TIA

Yogi Yang
Posté le 23 juillet 2016 - 08:07
Hi Stefan,

yes, it makes no difference.

The table has ~6.500 rows.

It looks like that tabledisplay (delete+fill) take some more time as only fill in the first time.

--------------------------------------------------
OK I found it:

The content is loaded in memory (not direct access, because of special sort option). If I change it to direct access, all calls using the same time .....
Mhh, thats not good ....
Posté le 23 juillet 2016 - 08:37
Ok, here a solution:

Put a

Mytable..BrowsedFile=""

before the "HEXECUTEQRY"

and then a

Mytable..BrowsedFile=MyQuery..name

before the "tabledisplay". (You don´t need a tabledisplay anymore in this case)

Then all calls use the same time.
Posté le 25 juillet 2016 - 19:40
Hi Michael,

I wonder if you measured the time needed for the Mytable..BrowsedFile="". This SHOULD be equal to your time difference (the time needed to EMPTY the table, if that is where it's coming from)...

Best regards
Posté le 25 juillet 2016 - 20:21
Hi Fabrice,

nope.

If I don´t use Mytable..BrowsedFile="" the whole process takes 5 secondes, with that only 1-2 seconds.

Like I wrote, it happens only if you use Query based Tables loaded in memory .... (not direct access)
Posté le 26 juillet 2016 - 13:35
Hi Michael.
So as you said the this behavior happens also on local database hfclassic, so i did a test:

I have a query of about 86000 records. That's 10 times the size of your example.
I also have a table control with 17 columns, all visible.
I created the table control with all default options but "loaded in memory" instead of "direct access", as I like that way. Anyway I tried with "direct access" it was exactly the same times, and it makes sense.


Method 1:
hexecutequery(QryNoname1)
tabledisplay(MyTable)
trace(chronovalue()) // about 1200 milliseconds

Method 2:
MyTable..browsedfile..=""
hexecutequery(QryNoname1)
MyTable..browsedfile="QryNoname1"
trace(chronovalue()) // about 1200 milliseconds

Subsequent calls to both takes the same amount of time.

So I wonder why are you getting those differencies, and also those large times.

Regards,
José Antonio.
Posté le 26 juillet 2016 - 13:59
Hi Jose,

I defined a special sort option ("ignorecase") for all my string fields, maybe this is the reason.
Posté le 26 juillet 2016 - 14:46
Michael

Hows about defining a array based on a structure and bind the table to the array

hexecutequery(QryNoname1)
FileToArray(myArray,QryNoname1)

ArraySort(MyArray,'YourConditions')

TableDisplay(MyTable)

Sort will be blistering fast as it is being done in memory.
Posté le 26 juillet 2016 - 14:54
Hir Derek,

ok, but what´s the difference between "your" memory solution and "PC Softs loaded in memory" ?
It´s both memory .... and should work in the same time ....

Apropos, my solution has a bad sidekick .... hsavescrollbar doesn´t work anymore ... so I went back to tablediplay()
Posté le 26 juillet 2016 - 21:44
Quote
Michael Drechsel

Hir Derek,



ok, but what´s the difference between "your" memory solution and "PC Softs loaded in memory" ?

It´s both memory .... and should work in the same time ....



Apropos, my solution has a bad sidekick .... hsavescrollbar doesn´t work anymore ... so I went back to tablediplay()

Only difference I can see is that I use ArraySort() before I ever get near displaying the result
I assume you are using TableSort() - so unless the PC Soft solution acknowledges the fact that you have checked the ' in memory' option and therefore issues,in the background, a TableDisplay() for you I would think they are sorting the table.

Just a guess of course
Posté le 27 juillet 2016 - 08:31
Hi Derek,

I use the AAF Tablesort in the header of the table. Every user understand, how to use it :-)
Posté le 06 décembre 2018 - 16:59
Hi,

Today I have noticed the same problem in a WD23 project using HFSQL client/server version 23.
The first time i run the query and display the table with 90.000 records it takes 5 seconds.
If i execute this again (without rerunning the project) it is about 2 minutes.
If I deselect the 'loaded in memory' option the problem is solved.

using ..browsedfile="" seems not to help

Did anyone find a solution for this?

Kind regards,
Joris