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

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

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
>

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

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

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
>