Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 12 total |
Slow/funny query progress |
Fri, Apr 27 2007 1:48 AM | Permanent Link |
Kerry Neighbour | I am using Delphi 7 with DBISAM c/s 4.25 build 2. I am running a simply query
on a table with some 40,000 records in it. There are a couple of inner and outer joins as well to some very small tables. I have added a progress bar to the QueryProgress event, and it works ok. And the query works ok. The thing is, there is a big time delay between when I start the query, and when the progress indicator starts to move. It takes 10 seconds between the Query.Open and the bar gettiing to the 5% mark (ie the first movement). It then takes 20 seconds to complete the rest of the query - ie the other 95%. Since I am trying to get this query as fast as I can, I am wondering what I can do to speed up the fiirst 5% that takes 30% of the time! my code is something like this Query.close Query.SQL.Assign(lstSQL) progressbar.position:=0; Query.prepare progressbar.position:=0; Query.open ....do more things I have run the query through DBSYS and I have optimised the Plan ok. The reason this 'problem' came to light is that when the query was not so optimised, it took AGES (10 minutes or more ) to get the progress bar to move - to the extent that you thought that the program had locked up. Mind you, I do not think that 33 seconds to load a simple query on a 40,000 record table is very good either! And this is generally with about 20 records returned. Still, I am working on it. |
Fri, Apr 27 2007 3:39 AM | Permanent Link |
"Frans van Daalen" | >I am using Delphi 7 with DBISAM c/s 4.25 build 2. I am running a simply
>query on a table with some 40,000 records in it. There are a couple of >inner and outer joins as well to some very small tables. > > I have added a progress bar to the QueryProgress event, and it works ok. > And the query works ok. > > The thing is, there is a big time delay between when I start the query, > and when the progress indicator starts to move. > > It takes 10 seconds between the Query.Open and the bar gettiing to the 5% > mark (ie the first movement). It then takes 20 seconds to complete the > rest of the query - ie the other 95%. > I have run the query through DBSYS and I have optimised the Plan ok. What is the time reported by dbsys for this query? |
Fri, Apr 27 2007 7:39 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Kerry,
<< Since I am trying to get this query as fast as I can, I am wondering what I can do to speed up the fiirst 5% that takes 30% of the time! >> You can start by posting the query execution plan here. -- Tim Young Elevate Software www.elevatesoft.com |
Sun, Apr 29 2007 8:38 PM | Permanent Link |
Kerry Neighbour | >
> You can start by posting the query execution plan here. > Ok - here is the plan from DBSYS. Interestingly enough, I found that DBSYS has the same problem. When I run this query, nothing happens for a long time, then the program bar starts to move. In one test I timed, it took 3 seconds for the bar to start to move, and 11 seconds for the whole query. With slower queries, it seems to keep the same sort of ratio. ie if a query takes 8 minutes, you can be sitting there for 2 minutes with nothing happening at all. I just ran this query on the same main table, but with a larger File table (500,000). It took 4 minutes for the progress bar to start, and 5 minutes for the full query to finish. This is an extreme example of what I am seeing. And another thing, which is probably Windows. The more I run a query, the faster it gets! No doubt something is being cached somewhere. This query that I show below took 76 seconds the first time through. after a few runs it is down to 11 seconds! While this sounds good, it is not. I am doing timing tests on remote/local connections, and I am finding that it is hard to do as caching is taking out the accuracy of the tests. Caching is not likely to happen in real use, as few users run the same query over and over (as I am doing). Anyway, this query is very slow, even though everyting seems optimised. It is only a comparitively small table (35,000 records), so it should not be this slow. It return zero records, which is correct. Any hints on making the query faster will be appreciated! ================================================================================ SQL statement (Executed with 4.25 Build 2) ================================================================================ SELECT box.ID, box.BoxNumber, box.RetainYears, box.RetainMonths, box.OffsiteStorageProviderID, box.DisposalReviewDate, box.MarkedForDisposal, box.OfficeID, box.OffsiteStorageProviderID, box.BoxSizeID, box.StatusID, box.CreatedByID, box.Created, box.permanent, box.MarkedForDisposal, boxstorage.storagelocation FROM box INNER JOIN userauthority_offices ON (box.OfficeID = userauthority_offices.OfficeID) LEFT OUTER JOIN boxstorage ON (box.officeID = boxstorage.OfficeID) LEFT OUTER JOIN currentholds_boxes ON (box.ID = currentholds_boxes.BoxID) LEFT OUTER JOIN file ON (box.ID = file.BoxID) LEFT OUTER JOIN currentholds_files ON (file.ID = currentholds_files.FileID) WHERE (userauthority_offices.UserID = 3) AND (file.BoxID IS NULL) ORDER BY box.ID TOP 1000 Tables Involved --------------- box (box) table opened shared, has 34929 rows userauthority_offices (userauthority_offices) table opened shared, has 101 rows boxstorage (boxstorage) table opened shared, has 6 rows currentholds_boxes (currentholds_boxes) table opened shared, has 1 rows file (file) table opened shared, has 107156 rows currentholds_files (currentholds_files) table opened shared, has 1 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the primary index for the table box WHERE Clause Execution ---------------------- The expression: userauthority_offices.UserID = 3 is OPTIMIZED, covers 10 rows or index keys, costs 126 bytes, and will be applied to the userauthority_offices table (userauthority_offices) before any joins The expression: file.BoxID IS NULL will be applied to each candidate row in the result set as the result set is generated due to the file table (file) being the target of an OUTER join Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: box.OfficeID = userauthority_offices.OfficeID is OPTIMIZED The expression: box.officeID = boxstorage.OfficeID is OPTIMIZED The expression: box.ID = currentholds_boxes.BoxID is OPTIMIZED The expression: box.ID = file.BoxID is OPTIMIZED The expression: file.ID = currentholds_files.FileID is OPTIMIZED ================================================================================ >>>>> 0 rows affected in 76.234 seconds ================================================================================ |
Mon, Apr 30 2007 3:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Kerry,
<< Ok - here is the plan from DBSYS. Interestingly enough, I found that DBSYS has the same problem. When I run this query, nothing happens for a long time, then the program bar starts to move. In one test I timed, it took 3 seconds for the bar to start to move, and 11 seconds for the whole query. With slower queries, it seems to keep the same sort of ratio. ie if a query takes 8 minutes, you can be sitting there for 2 minutes with nothing happening at all. >> The progress bar updating is based upon the TDBISAMSession.ProgressSteps property, so it is relative to the amount of work to do, not a specific time interval: http://www.elevatesoft.com/dbisam4d7_tdbisamsession_progresssteps.htm ElevateDB improves upon this by using a time interval instead. << And another thing, which is probably Windows. The more I run a query, the faster it gets! No doubt something is being cached somewhere. This query that I show below took 76 seconds the first time through. after a few runs it is down to 11 seconds! While this sounds good, it is not. I am doing timing tests on remote/local connections, and I am finding that it is hard to do as caching is taking out the accuracy of the tests. Caching is not likely to happen in real use, as few users run the same query over and over (as I am doing). >> Sure it will. The caching is done by the OS on the tables themselves, so unless you're only using these tables in this one query, the caching will most certainly speed things up on any subsequent table reads. << Anyway, this query is very slow, even though everyting seems optimised. It is only a comparitively small table (35,000 records), so it should not be this slow. It return zero records, which is correct. >> Is there a way you can send me your tables, or that I can download them ? The userauthority_offices join should effectively filter the number of rows that need to be joined down to 10 rows, but for some reason it doesn't appear to be doing so. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Apr 30 2007 9:47 PM | Permanent Link |
Kerry Neighbour | > ElevateDB improves upon this by using a time interval instead. ElevateDB is looking better all the time! > Is there a way you can send me your tables, or that I can download > them ? The userauthority_offices join should effectively filter the > number of rows that need to be joined down to 10 rows, but for some > reason it doesn't appear to be doing so. That is very kind of you. Because of the size of these things, I have uploaded the files to one of my websites. The links are http://www.docsminder.com/files/temp.rar (11 megs) http://www.docsminder.com/files/temp500k.rar (54 megs) The first file is all the basic tables, plus the query I run. This is probably the best system to get - it is about 100,000 records and 40,000 records in the main tables. Good enough for testing on. The second file contains a bigger set of secondary files (500,000). This data is the killer - it basically stops the system. It is where I am getting my RemoteSessionTimeout problems, for example. Anyway, if you can see a faster way to run the query, I would appreciate it. |
Tue, May 1 2007 11:51 AM | Permanent Link |
Bruno Krayenbuhl | Try this, I cannot test with real data but sometimes a multi-step script helps taking time
down. Bruno Krayenbuhl /* Exclude cases where file.BoxID is not NULL - STEP1 */ SELECT Box.ID, Box.OfficeID /* Pick it up for STEP2 */ INTO "/MEMORY/STEP1" LEFT OUTER JOIN file ON (box.ID = file.BoxID) WHERE file.BoxID is NULL; /* Speed up "/MEMORY/STEP2" creation */ CREATE UNIQUE INDEX IxOfficeID ON "/MEMORY/STEP1" (OfficeID); /* Grab only the Boxes that have S1.OfficeID.UserID=3 - STEP2 */ SELECT Box.ID, Box.OfficeID /* Pick it up for final join */ INTO "/MEMORY/STEP2" FROM userauthority_offices INNER JOIN "/MEMORY/STEP1" S1 ON (userauthority_offices.OfficeID=S1.OfficeID) WHERE (userauthority_offices.UserID = 3); /* Not needed any more */ DROP INDEX "/MEMORY/STEP1".IxOfficeID; /* Speed up final SELECT JOIN */ CREATE UNIQUE INDEX IxBoxId ON "/MEMORY/STEP2" (ID); /* Finally do the OUTER JOIN */ SELECT box.ID, box.BoxNumber, box.RetainYears, box.RetainMonths, box.OffsiteStorageProviderID, box.DisposalReviewDate, box.MarkedForDisposal, box.OfficeID, box.OffsiteStorageProviderID, box.BoxSizeID, box.StatusID, box.CreatedByID, box.Created, box.permanent, box.MarkedForDisposal, boxstorage.storagelocation INTO "/MEMORY/Result" /* Or whatever disk base workfile */ FROM "/MEMORY/STEP2" S2 INNER JOIN box ON (S2.Id=box.Id) LEFT OUTER JOIN boxstorage ON (S1.officeID = boxstorage.OfficeID) LEFT OUTER JOIN currentholds_boxes ON (box.ID = currentholds_boxes.BoxID) LEFT OUTER JOIN file ON (box.ID = file.BoxID) LEFT OUTER JOIN currentholds_files ON (file.ID = currentholds_files.FileID) ORDER BY box.ID TOP 1000; /* Not needed any more */ DROP INDEX INDEX IxBoxId ON "/MEMORY/STEP2".IxBoxId (ID); /* Not needed any more */ DROP TABLE "/MEMORY/STEP2"; /* Show the Result table in DBSys */ SELECT * FROM Result; /* After use of table Result -> DROP IT */ |
Tue, May 1 2007 11:14 PM | Permanent Link |
Kerry Neighbour | Hello Bruno,
> Try this, I cannot test with real data but sometimes a multi-step > script helps taking time down. Are these queries meant to run in DBSYS? If so, it does not seem to like the MEMORY table stuff. I get a 11949 error - Parsing error trying to work out things like "MEMORY/STEP1". Pardon my ignorance! |
Wed, May 2 2007 2:46 AM | Permanent Link |
Bruno Krayenbuhl | As I wrote I cannopt test on your data and there is clearly a mistake in
/* Speed up "/MEMORY/STEP2" creation */ CREATE UNIQUE INDEX IxOfficeID ON "/MEMORY/STEP1" (OfficeID); because it is quite likely that there will be more than one occurence of OfficeID in Step1. Search the PDF doc (Not the help file) for memory tables. The specs say "/Memory/... not "Memory/... Here after you find a real query script that works and can be also executed thru a DBISAMSql from a Delphi program. There is my Query followed by the Query plan generated by DBSys so you get an idea of what this Divide/Conquer strategy gives regarding timings on a table of 50'000 records. Bruno ==================== MY QUERY ====================================== /* Extraire les enregistrements collaborateurs m'intéressant Done here to be able to PATCH Collaborateurs.RecordId in the "/Memory/LJoin" that is the base table for the final Join. Collaborateurs's key is a composite of (NatObj and Col) but RecordId is UNIQUE */ select c.RecordId as ColRecId, c.NatObj, c.Col, c.Libelle into "/Memory/JCol" from Collaborateurs c where c.NatObj=2 and (c.Col='FM' or c.Col='FG') /* Same where clause as in the next Select */ group by ColRecId; /* Speed up "/Memory/LJoin" creation */ CREATE UNIQUE INDEX IxCol ON "/Memory/JCol" (Col); select p.UId, p.DatePrest, jc.ColRecId, p.ColTravNatObj, p.ColTrav, p.Mandat into "/Memory/LJoin" from Prestations p inner join "/Memory/JCol" jc on (p.ColTrav=jc.Col) where p.ColTravNatObj=2 and (p.ColTrav='FM' or p.ColTrav='FG') order by p.DatePrest; /* Not needed any more */ DROP INDEX "/Memory/JCol".IxCol; /* Speed up final join */ CREATE UNIQUE INDEX IxRecId ON "/Memory/JCol" (ColRecId); /* Create a memory table holding only the mandat's concerned by the extraction from Prestions */ select LJ.Mandat into "/Memory/GroupMan" from "/Memory/LJoin" LJ group by LJ.Mandat; /* Pick up information Libelle from table Client */ select JM.Mandat, c.Libelle into "/Memory/JClient" from Clients c inner join "/Memory/GroupMan" JM on (c.Mandat=JM.Mandat); DROP TABLE "/Memory/GroupMan"; /* Not used any more */ /* Speed up the final JOIN time (Time divide by factor of 5 or more */ CREATE UNIQUE INDEX IxMandat ON "/Memory/JClient" (Mandat); select LJ.DatePrest, LJ.UId, LJ.ColTravNatObj, LJ.ColTrav, JC.Libelle, LJ.Mandat, JM.Libelle INTO "/Memory/Result" /* Or where I would like it to go */ FROM "/Memory/LJoin" LJ LEFT OUTER JOIN "/Memory/JCol" JC on (LJ.ColRecId=JC.ColRecId) LEFT OUTER JOIN "/Memory/JClient" JM on (LJ.Mandat=JM.Mandat) WHERE JM.Libelle IS NULL; /* Clean up accessory tables */ DROP TABLE "/Memory/JCol"; DROP TABLE "/Memory/JClient"; SELECT * FROM "/Memory/Result"; /* This is what I want to see */ ==================== QUERY PLAN GENERATED BY DBSYS ===================== ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ /**/ select c.RecordId as ColRecId, c.NatObj, c.Col, c.Libelle into "/Memory/JCol" from Collaborateurs c where c.NatObj=2 and (c.Col='FM' or c.Col='FG') /**/ group by ColRecId /**/ Tables Involved --------------- Collaborateurs (c) table opened shared, has 36 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: ColRecId Result set will be ordered by the temporary index created for the grouping WHERE Clause Execution ---------------------- The expression: c.NatObj = 2 and c.Col = 'FM' or c.Col = 'FG' is PARTIALLY-OPTIMIZED, covers 25 rows or index keys, costs 5292 bytes, and will be applied to the Collaborateurs table (c) before any joins ================================================================================ >>>>> 2 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ CREATE UNIQUE INDEX IxCol ON "/Memory/JCol" (Col) ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ select p.UId, p.DatePrest, jc.ColRecId, p.ColTravNatObj, p.ColTrav, p.Mandat into "/Memory/LJoin" from Prestations p inner join "/Memory/JCol" jc on (p.ColTrav=jc.Col) where p.ColTravNatObj=2 and (p.ColTrav='FM' or p.ColTrav='FG') order by p.DatePrest /**/ Tables Involved --------------- Prestations (p) table opened shared, has 52110 rows JCol (jc) table opened shared, has 2 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Result set will be ordered by the following column(s) using a case-sensitive temporary index: DatePrest ASC WHERE Clause Execution ---------------------- The expression: p.ColTravNatObj = 2 and p.ColTrav = 'FM' or p.ColTrav = 'FG' is PARTIALLY-OPTIMIZED, covers 52110 rows or index keys, costs 11412090 bytes, and will be applied to the Prestations table (p) before any joins Join Ordering ------------- The driver table is the Prestations table (p) The Prestations table (p) is joined to the JCol table (jc) with the INNER JOIN expression: p.ColTrav = jc.Col Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: p.ColTrav = jc.Col is OPTIMIZED ================================================================================ >>>>> 19650 rows affected in 1.094 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ DROP INDEX "/Memory/JCol".IxCol /**/ ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ CREATE UNIQUE INDEX IxRecId ON "/Memory/JCol" (ColRecId) /**/ ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ select LJ.Mandat into "/Memory/GroupMan" from "/Memory/LJoin" LJ group by LJ.Mandat /**/ Tables Involved --------------- LJoin (LJ) table opened shared, has 19650 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: Mandat Result set will be ordered by the temporary index created for the grouping ================================================================================ >>>>> 571 rows affected in 0.203 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ select JM.Mandat, c.Libelle into "/Memory/JClient" from Clients c inner join "/Memory/GroupMan" JM on (c.Mandat=JM.Mandat) Tables Involved --------------- Clients (c) table opened shared, has 666 rows GroupMan (JM) table opened shared, has 571 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Join Ordering ------------- The driver table is the Clients table (c) The Clients table (c) is joined to the GroupMan table (JM) with the INNER JOIN expression: c.Mandat = JM.Mandat Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: c.Mandat = JM.Mandat is OPTIMIZED ================================================================================ >>>>> 541 rows affected in 0.031 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ DROP TABLE "/Memory/GroupMan" /**/ /**/ ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ CREATE UNIQUE INDEX IxMandat ON "/Memory/JClient" (Mandat) ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ select LJ.DatePrest, LJ.UId, LJ.ColTravNatObj, LJ.ColTrav, JC.Libelle, LJ.Mandat, JM.Libelle INTO "/Memory/Result" /**/ FROM "/Memory/LJoin" LJ LEFT OUTER JOIN "/Memory/JCol" JC on (LJ.ColRecId=JC.ColRecId) LEFT OUTER JOIN "/Memory/JClient" JM on (LJ.Mandat=JM.Mandat) WHERE JM.Libelle IS NULL /**/ Tables Involved --------------- LJoin (LJ) table opened shared, has 19650 rows JCol (JC) table opened shared, has 2 rows JClient (JM) table opened shared, has 541 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows WHERE Clause Execution ---------------------- Join Ordering ------------- The driver table is the LJoin table (LJ) The LJoin table (LJ) is joined to the JCol table (JC) with the LEFT OUTER JOIN expression: LJ.ColRecId = JC.ColRecId The LJoin table (LJ) is joined to the JClient table (JM) with the LEFT OUTER JOIN expression: LJ.Mandat = JM.Mandat Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further The expression: JM.Libelle IS NULL will be applied to each candidate row in the result set as the result set is generated due to the JClient table (JM) being the target of an OUTER join Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: LJ.ColRecId = JC.ColRecId is OPTIMIZED The expression: LJ.Mandat = JM.Mandat is OPTIMIZED ================================================================================ >>>>> 305 rows affected in 0.438 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ DROP TABLE "/Memory/JCol" ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ DROP TABLE "/Memory/JClient" ================================================================================ >>>>> 0 rows affected in 0 seconds ================================================================================ ================================================================================ SQL statement (Executed with 4.25 Build 3) ================================================================================ SELECT * FROM "/Memory/Result" /**/ Tables Involved --------------- Result (Result) table opened shared, has 305 rows Result Set Generation --------------------- Result set will be live Result set will consist of one or more rows Result set will be ordered by the primary index for the table Result ================================================================================ >>>>> 305 rows affected in 0 seconds ================================================================================ |
Wed, May 2 2007 2:56 AM | Permanent Link |
Kerry Neighbour | Thanks for the tips. I was starting to be of the opinion that first you had
to do a CREATE TABLE on the memory tables? But your sample scripts do not do that... > As I wrote I cannopt test on your data and there is clearly a mistake > in > /* Speed up "/MEMORY/STEP2" creation */ > CREATE UNIQUE INDEX IxOfficeID ON "/MEMORY/STEP1" > (OfficeID); > because it is quite likely that there will be more than one occurence > of OfficeID in Step1. > Search the PDF doc (Not the help file) for memory tables. The specs > say "/Memory/... not "Memory/... > |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |