Is it possible to export more than one lakh records using records approach

Question

HI 
 
 I have a requirement to export more than one lakh records into excel sheet. 
 challenges: 
 -> Data is coming from a view having multiple joins.( Facing performance issue at production) 
 -> So we followed appian records approach, but export to excel button is getting disabled when record count is more than one lakh. 
 -> As the records count is high, we are not interested to go stored pro and staging table approach 
 Any advice is helpful

Mike Schmitt · Answer

Just as a side note, we don't have the term "lakh" most other english-speaking places that I know of, so your question will probably be clearest to the most number of other users here if you just write out "hundred thousand" or "100,000", etc. 
 To your question: you have 2 potential approaches that I know of which might work. 
 
 use the old Excel Tools plug-in and CDT to Excel, and batch your export (i.e. 1000 rows at a time); or 
 use the newer Export DSE to Excel smart service which handles paging (but has several other sorely-missing features).

Mike Schmitt · Answer

Mr_AR said: but while using CDT to excel plugin , it is occupying much java heap memory 
 As I mentioned below , you would need to manually implement paging while using this technique - i.e. only loading a certain amount of information into the process per pass, and updating the same file with your iterative passes.

Mike Schmitt · Answer

I'd suggest maybe smaller batches. And maybe clearing out your process variables data between iterations, if you didn't already try that. And sadly, the CDT to Excel smart service may just be prone to this - i think that's one of the reasons it was deprecated. 
 I don't think SQL to Excel can deal with a stored procedure. Your best bet would be probably to abandon the stored procedure concept and instead query from a view. If your logic can't be accomplished by a view, then i'd bet that the proper alternative approach would be to have your stored procedure populate a temporary staging table, then query from that. It looks like you may have already said you don't want to do this approach, but if it's the only working approach, the fact that you don't want to won't change things much ;-)

Is it possible to export more than one lakh records using records approach

Top Replies