I am using Delphi 7 with DBISAM c/s 4.25 build 2. I am running a simply query
on a table with some 40,000 records in it. There are a couple of inner and
outer joins as well to some very small tables.

I have added a progress bar to the QueryProgress event, and it works ok.
And the query works ok.

The thing is, there is a big time delay between when I start the query, and
when the progress indicator starts to move.

It takes 10 seconds between the Query.Open and the bar gettiing to the 5%
mark (ie the first movement). It then takes 20 seconds to complete the rest
of the query - ie the other 95%.

Since I am trying to get this query as fast as I can, I am wondering what
I can do to speed up the fiirst 5% that takes 30% of the time!

my code is something like this

   Query.close
   Query.SQL.Assign(lstSQL)
   progressbar.position:=0;
   Query.prepare
   progressbar.position:=0;
   Query.open
....do more things

I have run the query through DBSYS and I have optimised the Plan ok.

The reason this 'problem' came to light is that when the query was not so
optimised, it took AGES (10 minutes or more ) to get the progress bar to
move - to the extent that you thought that the program had locked up.

Mind you, I do not think that 33 seconds to load a simple query on a 40,000
record table is very good either! And this is generally with about 20 records
returned. Still, I am working on it.

>I am using Delphi 7 with DBISAM c/s 4.25 build 2. I am running a simply
>query on a table with some 40,000 records in it. There are a couple of
>inner and outer joins as well to some very small tables.
>
> I have added a progress bar to the QueryProgress event, and it works ok.
> And the query works ok.
>
> The thing is, there is a big time delay between when I start the query,
> and when the progress indicator starts to move.
>
> It takes 10 seconds between the Query.Open and the bar gettiing to the 5%
> mark (ie the first movement). It then takes 20 seconds to complete the
> rest of the query - ie the other 95%.
> I have run the query through DBSYS and I have optimised the Plan ok.
What is the time reported by dbsys for this query?

Kerry,

<< Since I am trying to get this query as fast as I can, I am wondering what
I can do to speed up the fiirst 5% that takes 30% of the time! >>

You can start by posting the query execution plan here.

--
Tim Young
Elevate Software
www.elevatesoft.com

>
> You can start by posting the query execution plan here.
>

Ok - here is the plan from DBSYS. Interestingly enough, I found that DBSYS
has the same problem. When I run this query, nothing happens for a long time,
then the program bar starts to move. In one test I timed, it took 3 seconds
for the bar to start to move, and 11 seconds for the whole query. With slower
queries, it seems to keep the same sort of ratio. ie if a query takes 8 minutes,
you can be sitting there for 2 minutes with nothing happening at all.

I just ran this query on the same main table, but with a larger File table
(500,000). It took 4 minutes for the progress bar to start, and 5 minutes
for the full query to finish. This is an extreme example of what I am seeing.

And another thing, which is probably Windows. The more I run a query, the
faster it gets! No doubt something is being cached somewhere. This query
that I show below took 76 seconds the first time through. after a few runs
it is down to 11 seconds! While this sounds good, it is not. I am doing timing
tests on remote/local connections, and I am finding that it is hard to do
as caching is taking out the accuracy of the tests. Caching is not likely
to happen in real use, as few users run the same query over and over (as
I am doing).

Anyway, this query is very slow, even though everyting seems optimised. It
is only a comparitively small table (35,000 records), so it should not be
this slow. It return zero records, which is correct.

Any hints on making the query faster will be appreciated!

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

SELECT
 box.ID,
 box.BoxNumber,
 box.RetainYears,
 box.RetainMonths,
 box.OffsiteStorageProviderID,
 box.DisposalReviewDate,
 box.MarkedForDisposal,
 box.OfficeID,
 box.OffsiteStorageProviderID,
 box.BoxSizeID,
 box.StatusID,
 box.CreatedByID,
 box.Created,
 box.permanent,
 box.MarkedForDisposal,
 boxstorage.storagelocation
FROM
 box
 INNER JOIN userauthority_offices ON (box.OfficeID =
userauthority_offices.OfficeID)
 LEFT OUTER JOIN boxstorage ON (box.officeID = boxstorage.OfficeID)
 LEFT OUTER JOIN currentholds_boxes ON (box.ID = currentholds_boxes.BoxID)
 LEFT OUTER JOIN file ON (box.ID = file.BoxID)
 LEFT OUTER JOIN currentholds_files ON (file.ID = currentholds_files.FileID)
WHERE (userauthority_offices.UserID = 3)
 AND (file.BoxID IS NULL)
ORDER BY box.ID
TOP 1000

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

box (box) table opened shared, has 34929 rows
userauthority_offices (userauthority_offices) table opened shared, has 101
rows
boxstorage (boxstorage) table opened shared, has 6 rows
currentholds_boxes (currentholds_boxes) table opened shared, has 1 rows
file (file) table opened shared, has 107156 rows
currentholds_files (currentholds_files) table opened shared, has 1 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 primary index for the table box

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

The expression:

userauthority_offices.UserID = 3

is OPTIMIZED, covers 10 rows or index keys, costs 126 bytes, and will be
applied to the userauthority_offices table (userauthority_offices) before
any
joins

The expression:

file.BoxID IS NULL

will be applied to each candidate row in the result set as the result set is
generated due to the file table (file) 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.OfficeID = userauthority_offices.OfficeID

is OPTIMIZED

The expression:

box.officeID = boxstorage.OfficeID

is OPTIMIZED

The expression:

box.ID = currentholds_boxes.BoxID

is OPTIMIZED

The expression:

box.ID = file.BoxID

is OPTIMIZED

The expression:

file.ID = currentholds_files.FileID

is OPTIMIZED

================================================================================
>>>>> 0 rows affected in 76.234 seconds
================================================================================

Kerry,

<< Ok - here is the plan from DBSYS. Interestingly enough, I found that
DBSYS has the same problem. When I run this query, nothing happens for a
long time, then the program bar starts to move. In one test I timed, it took
3 seconds for the bar to start to move, and 11 seconds for the whole query.
With slower queries, it seems to keep the same sort of ratio. ie if a query
takes 8 minutes, you can be sitting there for 2 minutes with nothing
happening at all. >>

The progress bar updating is based upon the TDBISAMSession.ProgressSteps
property, so it is relative to the amount of work to do, not a specific time
interval:

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

ElevateDB improves upon this by using a time interval instead.

<< And another thing, which is probably Windows. The more I run a query, the
faster it gets! No doubt something is being cached somewhere. This query
that I show below took 76 seconds the first time through. after a few runs
it is down to 11 seconds! While this sounds good, it is not. I am doing
timing tests on remote/local connections, and I am finding that it is hard
to do as caching is taking out the accuracy of the tests. Caching is not
likely to happen in real use, as few users run the same query over and over
(as I am doing). >>

Sure it will.  The caching is done by the OS on the tables themselves, so
unless you're only using these tables in this one query, the caching will
most certainly speed things up on any subsequent table reads.

<< Anyway, this query is very slow, even though everyting seems optimised.
It is only a comparitively small table (35,000 records), so it should not be
this slow. It return zero records, which is correct. >>

Is there a way you can send me your tables, or that I can download them ?
The userauthority_offices join should effectively filter the number of rows
that need to be joined down to 10 rows, but for some reason it doesn't
appear to be doing so.

--
Tim Young
Elevate Software
www.elevatesoft.com

> ElevateDB improves upon this by using a time interval instead.

ElevateDB is looking better all the time!

> Is there a way you can send me your tables, or that I can download
> them ? The userauthority_offices join should effectively filter the
> number of rows that need to be joined down to 10 rows, but for some
> reason it doesn't appear to be doing so.

That is very kind of you. Because of the size of these things, I have uploaded
the files to one of my websites. The links are

http://www.docsminder.com/files/temp.rar   (11 megs)
http://www.docsminder.com/files/temp500k.rar   (54 megs)

The first file is all the basic tables, plus the query I run. This is probably
the best system to get - it is about 100,000 records and 40,000 records in
the main tables. Good enough for testing on.

The second file contains a bigger set of secondary files (500,000). This
data is the killer - it basically stops the system. It is where I am getting
my RemoteSessionTimeout problems, for example.

Anyway, if you can see a faster way to run the query, I would appreciate it.

Try this, I cannot test with real data but sometimes a multi-step script helps taking time
down.

Bruno Krayenbuhl

/* Exclude cases where file.BoxID is not NULL - STEP1 */
SELECT

Box.ID,
Box.OfficeID /* Pick it up for STEP2 */
INTO "/MEMORY/STEP1"
LEFT OUTER JOIN file ON (box.ID = file.BoxID)
WHERE file.BoxID is NULL;

/* Speed up "/MEMORY/STEP2" creation */
CREATE UNIQUE INDEX IxOfficeID ON "/MEMORY/STEP1"
(OfficeID);
/* Grab only the Boxes that have S1.OfficeID.UserID=3 - STEP2 */
SELECT
Box.ID,
Box.OfficeID /* Pick it up for final join */
INTO "/MEMORY/STEP2"
FROM userauthority_offices
INNER JOIN "/MEMORY/STEP1" S1
ON (userauthority_offices.OfficeID=S1.OfficeID)
WHERE (userauthority_offices.UserID = 3);

/* Not needed any more */
DROP INDEX "/MEMORY/STEP1".IxOfficeID;

/* Speed up final SELECT JOIN */
CREATE UNIQUE INDEX IxBoxId ON "/MEMORY/STEP2" (ID);
/* Finally do the OUTER JOIN */
SELECT
box.ID,
box.BoxNumber,
box.RetainYears,
box.RetainMonths,
box.OffsiteStorageProviderID,
box.DisposalReviewDate,
box.MarkedForDisposal,
box.OfficeID,
box.OffsiteStorageProviderID,
box.BoxSizeID,
box.StatusID,
box.CreatedByID,
box.Created,
box.permanent,
box.MarkedForDisposal,
boxstorage.storagelocation
INTO "/MEMORY/Result" /* Or whatever disk base workfile */
FROM "/MEMORY/STEP2" S2
INNER JOIN box ON (S2.Id=box.Id)
LEFT OUTER JOIN boxstorage ON (S1.officeID = boxstorage.OfficeID)
LEFT OUTER JOIN currentholds_boxes ON (box.ID = currentholds_boxes.BoxID)
LEFT OUTER JOIN file ON (box.ID = file.BoxID)
LEFT OUTER JOIN currentholds_files ON (file.ID = currentholds_files.FileID)
ORDER BY box.ID
TOP 1000;

/* Not needed any more */
DROP INDEX INDEX IxBoxId ON "/MEMORY/STEP2".IxBoxId
(ID);

/* Not needed any more */
DROP TABLE "/MEMORY/STEP2";

/* Show the Result table in DBSys */
SELECT * FROM Result;

/* After use of table Result -> DROP IT */

Hello Bruno,

> Try this, I cannot test with real data but sometimes a multi-step
> script helps taking time down.

Are these queries meant to run in DBSYS? If so, it does not seem to like
the MEMORY table stuff. I get a 11949 error - Parsing error trying to work
out things like "MEMORY/STEP1".

Pardon my ignorance!

As I wrote I cannopt test on your data and there is clearly a mistake in
/* Speed up "/MEMORY/STEP2" creation */
CREATE UNIQUE INDEX IxOfficeID ON "/MEMORY/STEP1"
(OfficeID);
because it is quite likely that there will be more than one occurence of OfficeID in Step1.

Search the PDF doc (Not the help file) for memory tables. The specs say "/Memory/... not
"Memory/...

Here after you find a real query script that works and can be also executed thru a
DBISAMSql from a Delphi program.
There is my Query followed by the Query plan generated by DBSys so you get an idea of what
this Divide/Conquer strategy gives regarding timings on a table of 50'000 records.

Bruno

==================== MY QUERY ======================================

/* Extraire les enregistrements collaborateurs  m'intéressant
  Done here to be able to PATCH Collaborateurs.RecordId in the
  "/Memory/LJoin" that is the base table for the final Join.
  Collaborateurs's key is a composite of (NatObj and Col)
  but RecordId is UNIQUE */
select c.RecordId as ColRecId, c.NatObj, c.Col, c.Libelle
into "/Memory/JCol"
from Collaborateurs c
where c.NatObj=2 and (c.Col='FM' or c.Col='FG') /* Same where clause as in the next Select */
group by ColRecId;

/* Speed up "/Memory/LJoin" creation */
CREATE UNIQUE INDEX IxCol ON "/Memory/JCol" (Col);

select p.UId, p.DatePrest, jc.ColRecId, p.ColTravNatObj, p.ColTrav, p.Mandat
into "/Memory/LJoin"
from Prestations p
inner join "/Memory/JCol" jc on (p.ColTrav=jc.Col)
where p.ColTravNatObj=2 and (p.ColTrav='FM' or p.ColTrav='FG')
order by p.DatePrest;

/* Not needed any more */
DROP INDEX "/Memory/JCol".IxCol;

/* Speed up final join  */
CREATE UNIQUE INDEX IxRecId ON "/Memory/JCol" (ColRecId);

/* Create a memory table holding only the mandat's concerned by the extraction from
Prestions */
select LJ.Mandat
into "/Memory/GroupMan"
from "/Memory/LJoin" LJ
group by LJ.Mandat;

/* Pick up information Libelle from table Client */
select JM.Mandat, c.Libelle
into "/Memory/JClient"
from Clients c
inner join "/Memory/GroupMan" JM on (c.Mandat=JM.Mandat);

DROP TABLE "/Memory/GroupMan"; /* Not used any more */

/* Speed up the final JOIN time (Time divide by factor of 5 or more */
CREATE UNIQUE INDEX IxMandat ON "/Memory/JClient"  (Mandat);

select LJ.DatePrest,
      LJ.UId,
      LJ.ColTravNatObj,
      LJ.ColTrav,
      JC.Libelle,
      LJ.Mandat,
      JM.Libelle
INTO   "/Memory/Result" /* Or where I would like it to go */
FROM   "/Memory/LJoin" LJ
LEFT OUTER JOIN  "/Memory/JCol" JC on
  (LJ.ColRecId=JC.ColRecId)
LEFT OUTER JOIN "/Memory/JClient" JM on
  (LJ.Mandat=JM.Mandat)
WHERE JM.Libelle IS NULL;

/* Clean up accessory tables */
DROP TABLE "/Memory/JCol";
DROP TABLE  "/Memory/JClient";

SELECT * FROM "/Memory/Result"; /* This is what I want to see */

==================== QUERY PLAN GENERATED BY DBSYS =====================

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

/**/
select c.RecordId as ColRecId, c.NatObj, c.Col, c.Libelle
into "/Memory/JCol"
from Collaborateurs c
where c.NatObj=2 and (c.Col='FM' or c.Col='FG') /**/
group by ColRecId


/**/

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

Collaborateurs (c) table opened shared, has 36 rows

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

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

ColRecId

Result set will be ordered by the temporary index created for the grouping

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

The expression:

c.NatObj = 2 and c.Col = 'FM' or c.Col = 'FG'

is PARTIALLY-OPTIMIZED, covers 25 rows or index keys, costs 5292 bytes, and
will be applied to the Collaborateurs table (c) before any joins

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

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

CREATE UNIQUE INDEX IxCol ON "/Memory/JCol" (Col)

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

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

select p.UId, p.DatePrest, jc.ColRecId, p.ColTravNatObj, p.ColTrav, p.Mandat
into "/Memory/LJoin"
from Prestations p
inner join "/Memory/JCol" jc on (p.ColTrav=jc.Col)
where p.ColTravNatObj=2 and (p.ColTrav='FM' or p.ColTrav='FG')
order by p.DatePrest


/**/

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

Prestations (p) table opened shared, has 52110 rows
JCol (jc) 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:

DatePrest ASC

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

The expression:

p.ColTravNatObj = 2 and p.ColTrav = 'FM' or p.ColTrav = 'FG'

is PARTIALLY-OPTIMIZED, covers 52110 rows or index keys, costs 11412090 bytes,
and will be applied to the Prestations table (p) before any joins

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

The driver table is the Prestations table (p)


The Prestations table (p) is joined to the JCol table (jc) with the INNER JOIN
expression:


p.ColTrav = jc.Col

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:

p.ColTrav = jc.Col

is OPTIMIZED

================================================================================
>>>>> 19650 rows affected in 1.094 seconds
================================================================================

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

DROP INDEX "/Memory/JCol".IxCol


/**/

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

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

CREATE UNIQUE INDEX IxRecId ON "/Memory/JCol" (ColRecId)


/**/

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

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

select LJ.Mandat
into "/Memory/GroupMan"
from "/Memory/LJoin" LJ
group by LJ.Mandat


/**/

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

LJoin (LJ) table opened shared, has 19650 rows

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

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

Mandat

Result set will be ordered by the temporary index created for the grouping

================================================================================
>>>>> 571 rows affected in 0.203 seconds
================================================================================

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

select JM.Mandat, c.Libelle
into "/Memory/JClient"
from Clients c
inner join "/Memory/GroupMan" JM on (c.Mandat=JM.Mandat)

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

Clients (c) table opened shared, has 666 rows
GroupMan (JM) table opened shared, has 571 rows

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

Result set will be canned

Result set will consist of one or more rows

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

The driver table is the Clients table (c)


The Clients table (c) is joined to the GroupMan table (JM) with the INNER JOIN
expression:


c.Mandat = JM.Mandat

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:

c.Mandat = JM.Mandat

is OPTIMIZED

================================================================================
>>>>> 541 rows affected in 0.031 seconds
================================================================================

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

DROP TABLE "/Memory/GroupMan" /**/


/**/

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

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

CREATE UNIQUE INDEX IxMandat ON "/Memory/JClient"  (Mandat)

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

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

select LJ.DatePrest,

      LJ.UId,

      LJ.ColTravNatObj,

      LJ.ColTrav,

      JC.Libelle,

      LJ.Mandat,

      JM.Libelle
INTO   "/Memory/Result" /**/
FROM   "/Memory/LJoin" LJ
LEFT OUTER JOIN  "/Memory/JCol" JC on

  (LJ.ColRecId=JC.ColRecId)
LEFT OUTER JOIN "/Memory/JClient" JM on

  (LJ.Mandat=JM.Mandat)
WHERE JM.Libelle IS NULL


/**/

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

LJoin (LJ) table opened shared, has 19650 rows
JCol (JC) table opened shared, has 2 rows
JClient (JM) table opened shared, has 541 rows

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

Result set will be canned

Result set will consist of one or more rows

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

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

The driver table is the LJoin table (LJ)


The LJoin table (LJ) is joined to the JCol table (JC) with the LEFT OUTER JOIN
expression:


LJ.ColRecId = JC.ColRecId


The LJoin table (LJ) is joined to the JClient table (JM) with the LEFT OUTER
JOIN expression:


LJ.Mandat = JM.Mandat

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:

JM.Libelle IS NULL

will be applied to each candidate row in the result set as the result set is
generated due to the JClient table (JM) 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:

LJ.ColRecId = JC.ColRecId

is OPTIMIZED

The expression:

LJ.Mandat = JM.Mandat

is OPTIMIZED

================================================================================
>>>>> 305 rows affected in 0.438 seconds
================================================================================

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

DROP TABLE "/Memory/JCol"

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

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

DROP TABLE  "/Memory/JClient"

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

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

SELECT * FROM "/Memory/Result" /**/

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

Result (Result) table opened shared, has 305 rows

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

Result set will be live

Result set will consist of one or more rows

Result set will be ordered by the primary index for the table Result

================================================================================
>>>>> 305 rows affected in 0 seconds
================================================================================

Thanks for the tips. I was starting to be of the opinion that first you had
to do a CREATE TABLE on the memory tables? But your sample scripts do not
do that...


> As I wrote I cannopt test on your data and there is clearly a mistake
> in
> /* Speed up "/MEMORY/STEP2" creation */
> CREATE UNIQUE INDEX IxOfficeID ON "/MEMORY/STEP1"
> (OfficeID);
> because it is quite likely that there will be more than one occurence
> of OfficeID in Step1.
> Search the PDF doc (Not the help file) for memory tables. The specs
> say "/Memory/... not "Memory/...
>