We have a few tables in a SQL database (2014). We connect using the dataserver from an HPUX running OpenEdge 10.2.
The only keys on these tables are the primary field (an int).
For some reason, given the below # of records, deleting seems to take a VERY long time. Writing to other tables is also slow, but I'd like to get this sorted out first. For some reason, one table is faster than the other.
delete bizordhdr
9min 21sec (10000 records, 64 columns)
delete BizScaleOrders
26sec (8000 records, 16 columns)
Frankly, 26 seconds is still too long for me, especially considering the size isn't that large. Is there something we need to do to optimize these commands?
If it helps I can post the specific table structure for each one.
The ABL code will result in a lot of operations being performed to complete the request, namely:
a) query all records in the table, and then
b) issue a DELETE FROM query for each and every record.
So 10000 DELETE FROM queries in your scenario.
Why not use the ABL "RUN STORED-PROCEDURE" statement and execute a SQL DELETE FROM query directly on SQL Server? For example,
RUN STORED-PROCEDURE send-sql-statement ("delete from dbName.OrderHeader").
This statement will execute the DELETE FROM query server-side and in a single action.
those times are very slow. can you share details of how you delete the rows?
Is it really possible to install MS SQL dataserver on HP-UX?!
I'm a bit shocked... Question - What are the client libraries that are used to communicate with MS SQL? Does Microsoft write them?
I had thought the SQL dataserver would only be available to Progress customers on Windows.
Dataserver can be running in remote/broker mode on a Windows machine (typically the one also hosting the SQL Server instance).
OpenEdge clients on Unix can then connect to the dataserver.
It's fairly simple. After connecting to the remote dataserver I run:
DEFINE BUFFER BizOrdHdr FOR dbName.OrderHeader.
FOR EACH BizOrdHdr:
DELETE BizOrdHdr.
END.
The configuration you described might also be the reason for the slowness described by the original poster.
I'm assuming you are saying that all database operations have to go from HP-UX (ABL client) to a remote OE dataserver/broker (Windows) to the SQL Server database itself (Windows). It sounds to me that the performance would always be slower than a more conventional connection to a database.
In any case, there are more points of failure, where performance is concerned.
Can you just test performance by running the ABL client on the *same* Windows server where the OE dataserver sits? That would take away the additional network hop *and* take the HP-UX platform out of the equation. (My experience with HP-UX is that you shouldn't settle for what you get until after you see the similar performance results in Windows or Linux. I've had a number of HP-UX-specific issues over the years. Especially platform-specific performance issues in HP-UX. You need to reference your performance results against another non-HP-UX platform. You may need to use a 10-year-old x64 chip in Windows/Linux to keep keep it fair, since your HP-UX probably uses an older IA64.)
The ABL code will result in a lot of operations being performed to complete the request, namely:
a) query all records in the table, and then
b) issue a DELETE FROM query for each and every record.
So 10000 DELETE FROM queries in your scenario.
Why not use the ABL "RUN STORED-PROCEDURE" statement and execute a SQL DELETE FROM query directly on SQL Server? For example,
RUN STORED-PROCEDURE send-sql-statement ("delete from dbName.OrderHeader").
This statement will execute the DELETE FROM query server-side and in a single action.
Thanks Rob, it seems that running the statements as SQL commands speeds it up no end. 1 second to delete (rounded up I assume) and about 15-20 seconds to write 10-12K records, which I can live with.
The only issue I'm having now is handling the SQL errors that may be generated (not progress errors, so NO-ERROR is not working). I figured that should be a separate question, so I asked it here: community.progress.com/.../29426. Any help you can provide would be
appreciated.
We're considering moving the tables into progress, and giving the other system a driver and ODBC connection, as that may be more reliable since the data originates with Progress, but I'll look into that later.