Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
9
Erster Beitrag:
vor 1 Jahr, 4 Monaten
Letzter Beitrag:
vor 1 Jahr, 4 Monaten
Beteiligte Autoren:
DannHCS, Fabrice Harari, kingdr, Paul Turner, Joris

[WD20] Execution of query slow on HFSQL, fast on MySQL

Startbeitrag von DannHCS am 30.06.2016 09:52

Hi,

I use 2 files with the same description, one on MySQL and one on HFSQL.
I have to update the records of HFSQL file with the records of MySQL file.

I use the column tms_umod (timestamp of the last modification) to know which records need to be updated.
So I execute this query on HFSQL file:
"SELECT tms_umod FROM HFile ORDER BY tms_umod DESC LIMIT 1"
dtLastMod is DateTime = HFile.tms_umod

And then, I execute this one on MySQL file:
"SELECT * FROM MyFile WHERE tms_umod > dtLastMod"

And then starts the copy..,

The problem for me is the execution of the first query (on HFSQL), it's too slow!! WHY?

I've tried to execute the same query on MySQL and it take less than 0,01 seconds

Thanks
Best regards
Dann

Antworten:

Hi Dann,

Does the use of the 'order by' make any difference in speed?

Regards,

Joris.

von Joris - am 30.06.2016 14:05
Hi

what is TOO SLOW?
Is there an index on the timestamp ?
How is the HFSQL setup (computer, drive, ram, cache=
Why not do simply a hreadlast on that key?
...

Best regards

von Fabrice Harari - am 30.06.2016 14:46
Hi Dann

I ditched HFSQL years ago as I found it way slower than MySQL, and certainly far less capable when it comes to more complex queries.

I know many on this forum will disagree with that experience, but none-the-less that was what I found.

Rgds
Paul

von Paul Turner - am 30.06.2016 23:25
Hi,

@Joris
Yes I think that ORDER BY is the statement that makes the query slower.

@Fabrice
I was talking about the query "SELECT tms_umod FROM HFile ORDER BY tms_umod DESC LIMIT 1", as I said on MySQL about 0,01 seconds, but in HFSQL takes about... 30 seconds.
I have no index on the timestamp.

The HFSQL is created dynamically on the description of the MySQL one and contains 33 columns and about 160.000 records.

The HReadLast cannot help me without a sort before (it will takes so many time)

Thank you,
Dann

von DannHCS - am 01.07.2016 06:51
Hi Dann,

if you don't have an index on tms_umod, then you have your answer...

add one and the result will be MUCH faster...

Best regards

von Fabrice Harari - am 01.07.2016 12:01
Try this:

SELECT max(tms_umod) FROM HFile

asssuming tms_umod was indexed.

HTH

King

von kingdr - am 01.07.2016 12:21
Hi Fabrice,
also in MySQL the column tms_umod is not an index,
but the query is so much faster than the same query on HFSQL.

Hi Kingdr,
Yes this query is a little bit faster, thank you very much!

Dann

von DannHCS - am 01.07.2016 13:10
Hi Dann

that does NOT change my answer... I don't know what kind of magic Mysql is using to find a result that fast without an index....

I'm telling you that -IF- you want it fast in HFSQL, you NEED an index on the field... NOTHING ELSE will work

Best regards

von Fabrice Harari - am 01.07.2016 15:11
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.