IDX GE Centricity Best strategy for huge extraction
I'm looking for the best strategy for extracting data from a Cache table that houses 86 Million lines of data and around 190 columns. I'm currently using DBeaver and the Invoice Service dates go back to 1996. Optimistically I'm looking to pull to a pipe delimited flat file, but even a year's worth of data takes many hours to get query results.
Any suggestions for the best practice/strategy?
Thanks,
John
Comments
Extracting such amount of data with using JDBC/ODBC, will be much slower than any sort of native access in Cache.
InterSystems Cache offers a way to export the whole table, with selected columns
Open System Management Portal, System Explorer, SQL. Switch to the desired namespace. Click on Wizards and select Data Export
The next steps should be pretty much simple, just select what you need to export and starts in the background. So, you will be able to control how it is going.
Look at the documentation for more details
The IDX system is oftentimes partitioned by Group(GRP). Additionally I suspect the 86M records do not represent invoices for a single year. Using %SYSTEM.WorkMgr you could break the job up in to smaller jobs by GRP and or InvCrePd or YEAR(BAR.Invoice.SerDt)