Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
9
Erster Beitrag:
vor 3 Jahren, 6 Monaten
Letzter Beitrag:
vor 3 Jahren, 5 Monaten
Beteiligte Autoren:
ICI, Arie, Fabrice Harari, Viggo Poulsen

Query execution

Startbeitrag von ICI am 23.12.2014 08:51

Hi all.
Query execution first time take a long time (Even with optimized indexes.) , until second time same and other query's prepare data almost immediately after execution.
Is possible to start query first time also in speed time of second.

Than you.

Antworten:

Hi

it all depends on the query and the corresponding data

Best regards

von Fabrice Harari - am 23.12.2014 09:47
Hi ICI

I guess a part of the data structure and the data are buffered the first time. Just a thought: What about making a Query in a background job as soon as you start the program ? ( if it is WD we are talking about)

Best regards
Viggo Poulsen

von Viggo Poulsen - am 23.12.2014 10:20
Thank You for your answers.

Fabrice, yes. Data file has 700-800.000 records.
a) If I want to SELECT some records on same data I expect do that in same time. Now it is between 2min - 2sec . It is big difference.
b) If I want to SUM some data (same number of records) I expect to do that always in (ex. 30secs )
c) Like hreadseek(), query with good index need to point faster on data but it oscillate.

Vigo.
I can not do that in background if I need data to report after user click on button PRINT.

Cache on HFSQL Server is on almost max.recommended. It's faster but not so fast.

I hope it can be improved in next years.
Can it be faster with MySQL or something different than HFSQL ?

Thank You once more !

von ICI - am 31.12.2014 07:11
Hi Vigo

as I was saying, it depends on the queries, and on the data content... as you are not giving us any example, it's hard to help

Some queries can NOT be optimized (contains condition, by example), but most of them will go much faster if you create the appropriate composite keys...

von Fabrice Harari - am 31.12.2014 10:38
Hi,
what do you mean by "the first time"?
- for each user after starting your app?
- after restarting the hfsql?
- otherwise?

I saw this happen every first time of the day after pc was turned on. In this case the pc (pos system) was holding the hfsql .
I turned out to be the system recovery system of Windows, which copied all ndx files as part of a new "restore point".
After excluding these files it was much faster.

von Arie - am 31.12.2014 11:40
Hi Arie.
"First time" is when I start query for the first time. It takes more time to give me result.
I also noticed , if I start similar query on same data, after that, query is finished faster.

Server 2008 is always ON.

Maybe HFSQL CC need time to prepare cache first time or whatever and after that use it and work with it faster. I don't know. I know sometimes qry need ex. 1min 27sec but next time it takes 2-3secs with different parameters. Khm.

I try to understand and eventually skip this situation and make my query's always faster.

Also, QRY files are much faster then same query by Code and Data Source . For me, SQL is SQL but it become different. I like Windev and HFSQL is good but sometimes it ... , hm.

Thank You !

von ICI - am 31.12.2014 15:46
ICI,
it looks like some cache issue indeed. And then you are looking at a black box more or less.

You say there is a diference between the Windev query files and putting a SQL together yourself. That makes sense in some way, because the Windev framework rewrites the query. (to be able to remove unspecified parameter lines)

Maybe this will help. When you use the hfsql log facility, you can get the SQL executed by the engine. It could be the final sql ran by the engine, but we don't know that either. I think that is the closest we can get.

If you are on hfsql19 then you could also use the EXPLAIN keyword to see what is going on. It will show you the "explain plan" of the query and the indexes used. This feature isn't documented very well but I got it from on of the LST's




von Arie - am 31.12.2014 16:30
I just saw this feature it implemented in V18 already...

von Arie - am 31.12.2014 17:15
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.