Bulk insert into OpenEdge database table - Forum - OpenEdge Development - Progress Community

Bulk insert into OpenEdge database table

 Forum

Bulk insert into OpenEdge database table

This question is not answered

SQL Server has a network client API for inserting large numbers of rows in a single round trip to the database.  (aka bulk insert).

Is there any capability in OpenEdge for something like this?  (I'm looking for something that exists either on the ABL *or* pn the SQL92 side of things)

Bulk deletes are possible via a delete statement in SQL92.  But I'm not certain how I can accomplish bulk inserts.  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.  The performance of that will hit a limit at around ~2000 records per second.

Ideally a bulk delete and insert could be performed within a single transaction.  But that isn't strictly necessary for my current use-case.  I don't mind separate transactions if they are fast.

I see that there is a Progress DataDirect driver that supports bulk load, but it doesn't seem that this driver supports the Progress database.

Any pointers would be appreciated.

All Replies
  • @gus: So what was it you tried to say in your empty post efter the discussion of appservers (as a solution/not a solution for bulk insert closer to the database).

  • @gus: So what was it you tried to say in your empty post efter the discussion of appservers (as a solution/not a solution for bulk insert closer to the database).

  • > On Dec 17, 2019, at 8:05 AM, ske wrote:

    >

    > @gus: So what was it you tried to say in your empty post efter the discussion of appservers (as a solution/not a solution for bulk insert closer to the database).

    >

    >

    >

    what i tried to say, in reponse to ChUIMonster, was

  • @gus: Your post ends after "was". Still some problem posting?

  • bah! no idea why my posts don't work.

    chuimonster said that the classic appserver could be viewed as Progress' answer to stored procedures.

    i said that that that was a fantasy of product management.=

  • >> fantasy of product management

    I think I agree.  I suppose chuimonster's point is that some see ABL as a niche language that suits the purpose of a stored proc language like T-SQL.  But most ABL programmers are trying to accomplish lots more these days, rather than just add,edit, and delete some records.  All those other things they are trying to accomplish will eventually compete and conflict with resources that should be set aside for the database itself.

    If ABL is analogous to T-SQL then keeping it close to the database makes sense but you have to accept a lot of restrictions.  Also you have to bundle the license of ABL, so that it is an integral part of the database.

  • ABL, as a whole and in today's world, is not analogous to T-SQL.  Nor was it 25 years ago.

    But way back in the dark ages of the 90s developers were starting to see that there was a case to be made for separating layers and that it might not be such a good idea to have your UI, data access, and business logic all mushed together.  Especially if you were planning to deploy in a client-server environment.

    Way back then one approach to that problem, in other environments, was to stick a lot of business logic into "stored procedures".  (I'm not saying that that was a good approach.)

    From a certain point of view you could sort of look at Progress' Classic Appserver as a variety of that sort of thinking.

    If you're a real glutton for punishment you could also probably trace the history of operating modes with footnotes explaining how they were allegedly related to various industry trends over the next 20+ years.

    Thankfully I have no idea what product management was actually fantasizing about at the time.  I'm quite sure that I wasn't happy about any of it though ;)

    --
    Tom Bascom
    tom@wss.com