Diese Seite mit anderen teilen ...

Informationen zum Thema:
WinDev Forum
Beiträge im Thema:
Erster Beitrag:
vor 2 Jahren
Letzter Beitrag:
vor 6 Monaten
Beteiligte Autoren:
David Egan, Fabrice Harari, DerekM-2, kingdr

Slow Running SQL Query (HFSQL)

Startbeitrag von David Egan am 01.08.2016 23:28

We have a SQL query which is built up from user entered selections but is essentially as follows:
SELECT h.RegisterId, h.ShiftNum,h.userid,h.Date as ShiftDate,
sum(LineValue) As Value,
SUM(d.DiscountValue) AS DiscountValue
FROM saldet d
INNER JOIN salhead h ON (d.salhead_linkid = h.sysid)
h.branchId = 'DE'
AND h.RegisterID >= '01'
AND h.RegisterId


Hi David,

I am wondering if the problem does not come from the group by clause.

Could you try to add BranchID as the first 'step' of your group by to see if it THEN uses a brnachID+RegisterdID key?

Best regards

von Fabrice Harari - am 02.08.2016 14:15
Hi Fabrice
Just tried that and no difference I'm afraid. I'm wondering if it has something to do with the fact that I have several indexes that use RegisterId & it can't work out which one is best. Might be something I need to send to PCS for explanation.

As most customers either run the report for all registers or just for one I have sort of worked around it by changing the parameters; it'll still be painfully slow though if they do select a specific range.



von David Egan - am 03.08.2016 03:36

Did you index alhead_linkid and sysid for a quick inner join
or they go for sequential inner join for searching that could
be cause to slow it down, I guess?



von kingdr - am 03.08.2016 03:42
Hi King
Yes, they're indexed and on queries that don't have a range of register ids, we get pretty well instant response. It's all very strange.


von David Egan - am 03.08.2016 03:53
I was just playing around on another very similar query which runs OK and noticed that the only substantial difference is that it has a line and h.status = '' whereas this problem query has and h.status not in ('H','V') (Sorry, I didn't think this had any bearing on it so didn't include that line in my original post). Changing that line, it then uses an index and time goes from almost 1m 30s to less than 2 secs.

Testing a bit more h.status in ('H',V') runs fast but making h.status 'V' slows the whole thing down once again so it appears to me as if all the indexes are ignored the moment there is a NOT component. So the index selection is not particularly smart but I can work around it in this case as I am in control of what gets written to the status field. However I can see situations where that would not be possible so if anyone has any suggestions on how to speed up a negative search I would love to hear them.


von David Egan - am 03.08.2016 05:29
Hi David,

the NOT case makes sense to me...

When you do Var=Value, using an index is easy, because you are looking for ONE value, but when you are doing Var = NOT(Value), then you are asking for ALL the other values but that one, and using an index will not help.

The system has not way of knowing that all the other values is only one value and which one, it has to go through all the lines...

Best regards

von Fabrice Harari - am 03.08.2016 12:54
Hi Fabrice
Yeah, it sort of makes sense to me except that all the selection clauses are AND so I would have thought it would be smart enough to filter it firstly on the most logical index and then use the NOT within that.


von David Egan - am 03.08.2016 18:54
Hi David,

I totally agree on that part...

I wrote a sql "like" engine a few years back (in windev 5.5), and that was the first thing I was doing: identifying the best index for quick access... It should be able to do the same...

We'll see what the support says...

Best regards

von Fabrice Harari - am 04.08.2016 13:05
Just adding a note here in case it is useful to someone, sometime who has an issue with complex HFSQL queries.

I had a "very complex" query. Dozen tables plus aliases, inner, left and right joins, and sub queries. 400,000 records in the database

Query was required to be "real time" but initial HFSQL tests were 2 min 40 sec ++. Everything was indexed, and after a lot of "tweaking" got it down to 1 min 50 sec. NBG.

Copied everything for this query to PostgreSQL ... query ran "out of the box" - 500 milliseconds.

I use HFSQL for everything else - no complaints - C/S / Web - 500++ database tables.

This (now PostgreSQL) query is pretty much static/reference data - so for me - no problems and great solution integrating PostgreSQL with my HFSQL application using the native driver. HFSQL does a great job for day-to-day OLTP - but obviously there are some limits. HFSQL does not need to compete with all database engines. For me, the free postgres native driver provided was enough to overcome this (rare) HFSQL limitation.


von DerekM-2 - am 13.02.2018 12:06
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.