Hi there,
I'm trying workaround the missing of an EXPORT method on (dynamic) queries.
I've evaluate several ways, but none of them can seriously compete with the static EXPORT statement.
Let's consider a simple case (see attached files).
But keep in mind that the goal is to be able to handle a multi-tables query (possibly with FIELDS keyword), therefore, the KnowledgeBase's solution P108929 is not applicable.
OpenEdge 10.2B
Database: sports2000 (local connection)
Table: customer
Number of records: 23457 (see init_customer.p)
Export procedure: exports.p
Results: Export Type Duration Speed
StaticExport 766ms 31recs/ms
DynamicExport 3371ms 7recs/ms
DatasetXMLExport 5788ms 4recs/ms
Now consider a network connection on the database, and larger tables/records sizes, the difference quickly becomes a problem.
Does anyone experience a better way to perform 'dynamic' exports?
Any help or suggestion would be highly appreciated.
Thanks
[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/exports.p.zip:550:0]
[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/init_5F00_customer.p.zip:550:0]
I haven't tested your code. But from my experience I would not expect a (huge) additional difference when running the Query via network.
A static and a dynamic query usually behave even well. The difference in speed will be caused by the fact that the static EXPORT is just a single statement. The dynamic approach requires dumping each field in separate statements. That causes a lot of overhead, independent from the method the data is retrieved from the database.
Usually a good approach to improve performance of ABL applications is to limit the number of loops and statements. EXPORT is just a single statement, they dynamic approach a number of statements and an additional loop (DU iField = 1 TO bh:NUM-FIELDS).
That does also explain why the Datasets XML export is faster than your dynamic approach - even with all the overhead for building a potentially huge XML document in memory.
Thanks for your remarks, Mike.
I do understand the reason of the poor performance of the suggested 'dynamic-export' workaround: a single statement vs a bunch of code.
Like me, you believe(d) that the dataset's WRITE method would give better performances. But re-read my results, and you'll be surprised (just as I was) to see that XML (or JSON) exports is even slower than the 'dynamic-export' piece of code.
That's why I asked: Is there another way (or a more efficient 'dynamic-export' code) to experiment to speed up this process?