Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 10 total |
different performance embeded vs. client/server |
Sun, Sep 27 2009 7:23 AM | Permanent Link |
Nenad | Hallo,
i have a query which is quite fast with an older version of dbisam, and with the current version embeded but really slow with c/s with Dbisam 4.29b1 There are 2 tables : a short table A which has 12 IDs, their Names and a active flag and a much larger Table B and C which have compound keys (can't change this) which consists of (ID1, ID2, ID_from_table_A) and some data e.g. Nr I want to get all the items from A with an indication if there is a record matching in B. select a.*,B.Nr as p1, C.Nr as p2 from TableA A left join TableB B on (B.ID1 =1 and B.ID2 =1 and a.ID=B.ID_from_table_A) left join TableC C on (C.ID1 =1 and C.ID2 =1 and a.ID=C.ID_from_table_A) where (a.active=1 or A.Nr<>null) "1" would be a parameter of the query this is quite fast with dbisam 4.26 Build 2 even with C/S, on 4.26 Build 3 is very slow.. The difference it seems is this from the Plan "The expression: B.ID1 = 1 and B.ID2 = 1 and a.ID = B.ID_from_table_A is PARTIALLY-OPTIMIZED" and in the new version : "The expression: a.ID = B.ID_from_table_A is UN-OPTIMIZED The expression: B.ID1 = 1 and B.ID2 = 1 is UN-OPTIMIZED " how can i reorder by statement for the new version ? |
Sun, Sep 27 2009 12:24 PM | Permanent Link |
"Raul" | What are the indicies on all of the tables ?
Non-optimized in most cases boils down dbisam not being able to use an index so start with that Raul "Nenad" <nenad.steric@chello.at> wrote in message news:CC0A4170-D0DE-4056-A25E-A7EB3CB3B64E@news.elevatesoft.com... > Hallo, > > i have a query which is quite fast with an older version of dbisam, > and with the current version embeded but really slow with c/s with Dbisam > 4.29b1 > > There are 2 tables : a short table A which has 12 IDs, their Names and a > active flag > and a much larger Table B and C which have compound keys (can't change > this) > which consists of (ID1, ID2, ID_from_table_A) and some data e.g. Nr > > I want to get all the items from A with an indication if there is a record > matching in B. > > > select > a.*,B.Nr as p1, C.Nr as p2 from TableA A > left join TableB B on > (B.ID1 =1 > and B.ID2 =1 > and a.ID=B.ID_from_table_A) > left join TableC C on > (C.ID1 =1 > and C.ID2 =1 > and a.ID=C.ID_from_table_A) > where (a.active=1 or A.Nr<>null) > > "1" would be a parameter of the query > > this is quite fast with dbisam 4.26 Build 2 even with C/S, on 4.26 Build > 3 is very slow.. > > The difference it seems is this from the Plan > > "The expression: > B.ID1 = 1 and B.ID2 = 1 and a.ID = B.ID_from_table_A > is PARTIALLY-OPTIMIZED" > > and in the new version : > > "The expression: > a.ID = B.ID_from_table_A > is UN-OPTIMIZED > > The expression: > B.ID1 = 1 and B.ID2 = 1 > is UN-OPTIMIZED > " > > how can i reorder by statement for the new version ? |
Mon, Sep 28 2009 6:28 AM | Permanent Link |
Nenad | Table B and C have both a big compound primary index (ID1, ID2, ID_from_table_A, ID3, ID4) and table A has only the ID as its primary index. The interessting thing is that it worked quite fast in the older version of dbisam. Also the query should be able to use the primary key of B and C as i specify values for ID1 and ID2 in the query, and thats what happens if i use the embeded version of dbisam. Nenad Raul wrote: > What are the indicies on all of the tables ? > > Non-optimized in most cases boils down dbisam not being able to use an index > so start with that > > Raul > |
Mon, Sep 28 2009 7:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Nenad
>Table B and C have both a big compound primary index (ID1, ID2, ID_from_table_A, ID3, ID4) >and table A has only the ID as its primary index. The only part of that index that DBISAM and ElevateDB can use is ID1 (assuming you have listed them in the order they occur within the index). What you will have to do is create single field indices for each column used is the JOIN or WHERE clauses. Roy Lambert [Team Elevate] |
Mon, Sep 28 2009 9:13 AM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:00AF66D4-7B86-446F-A2CB-E5E4EF9AC4C2@news.elevatesoft.com... > Nenad > >>Table B and C have both a big compound primary index (ID1, ID2, >>ID_from_table_A, ID3, ID4) >>and table A has only the ID as its primary index. > > The only part of that index that DBISAM and ElevateDB can use is ID1 > (assuming you have listed them in the order they occur within the index). > > What you will have to do is create single field indices for each column > used is the JOIN or WHERE clauses. > That still does not explain why performance change going from one version to the next. Robert |
Mon, Sep 28 2009 9:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
>That still does not explain why performance change going from one version to >the next. Correct, and I didn't even try to because I have no idea. Roy Lambert [Team Elevate] |
Mon, Sep 28 2009 11:24 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Nenad,
<< this is quite fast with dbisam 4.26 Build 2 even with C/S, on 4.26 Build 3 is very slow.. >> I suspect that the difference is down to this incident report: http://www.elevatesoft.com/incident?action=viewaddr&category=dbisam&release=4.26&type=f&incident=2612 <<The difference it seems is this from the Plan "The expression: B.ID1 = 1 and B.ID2 = 1 and a.ID = B.ID_from_table_A is PARTIALLY-OPTIMIZED" and in the new version : "The expression: a.ID = B.ID_from_table_A is UN-OPTIMIZED The expression: B.ID1 = 1 and B.ID2 = 1 is UN-OPTIMIZED " how can i reorder by statement for the new version ? >> Please post the entire query plans for 4.26 B2 and 4.26 B3, and then I'll be able to tell you what the problem is. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 29 2009 5:27 PM | Permanent Link |
Nenad | Tim,
these are the DDLs CREATE TABLE IF NOT EXISTS "TableA" ( "Spalte" SMALLINT, "Name" VARCHAR(30), "aktiv" INTEGER DEFAULT 1, PRIMARY KEY ("Spalte") COMPRESS FULL LOCALE CODE 1031 ); CREATE TABLE IF NOT EXISTS "TableB" ( "ProtokollNr" INTEGER, "UntArt_Id" INTEGER, "Spalte" SMALLINT, "OrdNr" SMALLINT DEFAULT 1, "AbNR" SMALLINT, "Nr" SMALLINT, "Data" VARCHAR(1), PRIMARY KEY ("ProtokollNr","UntArt_Id","Spalte","OrdNr","AbNR","Nr") COMPRESS FULL LOCALE CODE 1031 ); the Query is select distinct a.*,b.ProtokollNr as p1 from TableA a left join TableB b on (b.ProtokollNr =1 and b.UntArt_Id =1 and a.spalte=b.spalte) where (a.aktiv=1 or b.ProtokollNr<>null) and the fast Plan is : " ================================================================================ SQL statement (Executed with 4.26 Build 3) ================================================================================ select distinct a.*,b.ProtokollNr as p1 from TableA a left join TableB b on (b.ProtokollNr =1 and b.UntArt_Id =1 and a.spalte=b.spalte) where (a.aktiv=1 or b.ProtokollNr<>null) Tables Involved --------------- TableA (a) table opened shared, has 16 rows TableB (b) table opened shared, has 1061732 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more distinct rows Duplicate result set rows will be removed using a temporary index on all of the result set columns Join Ordering ------------- The driver table is the TableA table (a) The TableA table (a) is joined to the TableB table (b) with the LEFT OUTER JOIN expression: b.ProtokollNr = 1 and b.UntArt_Id = 1 and a.spalte = b.spalte 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 Scan Expression Execution ------------------------- The expression: a.aktiv = 1 or b.ProtokollNr <> null is UN-OPTIMIZED and will be applied to each candidate row in the result set as the result set is generated 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: b.ProtokollNr = 1 and b.UntArt_Id = 1 and a.spalte = b.spalte has been rewritten and is PARTIALLY-OPTIMIZED ================================================================================ >>>>> 11 rows affected in 0,032 seconds ================================================================================ " the (very) slow Plan is : ================================================================================ SQL statement (Executed with 4.29 Build 1) ================================================================================ select distinct a.*,b.ProtokollNr as p1 from TableA a left join TableB b on (b.ProtokollNr =1 and b.UntArt_Id =1 and a.spalte=b.spalte) where (a.aktiv=1 or b.ProtokollNr<>null) Tables Involved --------------- TableA (a) table opened shared, has 16 rows TableB (b) table opened shared, has 1061732 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more distinct rows Duplicate result set rows will be removed using a temporary index on all of the result set columns Join Ordering ------------- The driver table is the TableA table (a) The TableA table (a) is joined to the TableB table (b) with the LEFT OUTER JOIN expression: a.spalte = b.spalte AND b.ProtokollNr = 1 and b.UntArt_Id = 1 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 Scan Expression Execution ------------------------- The expression: a.aktiv = 1 or b.ProtokollNr <> null is UN-OPTIMIZED and will be applied to each candidate row in the result set as the result set is generated 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: a.spalte = b.spalte is UN-OPTIMIZED The expression: b.ProtokollNr = 1 and b.UntArt_Id = 1 is UN-OPTIMIZED ================================================================================ >>>>> 11 rows affected in 37,515 seconds ================================================================================ |
Wed, Sep 30 2009 12:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Nenad,
Thanks for the query plans. Unfortunately, I cannot get the information I need from just the empty tables because the joins aren't actually executed. I need to find out why 4.29 is considering the joins to be un-optimized, and that will require the data. Would it be possible to get the data ? -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Oct 3 2009 5:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Nenad,
I've corrected the problem, and a fix will be available in the next DBISAM build, which should be by Monday or Tuesday. The issue was caused by this fix: http://www.elevatesoft.com/incident?action=viewrep&category=dbisam&release=4.27&type=f&incident=2879 -- Tim Young Elevate Software www.elevatesoft.com |
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 |