|
| Slow Running SQL Query (HFSQL) |
| Iniciado por guest, 02,ago. 2016 01:28 - 9 respuestas |
| |
| | | |
|
| |
| Publicado el 02,agosto 2016 - 01:28 |
Hi
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) WHERE h.branchId = 'DE' AND h.RegisterID >= '01' AND h.RegisterId <= '01' GROUP BY RegisterId, ShiftNum,ShiftDate,userid The query works fine if we have RegisterId = '01' but as soon as we try a range of registers (either as above or using BETWEEN) it just dies. Looking at the Explain feature in HFSQL Control Centre I can see that it uses an index when RegisterId='01' but not for a range of registers. We have some other selection options which allow for selection of range of dates and shift numbers & these work fine, an index is always selected, unless we include a range of registers.
We have a number of indexes set on Salhead file, the pertinent ones being:
BranchId, RegisterId, TranType, Reference
BranchId, RegisterId, ShiftNum, Date, Reference
BranchId, Date, RegisterId
The question is, why doesn't it use one of these indexes & is there a way I can force it to do so?
Thanks
David |
| |
| |
| | | |
|
| | |
| |
| Publicado el 02,agosto 2016 - 16:15 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 03,agosto 2016 - 05:36 |
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.
Cheers
David |
| |
| |
| | | |
|
| | |
| |
| Publicado el 03,agosto 2016 - 05:42 |
Hi
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?
HTH
King |
| |
| |
| | | |
|
| | |
| |
| Publicado el 03,agosto 2016 - 05:53 |
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.
David |
| |
| |
| | | |
|
| | |
| |
| Publicado el 03,agosto 2016 - 07:29 |
Hi
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.
David |
| |
| |
| | | |
|
| | |
| |
| Publicado el 03,agosto 2016 - 14:54 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 03,agosto 2016 - 20: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.
David |
| |
| |
| | | |
|
| | |
| |
| Publicado el 04,agosto 2016 - 15:05 |
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 |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
68 mensajes |
|
| Publicado el 05,agosto 2016 - 03:23 |
Hi David,
There are a LOT of ways to break up your query to squeeze a little more performance. I don't know if you are using a single query or if you are creating the query dynamically.
First - You might want to add an index for just BranchId alone since none of your where elements contain all of the items in your existing indexes.
Remember indexes are used for = comparisons so when you throw an inequality in the where clause the index can't be used. Try to use = as much as possible. Using Between x and y is supposed to set a beginning and end but doesn't as it defers processing any other comparison in the where. Still, if you're using ranges it is the way to go rather than >=/<=.
The rule of thumb is to create an index with the fields that are found by = rather than range.
Group By doesn't depend on indexes. Order By can
.
When you use >= and <= the parser converts that to 4 separate comparisons. 2 ='s a < and a > then strings them out with ands.
If you are generating the query where clause on the fly, you can code for the common situation where the from and through are the same.
So, ... h.RegisterID >= '01' AND h.RegisterId <= '01' ..., turns into ... h.RegisterId='01' ... which would be quite fast.
------------------
If you use the RegisterID a lot AND if it isn't always in a range, like Registers 01,05,11 and 17 (I'm assuming cash registers) then you might want to code the query into single selects for each register and the union them together. - This works well on large datasets. On smaller ones, you could just use where RegisterId IN (01,05,11,17)
Anyway, if your range really slows things down, the Union goes like this -
SELECT RegisterId, ShiftNum, userid, Date as ShiftDate, sum(LineValue) As Value, SUM(DiscountValue) AS DiscountValue
FROM (
SELECT h.RegisterId, h.ShiftNum,h.userid,h.Date as ShiftDate, LineValue) As Value, d.DiscountValue AS DiscountValue
FROM saldet d INNER JOIN salhead h ON (d.salhead_linkid = h.sysid)
WHERE h.branchId = 'DE' AND h.RegisterID = '01'
UNION
SELECT h.RegisterId, h.ShiftNum,h.userid,h.Date as ShiftDate, LineValue) As Value, d.DiscountValue AS DiscountValue
FROM saldet d INNER JOIN salhead h ON (d.salhead_linkid = h.sysid)
WHERE h.branchId = 'DE' AND h.RegisterID = '05'
UNION
SELECT h.RegisterId, h.ShiftNum,h.userid,h.Date as ShiftDate, LineValue) As Value, d.DiscountValue AS DiscountValue
FROM saldet d INNER JOIN salhead h ON (d.salhead_linkid = h.sysid)
WHERE h.branchId = 'DE' AND h.RegisterID = '11'
UNION
SELECT h.RegisterId, h.ShiftNum,h.userid,h.Date as ShiftDate, LineValue) As Value, d.DiscountValue AS DiscountValue
FROM saldet d INNER JOIN salhead h ON (d.salhead_linkid = h.sysid)
WHERE h.branchId = 'DE' AND h.RegisterID = '17'
)
GROUP BY RegisterId, ShiftNum, ShiftDate, userid
---------------------
Using IN (x,y,z) causes the parser to create a separate OR statement for each element of the IN(Group) not bad for small groups
-------------------
One more -
If you want to do a negative search such as Where h.Status<> 15 - this drives the index search crazy since you are looking for what it doesn't have - nobody likes that. Try this - use Not Exist (Select 1 from...) - In this form you are giving the index something singular to find (very fast) but then saying that you don't want it. NOTE The Select 1 in the sub-query. It makes sure that we don't actually return any data from the sub query which can slow things down.
SELECT h.RegisterId, h.ShiftNum,h.userid,h.Date as ShiftDate, LineValue) As Value, SUM(d.DiscountValue) AS DiscountValue
FROM saldet d INNER JOIN salhead h ON (d.salhead_linkid = h.sysid)
WHERE h.branchId = 'DE' AND NOT EXISTS(Select 1 FROM Salhead AS H1 where H1.sysid = h.sysid and h.status='V')
Note: I'm taking this from MSSQL experience. I'm not entirely sure of the performance boost using HFSQL but the concepts are the same.
HTH,
Chris C
--
Sometimes waiting is the best way forward... |
| |
| |
| | | |
|
| | | | |
| | |
|