Does anyone know of anyway of inserting records faster than with either
INSERT statements or preprepared querys with parameters?

I insert many thousands of records and would love to pick up some
performance in this area, Inserts are currently a mix of these 2 types of
inserts

insert into table
select values from table b

and

insert into table(col,col1)
values(1,2)


Is there anything faster?..

Cheers
Clive.

Clive,

<< Does anyone know of anyway of inserting records faster than with either
INSERT statements or preprepared querys with parameters? >>

Are you wrapping up the inserts into transactions ?  That usually provides
the biggest performance boost.

--
Tim Young
Elevate Software
www.elevatesoft.com

Really, I didnt know that!.. Will give it a go..

Cheers
Clive.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:66BBB621-94AA-41FF-9531-9A8E25DD09B7@news.elevatesoft.com...
> Clive,
>
> << Does anyone know of anyway of inserting records faster than with either
> INSERT statements or preprepared querys with parameters? >>
>
> Are you wrapping up the inserts into transactions ?  That usually provides
> the biggest performance boost.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

I implementated this and I do get some performance gains, not big but worth
it.

Question though, all my SQL inserts are in a thread, however it appears that
when I do a db.commit it affects the main thread, ie it pauses it slightly,
is this correct behaviour?.  The thread has its own DB instance, but I
wonder if a syncronise is occuring to the dbengine or something that causes
this pause?.

thanks
Clive.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:66BBB621-94AA-41FF-9531-9A8E25DD09B7@news.elevatesoft.com...
> Clive,
>
> << Does anyone know of anyway of inserting records faster than with either
> INSERT statements or preprepared querys with parameters? >>
>
> Are you wrapping up the inserts into transactions ?  That usually provides
> the biggest performance boost.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Clive,

<< I implementated this and I do get some performance gains, not big but
worth it. >>

How many inserts are you wrapping up in each transaction start/commit block
?  Also, are you using the default Commit with the ForceFlush parameter set
to True ?  If so, try Commit(False) instead on all Commit calls except for
the last one.  That will prevent unnecessary flushing to disk at the OS
level.

<<  Question though, all my SQL inserts are in a thread, however it appears
that when I do a db.commit it affects the main thread, ie it pauses it
slightly, is this correct behaviour?.  The thread has its own DB instance,
but I wonder if a syncronise is occuring to the dbengine or something that
causes this pause?. >>

No Synchronize calls in DBISAM, however could it be that the main thread is
attempting a read on the same table ?  If so, then the Commit will block it
for a short time.

--
Tim Young
Elevate Software
www.elevatesoft.com

I used commit(FALSE).

Probably circa 10000 rows between commits, I tried committing at more
regular intervals but didnt make much difference,

My app isnt "supposed" to try and access the data until its complete, will
check that out more as it doesnt mean its not



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:D5936E32-562E-41F9-84CF-C214339BE2E9@news.elevatesoft.com...
> Clive,
>
> << I implementated this and I do get some performance gains, not big but
> worth it. >>
>
> How many inserts are you wrapping up in each transaction start/commit
> block ?  Also, are you using the default Commit with the ForceFlush
> parameter set to True ?  If so, try Commit(False) instead on all Commit
> calls except for the last one.  That will prevent unnecessary flushing to
> disk at the OS level.
>
> <<  Question though, all my SQL inserts are in a thread, however it
> appears that when I do a db.commit it affects the main thread, ie it
> pauses it slightly, is this correct behaviour?.  The thread has its own DB
> instance, but I wonder if a syncronise is occuring to the dbengine or
> something that causes this pause?. >>
>
> No Synchronize calls in DBISAM, however could it be that the main thread
> is attempting a read on the same table ?  If so, then the Commit will
> block it for a short time.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Clive,

<< I used commit(FALSE).

Probably circa 10000 rows between commits, I tried committing at more
regular intervals but didnt make much difference, >>

Hmm, that should do the trick.  What kinds of insert times are you seeing
(records/second) in this configuration ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Found the problem with the Pausing, will be no news to you!, But just for
the lurkers.

Basically

Thread starts, does a db.starttransaction

Current application is running, user attempts to update a field and the
application waits until the thread issues a COMMIT as StartTransaction
effectively locks all tables to other sessions.

Solution was to just lock the tables accessed in my thread using the
StartTransaction(TStringList) option.

Seems to be fine now

Cheers
Clive

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:D5936E32-562E-41F9-84CF-C214339BE2E9@news.elevatesoft.com...
> Clive,
>
> << I implementated this and I do get some performance gains, not big but
> worth it. >>
>
> How many inserts are you wrapping up in each transaction start/commit
> block ?  Also, are you using the default Commit with the ForceFlush
> parameter set to True ?  If so, try Commit(False) instead on all Commit
> calls except for the last one.  That will prevent unnecessary flushing to
> disk at the OS level.
>
> <<  Question though, all my SQL inserts are in a thread, however it
> appears that when I do a db.commit it affects the main thread, ie it
> pauses it slightly, is this correct behaviour?.  The thread has its own DB
> instance, but I wonder if a syncronise is occuring to the dbengine or
> something that causes this pause?. >>
>
> No Synchronize calls in DBISAM, however could it be that the main thread
> is attempting a read on the same table ?  If so, then the Commit will
> block it for a short time.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>