Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 15 of 15 total |
Speedup query |
Tue, Mar 10 2020 9:38 AM | Permanent Link |
Mirco Malagoli | No sorry,
the difference is that the DB that i have posted is not complete with data from tipo 5 and tipo 6. For test just change with tipo = 1 Roy Lambert wrote: Mirco I've just tried here and with I6 added in I still get c10 seconds. I suspect that you've somehow lost the indices I said to add in. However, I suspect that you need to go back to the beginning and see why you're doing this and if there's a better way. Roy Lambert |
Tue, Mar 10 2020 9:43 AM | Permanent Link |
Mirco Malagoli | New data
Attachments: XRacerRace sologiri_interm Backup On 2020-03-10.EDBBkp |
Wed, Mar 11 2020 8:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mirco
Downloaded it, restored the database and ran the query without any changes. Its taking ages. When it finally finishes I start looking for the cause. Roy Lambert |
Wed, Mar 11 2020 10:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mirco
1. I think you need to open a support ticket with Tim about this - I just can't make out what's happening, apart from the fact that it seems its something to do with the table interm. The key factor (I think) is Total rows visited: 3606 for i1 Total rows visited: 6459 for i1 and i2 Total rows visited: 60033 for i1..i3 Total rows visited: 1114013 for i1..i4 I cancelled t i1..i5, and when I did it was Total rows visited: 23480459 but for the temporary table (as below) for i1..i6 its Total rows visited: 3471 I have absolutely no idea what's going on which is why I suggest opening a support ticket. 2. whilst not ideal creating a temporary table give a solution DROP TABLE Trial! <<<<< only needed if it exists CREATE TEMPORARY TABLE Trial AS SELECT DISTINCT idGiro,tipo,tempo from interm WHERE idGiro in (SELECT idx from giri) GROUP BY idGiro,tipo WITH DATA! CREATE INDEX idx1 ON Trial (idgiro)! CREATE INDEX idx2 ON Trial (tipo)! CREATE INDEX idx3 ON Trial (tempo)! This dropped the time taken to less than a second for the query with I1..I6 plus maybe 5 seconds for the creation of the temporary table. You could probably speed it up by using an in-memory database for the temporary table. Roy Lambert |
Thu, Mar 19 2020 2:04 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Mirco
I am guessing that the query relates to bicycle races (giro), tips (tipo) and times (tempo). It appears that the tables 'giri' and 'interm' are essentially the same. It you redesigned the DB so that it is is normalised, you will find that you will get the results you are wanting (or the results that I think you are wanting) much more elegantly. 'giri' table: Combine 'giri' and 'interm' tables with a flag to indicate interim results. Key 'giro'. Remove all data that relates only to 'tips'. 'tip' table: Table containing only tip information. Key 'giro', 'tipo'. Remove all data that relates only to 'giri' The SELECT statement is invalid as it contains columns that are unrelated to the GROUP clause. Richard |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |