Subject : Insert efficiency tip required

Using DBISAM Engine Version 3.27 File Version 3.00

In my database I have many tables.
So far when the user adds one or two records the response is acceptable.

Now I am adding a utility which needs to insert many records into a new
database.

At the moment it takes about 1:53, that is 1 min 53 sec to create the new
database.

To isolate the bottle neck I concentrated on one table, table I.

When I add a record to the table I it is done via SQL and there are 740
records.

I thought the slowness might be due to table indexing. I dropped the index
but
the speed didn't noticeably change. Perhaps 1 sec quicker.

I then commented out the calls to write out that record. The time now became
1:22

In other words 31 sec faster. So it takes 31 sec to insert 740 records. That
is about
25 records per second.

(Admitably there is some code which gets called which calls the InsertRow
procedure.
There might be some hold up there, but I don't think so.)



This is the cut down code:-

PROCEDURE TTableIAccess.InsertRow
                        (    tableFields       : TITable;
                             DataModule        : TDataModule1;
                             SuppressAutoInc   : BOOLEAN;
                         VAR iLastAutoInc      : INTEGER;    {returned}
                         VAR response          : TResponse   {returned});

BEGIN
 TRY
   WITH DataModule.qryInsertRowIndiv DO BEGIN
     Close;
     SQL.Clear;
     SQL.Add( 'INSERT INTO INDIV' +
              ' VALUES (   :Param_IndivRecNum, ' +
              '            :Param_Sex,'+
              '            :Param_Create,'+
              '            :Param_Ident,'+
              '            :Param_NoteOverall)');

     IF SuppressAutoInc THEN
       SQL.Add( ' NOAUTOINC');


     ParamByName('Param_IndivRecNum').AsInteger :=
tableFields.ufIndivRecNum;

     ParamByName('Param_Sex').AsString          := tableFields.ufSex;

     ParamByName('Param_Create').AsDateTime     := tableFields.ufCreate;

     ParamByName('Param_NoteOverall').AsMemo    :=
tableFields.ufNoteOverall;

     ExecSQL;
     iLastAutoInc := DataModule.Indiv.LastAutoIncValue; {Return this value}
     response := resDone;
   END;
 EXCEPT
   ON E: EDBISAMEngineError DO BEGIN
     response := resError;

   END;
   ON E: Exception DO BEGIN
     response := resError;

   END
   ELSE BEGIN  {will trap EDBISAMEngineError again but doesn't matter}
     response := resError;
   END;
 END;{TRY}
END;

(In these calls SuppressAutoInc is always false.)

Question:- Is there a way to speed up the insert using SQL?

Question:- Is there some better way to insert these records?

Regards, Peter Evans

1. Do not rebuild the SQL every time, it negates the effectiveness of using
parameters. Instead, build the SQL one time (outside of your procedure), and
do an explicit prepare (Query.Prepare). Then, for each iteration, just set
the parameters and do the ExecSQL.

2. Try using starting a transaction before you start the insert loop, and
doing the commit at the end. But try the explicit prepare first, it might be
all you need. I think you will be pleasantly surprised.

Robert

"Peter" <pgevans@westnet.com.au> wrote in message
news:8FE22614-319E-4790-9BDB-5DB4E53AE80D@news.elevatesoft.com...
> Subject : Insert efficiency tip required
>
> Using DBISAM Engine Version 3.27 File Version 3.00
>
> In my database I have many tables.
> So far when the user adds one or two records the response is acceptable.
>
> Now I am adding a utility which needs to insert many records into a new
> database.
>
> At the moment it takes about 1:53, that is 1 min 53 sec to create the new
> database.
>
> To isolate the bottle neck I concentrated on one table, table I.
>
> When I add a record to the table I it is done via SQL and there are 740
> records.
>
> I thought the slowness might be due to table indexing. I dropped the index
> but
> the speed didn't noticeably change. Perhaps 1 sec quicker.
>
> I then commented out the calls to write out that record. The time now
> became 1:22
>
> In other words 31 sec faster. So it takes 31 sec to insert 740 records.
> That is about
> 25 records per second.
>
> (Admitably there is some code which gets called which calls the InsertRow
> procedure.
> There might be some hold up there, but I don't think so.)
>
>
>
> This is the cut down code:-
>
> PROCEDURE TTableIAccess.InsertRow
>                         (    tableFields       : TITable;
>                              DataModule        : TDataModule1;
>                              SuppressAutoInc   : BOOLEAN;
>                          VAR iLastAutoInc      : INTEGER;    {returned}
>                          VAR response          : TResponse   {returned});
>
> BEGIN
>  TRY
>    WITH DataModule.qryInsertRowIndiv DO BEGIN
>      Close;
>      SQL.Clear;
>      SQL.Add( 'INSERT INTO INDIV' +
>               ' VALUES (   :Param_IndivRecNum, ' +
>               '            :Param_Sex,'+
>               '            :Param_Create,'+
>               '            :Param_Ident,'+
>               '            :Param_NoteOverall)');
>
>      IF SuppressAutoInc THEN
>        SQL.Add( ' NOAUTOINC');
>
>
>      ParamByName('Param_IndivRecNum').AsInteger :=
> tableFields.ufIndivRecNum;
>
>      ParamByName('Param_Sex').AsString          := tableFields.ufSex;
>
>      ParamByName('Param_Create').AsDateTime     := tableFields.ufCreate;
>
>      ParamByName('Param_NoteOverall').AsMemo    :=
> tableFields.ufNoteOverall;
>
>      ExecSQL;
>      iLastAutoInc := DataModule.Indiv.LastAutoIncValue; {Return this
> value}
>      response := resDone;
>    END;
>  EXCEPT
>    ON E: EDBISAMEngineError DO BEGIN
>      response := resError;
>
>    END;
>    ON E: Exception DO BEGIN
>      response := resError;
>
>    END
>    ELSE BEGIN  {will trap EDBISAMEngineError again but doesn't matter}
>      response := resError;
>    END;
>  END;{TRY}
> END;
>
> (In these calls SuppressAutoInc is always false.)
>
> Question:- Is there a way to speed up the insert using SQL?
>
> Question:- Is there some better way to insert these records?
>
> Regards, Peter Evans
>

Robert,

> 1. Do not rebuild the SQL every time, it negates the effectiveness of
> using parameters. Instead, build the SQL one time (outside of your
> procedure), and do an explicit prepare (Query.Prepare). Then, for each
> iteration, just set the parameters and do the ExecSQL.
>
> 2. Try using starting a transaction before you start the insert loop, and
> doing the commit at the end. But try the explicit prepare first, it might
> be all you need. I think you will be pleasantly surprised.
>

Thank you for those tips. I will try them and let you know the results.
Regards, Peter

After following your Tip 1 I am very pleased to say that I was able to get
the utility to run 10 times faster.
Peter