|
[WD2X] Performance of HF SQL Server |
Started by Michael Drechsel, Jul., 22 2016 11:46 AM - 13 replies |
| |
| | | |
|
| |
Posted on July, 22 2016 - 11:46 AM |
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. |
| |
| |
| | | |
|
| | |
| |
Posted on July, 22 2016 - 5:24 PM |
Hi Michael,
do you use MyTable..DisplayEnabled = False before and MyTable..DisplayEnabled = True after TableDisplay()? |
| |
| |
| | | |
|
| | |
| |
Posted on July, 23 2016 - 6:09 AM |
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 |
| |
| |
| | | |
|
| | |
| |
Posted on July, 23 2016 - 8:07 AM |
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 .... |
| |
| |
| | | |
|
| | |
| |
Posted on July, 23 2016 - 8:37 AM |
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. |
| |
| |
| | | |
|
| | |
| |
Posted on July, 25 2016 - 7:40 PM |
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 |
| |
| |
| | | |
|
| | |
| |
Posted on July, 25 2016 - 8:21 PM |
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) |
| |
| |
| | | |
|
| | |
| |
Posted on July, 26 2016 - 1:35 PM |
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. |
| |
| |
| | | |
|
| | |
| |
Posted on July, 26 2016 - 1:59 PM |
Hi Jose,
I defined a special sort option ("ignorecase") for all my string fields, maybe this is the reason. |
| |
| |
| | | |
|
| | |
| |
Posted on July, 26 2016 - 2:46 PM |
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. |
| |
| |
| | | |
|
| | |
| |
Posted on July, 26 2016 - 2:54 PM |
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() |
| |
| |
| | | |
|
| | |
| |
Posted on July, 26 2016 - 9:44 PM |
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 |
| |
| |
| | | |
|
| | |
| |
Posted on July, 27 2016 - 8:31 AM |
Hi Derek,
I use the AAF Tablesort in the header of the table. Every user understand, how to use it |
| |
| |
| | | |
|
| | |
| |
Posted on December, 06 2018 - 4:59 PM |
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 |
| |
| |
| | | |
|
| | | | |
| | |
|