I am trying to run a query where I find out a list of records that exists
in one table but not another. A fairly standard query of the sort

SELECT box.boxnumber
FROM
BOX
LEFT OUTER JOIN Import_Offsite ON (box.boxnumber = Import_Offsite.Box_nr)
WHERE
( Import_Offsite.Box_Nr is NULL)
ORDER BY
box.boxnumber

The two tables have about 3,000 records in each. The result set is around
10. Just to give you an idea of the numbers. This query takes a long time
- 60 seconds - sometime a LOT more. With two tables of around 10,000 in each,
I gave up waiting (many minutes).

I have checked out the Plan, and everything is optimized ok - except for
the join. ie
  (box.boxnumber = Import_Offsite.Box_nr)

Both of these fields are text fields (about 20 vchar wide). Both fields are
indexed in their respective tables, both non-case sensitive, non-unique.

What more can I do? Can I make the join optimised somehow?  Would that help
the speed?



My system
Windows XP, Delphi 7
DBISAM 4.25

Kerry Neighbour <kerry@dojitraders.com> wrote in
news:639022ca0b048c939fad0e7b55e@elevatesoft.com:

> I am trying to run a query where I find out a list of records that
> exists in one table but not another. A fairly standard query of the
> sort
>
> SELECT box.boxnumber
> FROM
> BOX
> LEFT OUTER JOIN Import_Offsite ON (box.boxnumber =
> Import_Offsite.Box_nr) WHERE
> ( Import_Offsite.Box_Nr is NULL)
> ORDER BY
>  box.boxnumber
>
> The two tables have about 3,000 records in each. The result set is
> around 10. Just to give you an idea of the numbers. This query takes a
> long time - 60 seconds - sometime a LOT more. With two tables of
> around 10,000 in each, I gave up waiting (many minutes).
>
> I have checked out the Plan, and everything is optimized ok - except
> for the join. ie
>    (box.boxnumber = Import_Offsite.Box_nr)
>
> Both of these fields are text fields (about 20 vchar wide). Both
> fields are indexed in their respective tables, both non-case
> sensitive, non-unique.
>
> What more can I do? Can I make the join optimised somehow?  Would that
> help the speed?
>
>
>
> My system
> Windows XP, Delphi 7
> DBISAM 4.25
>
>
>

Could try selecting distinct box.boxnumber into a memory table, deleting
from the memory table where IN SELECT Box_nr FROM Import_Offsite, and
then selecting box.* joined to memory table on boxnumber.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Kerry

> I have checked out the Plan, and everything is optimized ok - except for
> the join. ie
>   (box.boxnumber = Import_Offsite.Box_nr)
> What more can I do? Can I make the join optimised somehow?  Would that
> help the speed?
You have already answered your question. You can add an index for the field
boxnumber in box table and another index to field Import_Offsite in Box_nr
table.

Eduardo

"Kerry Neighbour" <kerry@dojitraders.com> wrote in message
news:639022ca0b048c939fad0e7b55e@elevatesoft.com...
>
> Both of these fields are text fields (about 20 vchar wide). Both fields
> are indexed in their respective tables, both non-case sensitive,
> non-unique.
>

It's the case insensitive, which is probably set to NO. Can you make a new
index with case insensitive = yes? Or, if your data is all caps, use
UPPER(t1.field) = UPPER(t2.field).

> What more can I do? Can I make the join optimised somehow?  Would that
> help the speed?
>

You bet it will help the speed.

Robert

Kerry,

<< I have checked out the Plan, and everything is optimized ok - except for
the join. ie
  (box.boxnumber = Import_Offsite.Box_nr)

Both of these fields are text fields (about 20 vchar wide). Both fields are
indexed in their respective tables, both non-case sensitive, non-unique. >>

Could you post a copy of the query execution plan for the query along with
the table structure ?

--
Tim Young
Elevate Software
www.elevatesoft.com

> Could you post a copy of the query execution plan for the query along
> with the table structure ?

No problems - here is is

================================================================================
SQL statement (Executed with 4.25 Build 2)
================================================================================

SELECT
 box.BoxNumber,
 box.ID
FROM
 box
 LEFT OUTER JOIN Import_OffsiteBoxes ON (box.BoxNumber =
Import_OffsiteBoxes.Box_Nr)
WHERE
(Import_OffsiteBoxes.Box_Nr is NULL)
ORDER BY
 box.BoxNumber ASC

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

box (box) table opened shared, has 1385 rows
Import_OffsiteBoxes (Import_OffsiteBoxes) table opened shared, has 1461 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

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

BoxNumber ASC

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

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

The driver table is the box table (box)

The box table (box) is joined to the Import_OffsiteBoxes table
(Import_OffsiteBoxes) with the LEFT OUTER JOIN expression:

box.BoxNumber = Import_OffsiteBoxes.Box_Nr

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

The expression:

Import_OffsiteBoxes.Box_Nr is NULL

will be applied to each candidate row in the result set as the result set is
generated due to the Import_OffsiteBoxes table (Import_OffsiteBoxes) being
the
target of an OUTER join

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:

box.BoxNumber = Import_OffsiteBoxes.Box_Nr

is UN-OPTIMIZED

================================================================================
>>>>> 2 rows affected in 21 seconds
================================================================================


/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "box"
(
  "ID" AUTOINC NOT NULL,
  "BoxNumber" VARCHAR(20),
  "StatusID" INTEGER DEFAULT 0,
  "BoxSizeID" INTEGER,
  "OffsiteStorageProviderID" INTEGER,
  "DisposalReviewDate" VARCHAR(10),
  "MarkedForDisposal" INTEGER DEFAULT 0,
  "CreatedByID" INTEGER DEFAULT 0,
  "Created" DATE,
  "OfficeID" INTEGER DEFAULT 0,
  "Permanent" INTEGER DEFAULT 0,
  "OnHoldForID" INTEGER DEFAULT 0,
  "RetainYears" INTEGER DEFAULT 0,
  "RetainMonths" INTEGER DEFAULT 0,
  "BookInFlag" VARCHAR(10) DEFAULT '0',
  "OSSendOffsiteID" INTEGER DEFAULT 0,
  "DisposalReviewFromFile" INTEGER,
PRIMARY KEY ("ID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

CREATE INDEX IF NOT EXISTS "StatusID" ON "box" ("StatusID");
CREATE INDEX IF NOT EXISTS "BoxSizeID" ON "box" ("BoxSizeID");
CREATE INDEX IF NOT EXISTS "OffsiteStorageProviderID" ON "box" ("OffsiteStorageProviderID");
CREATE INDEX IF NOT EXISTS "CreatedByID" ON "box" ("CreatedByID");
CREATE INDEX IF NOT EXISTS "OfficeID" ON "box" ("OfficeID");
CREATE NOCASE INDEX IF NOT EXISTS "boxnumber" ON "box" ("BoxNumber");
CREATE INDEX IF NOT EXISTS "OSSendOffsiteID" ON "box" ("OSSendOffsiteID");


* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "Import_OffsiteBoxes"
(
  "ID" AUTOINC,
  "Box_Nr" VARCHAR(20),
  "Box_Status" VARCHAR(20),
  "Status_Date" DATE,
  "UpdateStatus" SMALLINT DEFAULT 0,
  "UpdateComment" VARCHAR(80),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 2
);

CREATE NOCASE INDEX IF NOT EXISTS "Box_Nr" ON "Import_OffsiteBoxes" ("Box_Nr");
>

> You have already answered your question. You can add an index for the
> field boxnumber in box table and another index to field Import_Offsite
> in Box_nr table.

Well, as I said...
"Both fields are indexed in their respective tables, both non-case sensitive,
non-unique."

>> Both of these fields are text fields (about 20 vchar wide). Both
>> fields are indexed in their respective tables, both non-case
>> sensitive, non-unique.
>>
> It's the case insensitive, which is probably set to NO. Can you make a
> new index with case insensitive = yes? Or, if your data is all caps,
> use UPPER(t1.field) = UPPER(t2.field).

No - as I said, the indexes are (case-insensitve = yes).


>> What more can I do? Can I make the join optimised somehow?  Would
>> that help the speed?
>>
> You bet it will help the speed.

I assume so - but HOW? That is the question I am asking. What more can I
do other than index the join fields?

> Could you post a copy of the query execution plan for the query along
> with the table structure ?
>

I added the JOINOPTIMIZECOSTS switch and I got the following, if that is
of any help

Join Execution
--------------

Costs ARE being taken into account when executing this join
Remove the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the optimizer to stop considering costs when optimizing this join

The expression:

box.BoxNumber = Import_OffsiteBoxes.Box_Nr

is UN-OPTIMIZED and is estimated to cost 245448 bytes per candidate row

================================================================================
>>>>> 2 rows affected in 20.531 seconds
================================================================================

"Kerry Neighbour" <kerry@dojitraders.com> wrote in message
news:9F846D12-4BE7-48B9-ACA1-B12E4CF72CA9@news.elevatesoft.com...
>>> Both of these fields are text fields (about 20 vchar wide). Both
>>> fields are indexed in their respective tables, both non-case
>>> sensitive, non-unique.
>>>
>> It's the case insensitive, which is probably set to NO. Can you make a
>> new index with case insensitive = yes? Or, if your data is all caps,
>> use UPPER(t1.field) = UPPER(t2.field).
>
> No - as I said, the indexes are (case-insensitve = yes).

Hmmm, look at this (AFAIK only changing table and field names) Something is
different in your setup, but I can't see what.

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "t1"
(
  "f1" VARCHAR(10),
  "f2" VARCHAR(10),
NOCASE PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

CREATE NOCASE INDEX IF NOT EXISTS "i1" ON "t1" ("f1");

INSERT INTO "t1" VALUES (
        'ABCD',
        'a');
INSERT INTO "t1" VALUES (
        'BCDE',
        'b');
INSERT INTO "t1" VALUES (
        'LLIG',
        'c');
/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "t2"
(
  "f1" VARCHAR(10),
  "f2" VARCHAR(10),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

CREATE INDEX IF NOT EXISTS "i1" ON "t2" ("f1");

INSERT INTO "t2" VALUES (
        'XYSS',
        'a1');
INSERT INTO "t2" VALUES (
        'ABCD',
        'a2');
SELECT
 t1.f1,
 t1.f2
FROM
 t1
 LEFT OUTER JOIN t2 ON (t1.f1 =
t2.f1)
WHERE
(t2.f1 is NULL)
ORDER BY
 t1.f1 ASC


================================================================================
SQL statement (Executed with 4.24 Build 1)
================================================================================

SELECT
 t1.f1,
 t1.f2
FROM
 t1
 LEFT OUTER JOIN t2 ON (t1.f1 =
t2.f1)
WHERE
(t2.f1 is NULL)
ORDER BY
 t1.f1 ASC

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

t1 (t1) table opened shared, has 3 rows
t2 (t2) table opened shared, has 2 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

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

f1 ASC

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

The expression:

t2.f1 is NULL

will be applied to each candidate row in the result set as the result set is
generated due to the t2 table (t2) being the target of an OUTER join

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

The driver table is the t1 table (t1)

The t1 table (t1) is joined to the t2 table (t2) with the LEFT OUTER JOIN
expression:

t1.f1 = t2.f1

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:

t1.f1 = t2.f1

is OPTIMIZED

================================================================================
>>>>> 2 rows affected in 0 seconds
================================================================================


>
>>> What more can I do?

Rain dance. Or maybe Tim or somebody else has an answer. I can't see why
your join is unoptimized, and mine is optimized. Either I'm missing
something obvious, or there is some subtle bug in DBISAM.

Robert