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 ?

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 ?

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
>

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]

"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

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]

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

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
================================================================================

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

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