Hi Everyone,
I have a requirement where in need to fetch more than 20K records from the database and then store it in Excel file,
As of now we are querying all the data once and writing it into a single excel file, which is taking more than 10-15 mins.
Can anyone suggest how we can break this into multiple excel file using the process model i,e if there are 100 records in the databse then we should be able to get 10 excel file with 10-10 records in each file.
Discussion posts and replies are publicly visible
What exactly are you doing? 20K records is not that much, you should be able to use Export to Excel node to quickly export it.
Hi Mike,
In the Record we have given one link to export the overall data from the table into one excel file, its taking approx 10-15 mins as of now.
So I have thought if we can set some batch size limit and get the different different files for that set of data.
For example:
If in the records(table) we have 100 data and we have set the batch size to 10 then 10 different excel files should get generated once we are clicking the export button.
How are you generating the link? How are you generating the document?
Its a normal link that is already provided in the record.
What we need is to create a process model which can generate multiple excel file with a given no of records in each file.
Hi @gauravs0002 I agree with the points highlighted by Mike Cichy, 20K records are not that huge, and hence Appian should be able to export it in less amount of time.
I have couple of quick questions, to understand why it's taking that long for you to export the Data into excel.
Also it would be worth having a look into your Server configuration, because i have seen some cases where the server configuration was low and hence while performing any major job, server used to perform slower than expected.
Also, i would recommend to monitor the SQL Active Sessions, while performing this operation, just to make sure that, some other jobs / sessions are not active (as part of some other process) when you have started the export of data into Excel.
Hope this will help you in debugging and resolving the issue.
HI Alok,
Can you please guide me on how to check the server configuration,we are using cloud based platform.Also the monitoring of SQL Active Session.
Hi Gaurav,
You need to work with your server administrator to find the server configuration details.
Also related to monitoring the Active SQL sessions / active queries, it depends upon which database you are working with, like:
MySQL: It would be worth executing the following query: SHOW FULL PROCESSLIST;
/* This will show all the queries running for 5 seconds or more in MySQL */ SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME >= 5;
Oracle: You do have the option to monitor the session if you are using SQL Developer available in your machine by selecting the following options:
Tools > Monitor Sessions > In the Select Connection dialog box, select a connection to SYSTEM (or another account with full DBA privileges) > And also choose the Status as Active
This will show the list of SQL active session currently active / running.
Hope this will help.
True.
If it is the view that is fetching the data then you can make it faster by providing index to the columns. That did help me.
Thank You!
Thanks for your valuable solutions.
Considering a small scenario is there any way possible by which i can generate multiple files with with given batch size.
I was thinking to proceed in this way:
I am getting the batchof records in cdt, since export cdt to excel is now deprecated i am not able to export the batch of data into the excel