The following query is the underlying cursor specification of the Northwind
"CustOrderHist" procedure:

SELECT
 ProductName,
 SUM(Quantity) AS Total
FROM
 Products P,
 "Order Details" OD,
 Orders O,
 Customers C
WHERE
 C.CustomerID = 'BERGS'
 AND C.CustomerID = O.CustomerID
 AND O.OrderID = OD.OrderID
 AND OD.ProductID = P.ProductID
GROUP BY ProductName

Execution time: 1106 *seconds*

Equivalent JOIN syntax:

SELECT
 ProductName,
 SUM(Quantity) AS Total
FROM
 Products P
 JOIN "Order Details" OD ON OD.ProductID = P.ProductID
 JOIN Orders O ON O.OrderID = OD.OrderId
 JOIN Customers C ON C.CustomerID = O.CustomerID
WHERE
 C.CustomerID = 'BERGS'
GROUP BY ProductName

Execution time: 20 milliseconds

Btw, DBISAM executes the two join syntaxes equally fast, as do other
database engines.

Ole Willy Tuv

> The following query is the underlying cursor specification of the Northwind
> "CustOrderHist" procedure:
>

/me shoots Ole!

--
Charalabos Michael - [Creation Power] - http://www.creationpower.gr

Ole


I presume you're referring to time taken in ElevateDB?

Roy Lambert

Roy,

<< I presume you're referring to time taken in ElevateDB? >>

Yes. ElevateDB obviously processes the old-style join differently than the
explicit JOIN syntax.

Ole Willy Tuv

Ole

Since you're on line any idea how I go about creating an in-memory table?

Due to the way the TMS dbgrid doesn't work correctly with queries I need to use a table, want to use a memory rather than a disk one and have no real idea how.


Roy Lambert

Roy,

<< Since you're on line any idea how I go about creating an in-memory table?

Due to the way the TMS dbgrid doesn't work correctly with queries I need to
use a table, want to use a memory rather than a disk one and have no real
idea how. >>

Sorry, no. I don't have EDB installed, only using the EDB Manager tool.

Ole Willy Tuv

Tim,

Another issue I didn't notice at first, is that the aggregated column has
null in every row. This is the case with both queries.

Ole Willy Tuv

Ole


Ah well, in that case I'm stuck until the maestro logs in.

Roy Lambert

Tim,

<< Another issue I didn't notice at first, is that the aggregated column has
null in every row. >>

Seems to be an issue with SMALLINT (the column type of "Order
Details".Quantity). A cast to any other numeric type returns the correct
values, e.g.:

SELECT
 ProductName,
 SUM(CAST(Quantity AS INTEGER)) AS Total

Ole Willy Tuv

Ole,

<< The following query is the underlying cursor specification of the
Northwind "CustOrderHist" procedure: >>

See here:

http://www.elevatesoft.com/edb1migrate_statements.htm

under SELECT statement:

"ElevateDB does not optimize join expressions in the WHERE clause, otherwise
known as SQL-89 style joins. You must use the JOIN clause in order to have
ElevateDB optimize the joins."

--
Tim Young
Elevate Software
www.elevatesoft.com