Question

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

Forum|Forum|3 years ago
October 26, 2022
10 replies
0 views

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

richardm900458

Hi buddy,
try to use english terms that everyone can support you. lakh is indian ;)

you can use multiple other approaches:
you can add a view as data store entity.
https://docs.appian.com/suite/help/22.3/Export_To_Excel_Smart_Service.html
or
https://docs.appian.com/suite/help/22.3/Export_To_CSV_Smart_Service.html

and for the record export, you can fine some best practices here:
https://docs.appian.com/suite/help/22.2/Optimizing_Record_Lists_for_Export_to_Excel.html

is this helping you?

ajk0002Author

Hi [mention:c03f69cc9b264af29d5e31ca80c64eff:e9ed411860ed4f2ba0265705b8793d05]

Thanks for your immediate response.

This was an existing project , we are not getting all values into database view.

-> we used stored proc to get required data, but while using CDT to excel plugin , it is occupying much java heap memory .

richardm900458

did you just one of the mentioned smart services?

mikes0011

Brainy

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).

The Expression Guru

ajk0002Author

Hi [mention:b45a4f6a20b14a008e4e0ec732a8bf98:e9ed411860ed4f2ba0265705b8793d05]

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)

mikes0011

Brainy

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 ;-)

The Expression Guru

Sign up

Login with SSO

Login to the community

Login with SSO