pcsoft.us.windevCopyright 2026, PC SOFT5 Aug 2016 03:23:23 Z2 Aug 2016 01:28:00 ZHi 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 David30WEBDEVen_UShttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql/read.awpmoderateur@pcsoft.fr (El moderador)webmaster@pcsoft.fr (El webmaster)CCordespcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58971/read.awp5 Aug 2016 03:23:23 ZHi 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 …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58971/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58971/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58965/read.awp4 Aug 2016 15:05:00 ZHi 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 f…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58965/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58965/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58957/read.awp3 Aug 2016 20:54:00 ZHi 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…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58957/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58957/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58956/read.awp3 Aug 2016 14:54:00 ZHi David, the NOT case makes sense to me... When you do Var=Value, using an index is easy, because you are looking for ONE…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58956/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58956/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58943/read.awp3 Aug 2016 07:29:00 ZHi I was just playing around on another very similar query which runs OK and noticed that the only substantial difference is th…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58943/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58943/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58941/read.awp3 Aug 2016 05:53:00 ZHi 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 …https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58941/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58941/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58944/read.awp3 Aug 2016 05:42:00 ZHi Did you index alhead_linkid and sysid for a quick inner join or they go for sequential inner join for searching that coul…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58944/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58944/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58939/read.awp3 Aug 2016 05:36:00 ZHi 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 seve…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58939/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58939/read.awpSlow Running SQL Query (HFSQL)guestpcsoft.us.windevhttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58933/read.awp2 Aug 2016 16:15:00 ZHi David, I am wondering if the problem does not come from the group by clause. Could you try to add BranchID as the first…https://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58933/read.awphttps://forum.pcsoft.fr/es-ES/pcsoft.us.windev/58922-slow-running-sql-query-hfsql-58933/read.awpSlow Running SQL Query (HFSQL)