Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 8 of 8 total |
fastets way to insert with DBISAM |
Tue, May 2 2006 11:47 PM | Permanent Link |
"Clive" | 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. |
Wed, May 3 2006 12:03 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, May 3 2006 3:53 PM | Permanent Link |
"Clive" | 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 > > |
Wed, May 3 2006 5:13 PM | Permanent Link |
"Clive" | 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 > > |
Thu, May 4 2006 4:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Thu, May 4 2006 7:26 PM | Permanent Link |
"Clive" | 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 > > |
Fri, May 5 2006 2:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Sun, May 7 2006 2:10 AM | Permanent Link |
"Clive" | 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 > > |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |