[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Bulk insert into OpenEdge database table

  • Thread starter Rob Fitzpatrick
  • Start date
Status
Not open for further replies.
R

Rob Fitzpatrick

Guest
> The best I've ever been able to do is use ABL but I think that is doing about 3 round trips over the network for every record that is created. Does your bulk insert need to happen via client/server? A shared memory connection will be faster. > Ideally a bulk delete and insert could be performed within a single transaction. Why is this ideal? A single long-running transaction with a lot of notes can cause BI growth and, in the event of a crash, could take a long time (and even more BI space) to undo. > But that isn't strictly necessary for my current use-case. Can you say more about your use-case? Is this something you would do regularly in the normal course of business, or more once-in-a-blue-moon, e.g. an application upgrade scenario? Do you have the option of taking any downtime around the time of the bulk insert? There are some techniques you can leverage, though you want to carefully weigh their costs and benefits. Specifically, you can increase the BI cluster size, so you are checkpointing less frequently (but you need downtime for the truncate bi to change the cluster size before and after the insert). Or you can use buffered I/O (-r) to speed updates but this means taking a full backup before you begin (as well as DB restart before and after insert), and keeping the application offline during the bulk insert, as you never want clients doing work while you're running with -r. But it can make a mass load significantly faster. Caveat: this is a very brief description and you should research -r carefully to understand the risks of using it in production. Some links: slides: pugchallenge.org/.../321_Database_IO.pptx audio: pugchallenge.org/.../321_Database_IO.mp3 I suggest you benchmark these techniques in a non-prod environment with prod-like configuration to weigh the performance benefits. > I am sure you might have thought about this, still just wanted to share as number of transactions does not matter here. The number of transactions for a bulk insert determines the number of RL_TBGN and RL_TEND notes written to BI and AI. Fewer transactions (via transaction batching) does mean fewer of these writes. How much this matters would be a function of the size of a begin and end note, compared with the volume of notes written for one record insert and its index keys. Note, again, that BI growth may be a side-effect of transaction batching, depending on your batch size.

Continue reading...
 
Status
Not open for further replies.
Top