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
Discussion posts and replies are publicly visible
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.
Hi Mike Schmitt
Thanks for your immediate response.
I have used first approach, where I get data from stored proc , using loop concept and passing batches(10000) of data to CDT to Excel smart service,
but faced java heap issue and my environment get shut down:)
Any suggestions on how to use SQL to Excel plugin (How to call stored proc using this plugin)
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 ;-)