Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
SPEED OF JOIN IN SQL |
Thu, Feb 24 2022 10:40 AM | Permanent Link |
Accowin LBRP | Hello,
I notice a big difference in speed when I use these 2 statements in the join. Both tables are indexed on the 4 fields used in the join SELECT h.DagbSubType, IF(h.DagbSubType=2, 'KN','FA') as fk ,b.Land, h.Intracode, b.Procent, SUM(-h.BedragEur) As bedragEUR, Round(SUM(l.BTwBedrag/H.koers),2) AS L_BtwBedrag, h.BtwRooster, l.BtwKode, ROUND(SUM(-h.BtwBedrag/h.koers),2) AS H_BtwBedrag FROM history h LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line --LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line) LEFT JOIN BtwCodes b on l.BtwKode = b.Referte WHERE h.Intracode > '' Group by h.DagbSubType, b.Land, h.Intracode, b.Procent, h.BtwRooster, l.BtwKode This query is very slow: LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line This is fast LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line) Why is the second JOIN faster? Should I always use the () if possible? |
Fri, Feb 25 2022 5:09 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Accowin
<< This query is very slow: LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line This is fast LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line) Why is the second JOIN faster? Should I always use the () if possible? >> In the first JOIN the engine will try to use four indexes separated for each condition and the second one will use just one compound index for table h and one for table I. When you use () you are telling to the engine to use (if exists) an index with fields in the condition and therefore it will speed up the things a lot. Eduardo |
Mon, Feb 28 2022 9:20 AM | Permanent Link |
Accowin LBRP | <<
This query is very slow: LEFT JOIN Verlines l on h.Dagboek=l.Dagboek and h.DocNr = l.Docnr and h.jaar=l.jaar and h.Lijn=l.line This is fast LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar, h.Lijn) = (l.Dagboek, l.Docnr, l.jaar, l.line) >> In the first JOIN the engine will try to use four indexes separated for each condition and the second one will use just one compound index for table h and one for table I. When you use () you are telling to the engine to use (if exists) an index with fields in the condition and therefore it will speed up the things a lot. Thanks Eduardo, that is clear. So if there is an index on the corresponding fields, I shoud use as much as possible the compound index. If in The example above, the field "line" should not be indexed, than I would write: LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar) = (l.Dagboek, l.Docnr, l.jaar) and h.Lijn=l.line |
Tue, Mar 1 2022 5:20 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Accowin wrote:
<< So if there is an index on the corresponding fields, I shoud use as much as possible the compound index. If in The example above, the field "line" should not be indexed, than I would write: LEFT JOIN Verlines l on (h.Dagboek, h.DocNr, h.jaar) = (l.Dagboek, l.Docnr, l.jaar) and h.Lijn=l.line >> Yes, you have got the idea. To speed up the query execution time, the engine needs indexes for each condition and be sure that using the idea behing "row value" it is faster than using individual indexes even for each field involved in the conditions. Eduardo |
Tue, Mar 1 2022 9:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Accowin
Whilst I can agree with Jose I had a different interpretation of what you wrote so I'd like to ask a question to clarify things for me. You wrote "Both tables are indexed on the 4 fields used in the join" which I interpreted as there being four separate indices. Jose seems to have interprets it as there being a single compound index. Which is correct? Roy Lambert |
Wed, Jul 13 2022 10:19 AM | Permanent Link |
Accowin LBRP | Roy,
We have 1 Index on 4 fields. |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |