I have about 30K records for 200 partners on 50 documents in TN for a day.
Requirement is to analyze and report on these documents.
Is it a good idea to query on these tables and process each record ( select 10 fields which are needed for analysis) and dump them into a staging table ?from where my web team can create reports.
Do you have to report any custom attributes/fields a part from TN transaction analysis fields?
yes query the TN db…what you can do is just select what ever criteria you want to report based on the doc type/partner id’s and Transactions tab (TNC)–>view SQL for that selection criteria and then customize that sql query via Adapter Service per your reporting needs and schedule this wrapper that calls the above AS.
Yeah I do have the custom fields/attributes and I have built the query and is running fine except for its takes long runtimes.
But the question is it a good idea to select all records( 30 k or so) and bring into the memory all @ once or shud I do multiple queries (on partners/docs this wud be quite a few if I go by partnersand also will make the runtime for too long as it quering the tables multiple times). And also when I insert into the staging table ,can I insert all of them as batch insert.
More looking into the best practice scenerio.
Appreciate your time.
No its not a good idea to keep all in memory and then query/report:
Yes you can do batch insert SQL:
One option to consider would be to replicate the TN DB tables and then query the replicated tables using reporting tools.