I am looking for a good unicode, local, single user BDE replacement and ElevateDB looked very nice to me, until I started comparing speeds...

How is it possible that a nested inner join query with 5 tales (just like the one in the ElevateDB 2 sql manual) takes 280 seconds while it took only
1.7 seconds with the BDE???  This is totally unacceptable!  My tables have only around 8000 records.

This is the example I'm referring to:
SELECT c.Company,
o.OrderNo,
e.LastName,
p.Description,
v.VendorName
FROM Customer c
INNER JOIN Orders o ON c.CustNo=o.CustNo
INNER JOIN Employee e ON o.EmpNo=e.EmpNo
INNER JOIN Items i ON o.OrderNo=i.OrderNo
INNER JOIN Parts p ON i.PartNo=p.PartNo
INNER JOIN Vendors v ON p.VendorNo=v.VendorNo
ORDER BY e.LastName

BDE uses a lot of memory to cache a lot more data than EDB does by
default, and that's why it can be sometimes faster then EDB, but the
functionality and capacity are barely comparable.
You can increase EDB buffering to see if it helps, but much more
important than that is to ensure your queries are *optimized*.
Can you please post a "Plan" of the execution of your query?
To obtain a Plan, check the "Request Execution Plan" check box before
executing the query in EDB Manager.

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:
You can increase EDB buffering to see if it helps, but much more
important than that is to ensure your queries are *optimized*.

How could I do that??? Nothing in the help about that.

The "plan" only suggests to reverse the order of the tables, but that only makes a difference of a few percents since all 5 tables have
approximately 8000 records.

Are there indices on all fields used in the JOINs and ORDER BY clauses, and if so do they have the same collation?


I know that the BDE can be faster than ElevateDB but I find a factor in excess of 150 times a bit difficult to believe.

Roy Lambert [Team Elevate]

Yes, there are indexes on all those fields, and they are all just integers.
With or without the sorting, that makes almost no difference.

In that case, as Fernando asked can you post a copy of the plan for us to have a look at. When there are issues of this nature, if they can't be fixed by using the existing facilities, Tim will generally resolve it. Either with advice or by improving ElevateDB.


If there are problems than please help us to sort them out. Even if you don't go with ElevateDB you'll leave us with a better product

Roy Lambert [Team Elevate]

All right....


================================================================================
SQL Query (Executed by ElevateDB 2.02 Build 7)

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 "A"."Woord" AS "Woord", "A"."geslacht" AS "GeslachtNL",
"B"."KonceptNL" AS "KonceptNL", "B"."Gebruik" AS "GebruikNL", "B"."Kat" AS "Kat",
"E"."WoordSR" AS "WoordSR", "E"."geslacht" AS "GeslachtSR", "D"."KonceptSR" AS "KonceptSR"
"D"."Gebruik" AS "GebruikSR", "B"."K_ID" AS "K_ID", "B"."trefwoorden" AS
"trefwoorden", "B"."Syn_K_ID" AS "Syn_K_ID", "A"."WoordMetKlemtonen" AS
"WoordMetKlemtonenNL", "A"."Grammatika" AS "GrammatikaNL", "C"."Opmerkingen" AS "Opmerkingen"
"E"."WoordMetKlemtonen" AS "WoordMetKlemtonenSR", "E"."Grammatika" AS
"GrammatikaSR", "C"."Syn_K_SR_ID" AS "Syn_K_SR_ID", "D"."K_ID" AS "K_SR_ID",
"D"."WoordSR_ID" AS "WoordSR_ID", "B"."nivo" AS "nivo", "B"."foto" AS "foto" FROM
"WoordenNL" AS "A" INNER JOIN "KonceptenNL" AS "B" ON "A"."ID" = "B"."WoordNL_Id" INNER
JOIN "Vertaling_NL_SR" AS "C" ON "B"."Syn_K_ID" = "C"."Syn_K_NL_ID" INNER JOIN
"KonceptenSR" AS "D" ON "C"."Syn_K_SR_ID" = "D"."Syn_K_ID" INNER JOIN "WoordenSR"
AS "E" ON "D"."WoordSR_ID" = "E"."ID" ORDER BY "A"."Woord", "B"."Kat",
"B"."KonceptNL"

Source Tables
-------------

WoordenNL (A): 6880 rows
KonceptenNL (B): 7884 rows
Vertaling_NL_SR (C): 6540 rows
KonceptenSR (D): 6872 rows
WoordenSR (E): 6075 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Joins
-----

The driver table was the WoordenNL (A) table

The WoordenNL (A) table was joined to the KonceptenNL (B) table with the inner
join expression:

"A"."ID" = "B"."WoordNL_Id"

The KonceptenNL (B) table was joined to the Vertaling_NL_SR (C) table with the
inner join expression:

"B"."Syn_K_ID" = "C"."Syn_K_NL_ID"

The Vertaling_NL_SR (C) table was joined to the KonceptenSR (D) table with the
inner join expression:

"C"."Syn_K_SR_ID" = "D"."Syn_K_ID"

The KonceptenSR (D) table was joined to the WoordenSR (E) table with the inner
join expression:

"D"."WoordSR_ID" = "E"."ID"

The optimizer attempted to re-order the joins to a more optimal order

The optimizer successfully re-ordered the joins into this more optimal order:

The driver table was the WoordenSR (E) table

The WoordenSR (E) table was joined to the KonceptenSR (D) table with the inner
join expression:

"D"."WoordSR_ID" = "E"."ID"

The KonceptenSR (D) table was joined to the Vertaling_NL_SR (C) table with the
inner join expression:

"C"."Syn_K_SR_ID" = "D"."Syn_K_ID"

The Vertaling_NL_SR (C) table was joined to the KonceptenNL (B) table with the
inner join expression:

"B"."Syn_K_ID" = "C"."Syn_K_NL_ID"

The KonceptenNL (B) table was joined to the WoordenNL (A) table with the inner
join expression:

"A"."ID" = "B"."WoordNL_Id"

The following join condition was applied to the KonceptenSR (D) table:

"D"."WoordSR_ID" = "E"."ID" [Index scan (KonceptenSR.iWoordSR)]

The following join condition was applied to the Vertaling_NL_SR (C) table:

"C"."Syn_K_SR_ID" = "D"."Syn_K_ID" [Index scan (Vertaling_NL_SR.iSR)]

The following join condition was applied to the KonceptenNL (B) table:

"B"."Syn_K_ID" = "C"."Syn_K_NL_ID" [Row scan (KonceptenNL)]

The following join condition was applied to the WoordenNL (A) table:

"A"."ID" = "B"."WoordNL_Id" [Index scan (woordenNL.PrimKey)]

================================================================================
7898 row(s) returned in 265.358 secs
================================================================================

In EDB the buffering parameters are defined on a table basis. You can
alter the buffering default values when you create the tables, or after
that using ALTER TABLE :

CREATE|ALTER TABLE <TableName>
....
....
[MAX ROW BUFFER SIZE <MaxRowBufferSize>]
[MAX INDEX BUFFER SIZE <MaxIndexBufferSize>]
....

You can also use EDB Manager to alter this parameters interactively.

However, before any change to the buffering values I'd like to see the
execution Plan. Can you post it please?

--
Fernando Dias
[Team Elevate]

The following part of your plan indicates that one of the join
conditions is being executed without the use of an index:

-----------------------------
The following join condition was applied to the KonceptenNL (B) table:
"B"."Syn_K_ID" = "C"."Syn_K_NL_ID" [Row scan (KonceptenNL)]
-----------------------------

Are you sure you have an index on KonceptenNL where the first column is
 "Syn_K_ID" ?


--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:
Are you sure you have an index on KonceptenNL where the first column is
 "Syn_K_ID" ?

Well, apparently I forgot that one...
Adding this index seems to make the difference!  Now the execution time is 2.99 seconds; still the double of the BDE, but acceptable.
Shouldn't the SQL "engine" know when it should create a temporary index, as apparently the BDE does?
If not, please add this hint to your help files!!!
BTW: changing the buffer sizes didn't change anything.

Thanks for spending your Sunday assisting me!