Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Speed on sql |
Sat, May 26 2007 7:37 AM | Permanent Link |
"Sorin" | Hello
I want to move a BDE application to EDB I migrate the database to dbisam and from dbisam to edb I try to run an sql query on the 3 databases using the relevant additional software's. (database explorer, database system utility and elevatedb manager) The bde return a result after less then 35 seconds, the dbisam after 811 seconds and edb after 348 seconds. The sql select 35 fields from 15 joined tables. Do I miss something or is this normal? sorin |
Sat, May 26 2007 8:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Sorin
Do you have indices defined for the columns that perform the joins? To help us help you can you post the query plans from DBISAM and ElevateDB Roy Lambert |
Sat, May 26 2007 11:47 AM | Permanent Link |
"Sorin" | Roy
I add few missing indexes but it doesn't change the speed I add the generated plans DBISAM plan ================================================================================ SQL statement (Executed with 4.25 Build 4) ================================================================================ SELECT Anm.Pail, Anm.Tzip, Hsn.Tavit, Anm.AnimalId, Anm.Name, tGz.Geza, Anm.Sex, Bhl.StatusPayId, tZv.Zeva, Anm.Nolad, Anm.Ikur, tGz.Danger, Bhl.TZeut, Bhl.Shem, tRh.Rehov, Bhl.Number, tIs.CodIsuv, Bhl.TeivatDoar, Bhl.Mikud, Bhl.Tel, Bhl.Fax, Bhl.Cel, Bhl.EMail, tCs.Cause, Aar.Moed MoedArhiv, Hsn.Moed, tRf.LicenceNo, Hsn.Atzva, Esg.Moed MoedEsg, tTh.Tahana, tSb.SibaEsg, tTh.Tel TelTahana, Ebt.MoedT, tDb.DargaByte, Esg.MoedEnd, Esg.TipulEsgId, Ept.MoedBdk FROM VAnimals Anm LEFT JOIN VTblGeza tGz ON (Anm.GezaId = tGz.Id) LEFT JOIN VTblZeva tZv ON (Anm.ZevaId = tZv.Id) LEFT JOIN VHisun Hsn ON (Hsn.AnimalId = Anm.Id) LEFT JOIN VTblRofe tRf ON (Hsn.RofeId = tRf.Id) LEFT JOIN VBahalim Bhl ON (Anm.BahalimId = Bhl.Id) LEFT JOIN VTblRehov tRh ON (Bhl.RehovId = tRh.Id) LEFT JOIN VTblIsuv tIs ON (Bhl.IsuvId = tIs.Id) LEFT JOIN VEsger Esg ON (Esg.AnimalId = Anm.Id) LEFT JOIN VTblSibaEsg tSb ON (Esg.SibaEsgId = tSb.Id) LEFT JOIN VTblTahana tTh ON (Esg.TahanaId = tTh.Id) LEFT JOIN VEsgerPtira EPt ON (Ept.EsgId = Esg.Id) LEFT JOIN VEsgerByte Ebt ON (Ebt.EsgId = Esg.Id) LEFT JOIN VTblDargaByte tDb ON (Ebt.DargaByteId = tDb.Id) LEFT JOIN VAnimalArchion Aar ON (Aar.AnimalId = Anm.Id) LEFT JOIN VTblArchionCauseAnml tCs ON (Aar.CauseId = tCs.Id) Tables Involved --------------- VAnimals (Anm) table opened shared, has 39784 rows VTblGeza (tGz) table opened shared, has 176 rows VTblZeva (tZv) table opened shared, has 39 rows VHisun (Hsn) table opened shared, has 37682 rows VTblRofe (tRf) table opened shared, has 145 rows VBahalim (Bhl) table opened shared, has 32058 rows VTblRehov (tRh) table opened shared, has 3254 rows VTblIsuv (tIs) table opened shared, has 16 rows VEsger (Esg) table opened shared, has 2582 rows VTblSibaEsg (tSb) table opened shared, has 10 rows VTblTahana (tTh) table opened shared, has 0 rows VEsgerPtira (EPt) table opened shared, has 782 rows VEsgerByte (Ebt) table opened shared, has 242 rows VTblDargaByte (tDb) table opened shared, has 3 rows VAnimalArchion (Aar) table opened shared, has 10204 rows VTblArchionCauseAnml (tCs) table opened shared, has 12 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 VAnimals table (Anm) The VAnimals table (Anm) is joined to the VTblGeza table (tGz) with the LEFT OUTER JOIN expression: Anm.GezaId = tGz.Id The VAnimals table (Anm) is joined to the VTblZeva table (tZv) with the LEFT OUTER JOIN expression: Anm.ZevaId = tZv.Id The VAnimals table (Anm) is joined to the VHisun table (Hsn) with the LEFT OUTER JOIN expression: Anm.Id = Hsn.AnimalId The VAnimals table (Anm) is joined to the VBahalim table (Bhl) with the LEFT OUTER JOIN expression: Anm.BahalimId = Bhl.Id The VAnimals table (Anm) is joined to the VEsger table (Esg) with the LEFT OUTER JOIN expression: Anm.Id = Esg.AnimalId The VAnimals table (Anm) is joined to the VAnimalArchion table (Aar) with the LEFT OUTER JOIN expression: Anm.Id = Aar.AnimalId The VHisun table (Hsn) is joined to the VTblRofe table (tRf) with the LEFT OUTER JOIN expression: Hsn.RofeId = tRf.Id The VBahalim table (Bhl) is joined to the VTblRehov table (tRh) with the LEFT OUTER JOIN expression: Bhl.RehovId = tRh.Id The VBahalim table (Bhl) is joined to the VTblIsuv table (tIs) with the LEFT OUTER JOIN expression: Bhl.IsuvId = tIs.Id The VEsger table (Esg) is joined to the VTblSibaEsg table (tSb) with the LEFT OUTER JOIN expression: Esg.SibaEsgId = tSb.Id The VEsger table (Esg) is joined to the VTblTahana table (tTh) with the LEFT OUTER JOIN expression: Esg.TahanaId = tTh.Id The VEsger table (Esg) is joined to the VEsgerPtira table (EPt) with the LEFT OUTER JOIN expression: Esg.Id = Ept.EsgId The VEsger table (Esg) is joined to the VEsgerByte table (Ebt) with the LEFT OUTER JOIN expression: Esg.Id = Ebt.EsgId The VEsgerByte table (Ebt) is joined to the VTblDargaByte table (tDb) with the LEFT OUTER JOIN expression: Ebt.DargaByteId = tDb.Id The VAnimalArchion table (Aar) is joined to the VTblArchionCauseAnml table (tCs) with the LEFT OUTER JOIN expression: Aar.CauseId = tCs.Id 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: Anm.GezaId = tGz.Id is OPTIMIZED The expression: Anm.ZevaId = tZv.Id is OPTIMIZED The expression: Anm.Id = Hsn.AnimalId is OPTIMIZED The expression: Hsn.RofeId = tRf.Id is OPTIMIZED The expression: Anm.BahalimId = Bhl.Id is OPTIMIZED The expression: Bhl.RehovId = tRh.Id is OPTIMIZED The expression: Bhl.IsuvId = tIs.Id is OPTIMIZED The expression: Anm.Id = Esg.AnimalId is UN-OPTIMIZED The expression: Esg.SibaEsgId = tSb.Id is OPTIMIZED The expression: Esg.TahanaId = tTh.Id is OPTIMIZED The expression: Esg.Id = Ept.EsgId is OPTIMIZED The expression: Esg.Id = Ebt.EsgId is OPTIMIZED The expression: Ebt.DargaByteId = tDb.Id is OPTIMIZED The expression: Anm.Id = Aar.AnimalId is OPTIMIZED The expression: Aar.CauseId = tCs.Id is OPTIMIZED ================================================================================ >>>>> 39854 rows affected in 738.422 seconds ================================================================================ ElavateDB plan: ================================================================================ SQL Query (Executed by ElevateDB 1.02 Build 1) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ SELECT ALL "Anm"."Pail" AS "Pail", "Anm"."Tzip" AS "Tzip", "Hsn"."Tavit" AS "Tavit", "Anm"."AnimalId" AS "AnimalId", "Anm"."Name" AS "Name", "tGz"."Geza" AS "Geza", "Anm"."Sex" AS "Sex", "Bhl"."StatusPayId" AS "StatusPayId", "tZv"."Zeva" AS "Zeva", "Anm"."Nolad" AS "Nolad", "Anm"."Ikur" AS "Ikur", "tGz"."Danger" AS "Danger", "Bhl"."TZeut" AS "TZeut", "Bhl"."Shem" AS "Shem", "tRh"."Rehov" AS "Rehov", "Bhl"."Number" AS "Number", "tIs"."CodIsuv" AS "CodIsuv", "Bhl"."TeivatDoar" AS "TeivatDoar", "Bhl"."Mikud" AS "Mikud", "Bhl"."Tel" AS "Tel", "Bhl"."Fax" AS "Fax", "Bhl"."Cel" AS "Cel", "Bhl"."EMail" AS "EMail", "tCs"."Cause" AS "Cause", "Aar"."Moed" AS "MoedArhiv", "Hsn"."Moed" AS "Moed", "tRf"."LicenceNo" AS "LicenceNo", "Hsn"."Atzva" AS "Atzva", "Esg"."Moed" AS "MoedEsg", "tTh"."Tahana" AS "Tahana", "tSb"."SibaEsg" AS "SibaEsg", "tTh"."Tel" AS "TelTahana", "Ebt"."MoedT" AS "MoedT", "tDb"."DargaByte" AS "DargaByte", "Esg"."MoedEnd" AS "MoedEnd", "Esg"."TipulEsgId" AS "TipulEsgId", "Ept"."MoedBdk" AS "MoedBdk" FROM "VAnimals" AS "Anm" LEFT OUTER JOIN "VTblGeza" AS "tGz" ON ("Anm"."GezaId" = "tGz"."Id"), LEFT OUTER JOIN "VTblZeva" AS "tZv" ON ("Anm"."ZevaId" = "tZv"."Id"), LEFT OUTER JOIN "VHisun" AS "Hsn" ON ("Hsn"."AnimalId" = "Anm"."Id"), LEFT OUTER JOIN "VBahalim" AS "Bhl" ON ("Anm"."BahalimId" = "Bhl"."Id"), LEFT OUTER JOIN "VEsger" AS "Esg" ON ("Esg"."AnimalId" = "Anm"."Id"), LEFT OUTER JOIN "VAnimalArchion" AS "Aar" ON ("Aar"."AnimalId" = "Anm"."Id") LEFT OUTER JOIN "VTblRofe" AS "tRf" ON ("Hsn"."RofeId" = "tRf"."Id") LEFT OUTER JOIN "VTblRehov" AS "tRh" ON ("Bhl"."RehovId" = "tRh"."Id"), LEFT OUTER JOIN "VTblIsuv" AS "tIs" ON ("Bhl"."IsuvId" = "tIs"."Id") LEFT OUTER JOIN "VTblSibaEsg" AS "tSb" ON ("Esg"."SibaEsgId" = "tSb"."Id"), LEFT OUTER JOIN "VTblTahana" AS "tTh" ON ("Esg"."TahanaId" = "tTh"."Id"), LEFT OUTER JOIN "VEsgerPtira" AS "EPt" ON ("Ept"."EsgId" = "Esg"."Id"), LEFT OUTER JOIN "VEsgerByte" AS "Ebt" ON ("Ebt"."EsgId" = "Esg"."Id") LEFT OUTER JOIN "VTblDargaByte" AS "tDb" ON ("Ebt"."DargaByteId" = "tDb"."Id") LEFT OUTER JOIN "VTblArchionCauseAnml" AS "tCs" ON ("Aar"."CauseId" = "tCs"."Id") Source Tables ------------- VAnimals (Anm): 39784 rows VTblGeza (tGz): 176 rows VTblZeva (tZv): 39 rows VHisun (Hsn): 37682 rows VTblRofe (tRf): 145 rows VBahalim (Bhl): 32058 rows VTblRehov (tRh): 3254 rows VTblIsuv (tIs): 16 rows VEsger (Esg): 2582 rows VTblSibaEsg (tSb): 10 rows VTblTahana (tTh): 0 rows VEsgerPtira (EPt): 782 rows VEsgerByte (Ebt): 242 rows VTblDargaByte (tDb): 3 rows VAnimalArchion (Aar): 10204 rows VTblArchionCauseAnml (tCs): 12 rows Result Set ---------- The result set was static The result set consisted of zero or more rows Joins ----- The driver table was the VAnimals (Anm) table The VAnimals (Anm) table was joined to the VTblGeza (tGz) table with the left outer join expression: ("Anm"."GezaId" = "tGz"."Id") The VAnimals (Anm) table was joined to the VTblZeva (tZv) table with the left outer join expression: ("Anm"."ZevaId" = "tZv"."Id") The VAnimals (Anm) table was joined to the VHisun (Hsn) table with the left outer join expression: ("Hsn"."AnimalId" = "Anm"."Id") The VAnimals (Anm) table was joined to the VBahalim (Bhl) table with the left outer join expression: ("Anm"."BahalimId" = "Bhl"."Id") The VAnimals (Anm) table was joined to the VEsger (Esg) table with the left outer join expression: ("Esg"."AnimalId" = "Anm"."Id") The VAnimals (Anm) table was joined to the VAnimalArchion (Aar) table with the left outer join expression: ("Aar"."AnimalId" = "Anm"."Id") The VHisun (Hsn) table was joined to the VTblRofe (tRf) table with the left outer join expression: ("Hsn"."RofeId" = "tRf"."Id") The VBahalim (Bhl) table was joined to the VTblRehov (tRh) table with the left outer join expression: ("Bhl"."RehovId" = "tRh"."Id") The VBahalim (Bhl) table was joined to the VTblIsuv (tIs) table with the left outer join expression: ("Bhl"."IsuvId" = "tIs"."Id") The VEsger (Esg) table was joined to the VTblSibaEsg (tSb) table with the left outer join expression: ("Esg"."SibaEsgId" = "tSb"."Id") The VEsger (Esg) table was joined to the VTblTahana (tTh) table with the left outer join expression: ("Esg"."TahanaId" = "tTh"."Id") The VEsger (Esg) table was joined to the VEsgerPtira (EPt) table with the left outer join expression: ("Ept"."EsgId" = "Esg"."Id") The VEsger (Esg) table was joined to the VEsgerByte (Ebt) table with the left outer join expression: ("Ebt"."EsgId" = "Esg"."Id") The VEsgerByte (Ebt) table was joined to the VTblDargaByte (tDb) table with the left outer join expression: ("Ebt"."DargaByteId" = "tDb"."Id") The VAnimalArchion (Aar) table was joined to the VTblArchionCauseAnml (tCs) table with the left outer join expression: ("Aar"."CauseId" = "tCs"."Id") The optimizer attempted to re-order the joins to a more optimal order The joins were already in the most optimal order The following join condition was applied to the VTblGeza (tGz) table: ("tGz"."Id" = "Anm"."GezaId" [Index scan]) The following join condition was applied to the VTblZeva (tZv) table: ("tZv"."Id" = "Anm"."ZevaId" [Index scan]) The following join condition was applied to the VHisun (Hsn) table: ("Hsn"."AnimalId" = "Anm"."Id" [Index scan]) The following join condition was applied to the VBahalim (Bhl) table: ("Bhl"."Id" = "Anm"."BahalimId" [Index scan]) The following join condition was applied to the VEsger (Esg) table: ("Esg"."AnimalId" = "Anm"."Id" [Row scan]) The following join condition was applied to the VAnimalArchion (Aar) table: ("Aar"."AnimalId" = "Anm"."Id" [Index scan]) The following join condition was applied to the VTblRofe (tRf) table: ("tRf"."Id" = "Hsn"."RofeId" [Index scan]) The following join condition was applied to the VTblRehov (tRh) table: ("tRh"."Id" = "Bhl"."RehovId" [Index scan]) The following join condition was applied to the VTblIsuv (tIs) table: ("tIs"."Id" = "Bhl"."IsuvId" [Index scan]) The following join condition was applied to the VTblSibaEsg (tSb) table: ("tSb"."Id" = "Esg"."SibaEsgId" [Index scan]) The following join condition was applied to the VTblTahana (tTh) table: ("tTh"."Id" = "Esg"."TahanaId" [Index scan]) The following join condition was applied to the VEsgerPtira (EPt) table: ("Ept"."EsgId" = "Esg"."Id" [Index scan]) The following join condition was applied to the VEsgerByte (Ebt) table: ("Ebt"."EsgId" = "Esg"."Id" [Index scan]) The following join condition was applied to the VTblDargaByte (tDb) table: ("tDb"."Id" = "Ebt"."DargaByteId" [Index scan]) The following join condition was applied to the VTblArchionCauseAnml (tCs) table: ("tCs"."Id" = "Aar"."CauseId" [Index scan]) ================================================================================ 39854 row(s) returned in 567.906 secs ================================================================================ "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:826E1873-822E-4725-A692-21E904363DB5@news.elevatesoft.com... > Sorin > > > Do you have indices defined for the columns that perform the joins? > > To help us help you can you post the query plans from DBISAM and ElevateDB > > > Roy Lambert > |
Sat, May 26 2007 1:24 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Sorin
You've missed indices for ("Esg"."AnimalId" = "Anm"."Id" [Row scan]) The expression: Anm.Id = Esg.AnimalId is UN-OPTIMIZED Try adding those and see what happens. Roy Lambert |
Sat, May 26 2007 5:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Sorin,
<< I add few missing indexes but it doesn't change the speed >> Roy is correct - the one join is missing an index for optimization, resulting in ~2500 * ~40000 row scans. That's the main slow-down. -- Tim Young Elevate Software www.elevatesoft.com |
Sun, May 27 2007 5:54 AM | Permanent Link |
"Sorin" | Wow thats great!!!
dbisam 40 seconds edb 22 seconds thanks sorin > Roy is correct - the one join is missing an index for optimization, > resulting in ~2500 * ~40000 row scans. That's the main slow-down. > > -- > Tim Young > Elevate Software > www.elevatesoft.com > |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |