Malcolm,

<< By using EMS's DBISAM Manager I can see the tables have the following
indexes
ProductDay
Primary - SalesDate,LocationNo,TerminalNo,PriceLevel,ProductCode
SalesDate - SatesDate
LocationNo - LocationNo
TerminalNo - TerminalNo
PriceLevel - PriceLevel
ProductCode - ProductCode >>

Are any of the indexes marked as case-insensitive ?

<< I don't know how to get to DBSys all I have downloaded is the ODBC
driver........ Will "queryr.GeneratePlan := True," run/work in VB6 >>

No, you can only generate execution plans from the DBSYS utility or any of
the Delphi/C++Builder VCL components.  ODBC does not provide a facility for
execution plans outside of straight SQL, which is not how DBISAM returns
execution plans.

You can download a copy of DBSYS here:

http://www.elevatesoft.com/download?action=login

Just sign up for an evaluation, and then when you get the approval
information, you'll need to download the DBISAM 4.x Additional Software and
Utilities.  That includes the Database System Utility (DBSYS).  In DBSYS,
you simply select File..New SQL Query, and then select the target database
directory.  You can then select the Generate Plan check box from the SQL
Query window.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Are any of the indexes marked as case-insensitive ?

Dammit - I keep forgetting that.

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Tim

>Are any of the indexes marked as case-insensitive ?

Dammit - I keep forgetting that.

Roy Lambert

Many Thanks Guys,

I have signed up, once I receive the sign on info I will download and see what I can do. I'll let you know.

PS how are indexes marked as case-insensitive (what will I see that tells me?)

Much appreciated
Malcolm

Malcolm


In DBSys there's a column in the grid that displays the indices (actually headed indexes cos Yanks can't spell which says which are case insensitive and which aren't.

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Malcolm


In DBSys there's a column in the grid that displays the indices (actually headed indexes cos Yanks can't spell which says which are case
insensitive and which aren't.

Roy Lambert

Hi
How long does it normally take to hear back with a sign-on as I have not received anything as yet

Malcoln

Malcolm,

<<How long does it normally take to hear back with a sign-on as I have not
received anything as yet >>

Send me an email with your information and I will see what the status of the
evaluation acknowledgement is.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Many Thanks for your offer - received the logon info this morning.

As resquested I have run a particular SQL and genterated the Plan (see below) on the Paradox database this sql takes around 2 minutes to run on
DBISAM just over 5. I have looked at the plan and it seems to be saying the SQL is ok but would appreciate your interpretation. Any thing I can
do to improve the times will be welcome. I have changed the SalesDate, LocationNo, and ProductCode indecies to be Duplicate byte compression
(As far as I can see all indecies within the database were set to None) I can change them back but on a previous test it did appear to offer some
improvement.

Any thoughts or suggestion will be very welcome.

Regards
Malcolm

================================================================================
SQL statement (Executed with 4.26 Build 3)
================================================================================

SELECT DISTINCT D.SalesDate, D.LocationNo, D.ProductCode, D.SalesQty,
D.SalesValue, D1.SellingCode, D1.ECRDept, D1.FreeText4
FROM ProductDay D, ProductDetail D1
WHERE
(D.SalesDate >= '2008-08-01'
and D.SalesDate <= '2008-08-07')
AND (D1.ECRDept = 3)
AND (D1.ProductCode = D.ProductCode)
AND (D1.LocationNo = D.LocationNo)
ORDER BY D.SalesDate, D.LocationNo, D.ProductCode, D.SalesQty, D.SalesValue,
D1.SellingCode, D1.ECRDept, D1.FreeText4

Tables Involved
---------------

ProductDetail (D1) table opened shared, has 731901 rows
ProductDay (D) table opened shared, has 3272361 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

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

SalesDate ASC
LocationNo ASC
ProductCode ASC
SalesQty ASC
SalesValue ASC
SellingCode ASC
ECRDept ASC
FreeText4 ASC

WHERE Clause Execution
----------------------

The expression:

D.SalesDate >= '2008-08-01' and D.SalesDate <= '2008-08-07'

is OPTIMIZED, covers 718738 rows or index keys, costs 40188633 bytes, and will
be applied to the ProductDay table (D) before any joins

The expression:

D1.ECRDept = 3

is OPTIMIZED, covers 16524 rows or index keys, costs 150368 bytes, and will be
applied to the ProductDetail table (D1) before any joins

Join Ordering
-------------

The driver table is the ProductDetail table (D1)

The ProductDetail table (D1) is joined to the ProductDay table (D) with the
INNER JOIN expression:

D1.ProductCode = D.ProductCode AND D1.LocationNo = D.LocationNo

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:

D1.ProductCode = D.ProductCode AND D1.LocationNo = D.LocationNo

is OPTIMIZED

================================================================================
>>>>> 7422 rows affected in 312.766 seconds
================================================================================

Malcolm


One thing I should have spotted earlier is that the result set is canned. In DBISAM terms this means that a new temporary table is created and the data written to that. Still 7400 rows shouldn't take that long to write.

Do all the columns in the ORDER BY clause have an index on them, or are they the first field in an index?

Roy Lambert [Team Elevate]

Hi Roy,

Thanks again for your response.

Only SalesDate, ProductCode & LocationNo are indexed (on both tables) none of the others are. Should they be, is it best that all fields be
indexed?

How else can the result of the SQL be processed other than the "Canned" result.

Regards
Malcolm

Malcolm


I wrote the last two paragraphs and then read the query again and I have a suspicion that your query isn't doing what you want. From the manual

Cartesan Join
A Cartesian join connects two tables in a non-relational manner. The syntax is as follows:
FROM table_reference, table_reference [,table_reference...]
Use a Cartesian join to connect the column of two tables into one result set, but without correlation between the rows
from the tables. Cartesian joins match each row of the source table with each row of the joining table. No column
comparisons are used, just simple association. If the source table has 10 rows and the joining table has 10, the result
set will contain 100 rows as each row from the source table is joined with each row from the joined table.

which could really explain a looooong query.

Is that what you're after.

If you want give me a call on 0 1 5 9 3 7 2 1 8 8 4 and I'll talk it through with you.

Result sets come in two flavours canned and live. Anything with a join in it (at least in DBISAM) is canned. One significant difference is that canned results sets are a temporary table written to disk and live result sets operate off the actual table. The latter are generally, but not always faster.

As far as the indices go the general rule in DBISAM is that any field involved in a WHERE or JOIN clause should either have its own index or be the first field in a multifield index, and you have those. I think for ORDER BY you want an index representing the ORDER BY clause so that it can use it for sorting so a compound index of all the fields in that clause.


Roy Lambert [Team Elevate]