Hi All,
I have a requirement where I need to query with 40k input and output will be 2x or 3x of that. I have passed the batchSize as 20 and fetchTotalCount as true. I need to display the data in grid and need to export all in a excel also. But I am getting a time out error. Is there any way I can query these number of data in UI. Please help us with your suggestions.
Regards,
Kavya
Discussion posts and replies are publicly visible
Limiting the batchSize to 20 should prevent the memory issue. A timeout could still occur. Do you query data from a database view?
Can you explain your situation in more detail? That might help us to better understand and advice.
I previously used Stored procedure to query data from DB view, But we have limitation in returning data. So we use query Entity and passed 4k input and got result upto 7k. Now user wants to give 40k input and expect the result in grid. We are querying it from Oracle DB view. and pass this data to Grid and the export to excel function. Let me know if you require additional information in analysis.
Thanks
I am still not sure I understand what you are doing. Anyways, you have to make sure to keep the queried data below 1MB and the query time below the timeout. If that does not meet your requirements, you will have to find a different way.
Can you suggest any alternatives if you have any idea? That would be helpful.
What does "Now user wants to give 40k input and expect the result in grid." mean?
What about the Excel export? Dumping larger volumes of data into an Excel should not be a problem with the existing plugins.
Consider a view about millions of rows and user will be provide one particular field as input to filter from View. For low amount of data (tried upto 4k ) It is working. But the user said they will provide 40k input to search and it should query all the data. In such scenario we are getting timeout error. For excel we are using Export DSE to Excel,we are passing the data from UI and storing it in a temp table and exporting data from that temp table. So in grid and export both we need to display the resulted values. Now we are getting timeout error in query Entity.
Use index's in you table for the columns that you are querying. Use query selection to return only the columns that you want to display or export.
"millions of rows" and "query all data" is a seriously bad combination. In Appian, but also in any other environment. Why not only provide the Excel export and let users play with the data on their own machines.
Stefan , I understand your concern but the thing is In older version of appian(17.1) they used executesqlquery() plugin function to execute these queries and they got their results without any error. Since we are migrating the app to new version and the plugin is deprecated now, we are searching for ways to replace the functionality. The users are habituated with those huge searches and they expect these functionality to be available in new version also.
I understand. If you provide more details about the use case, including some screenshots, so we can really understand what this is about, we might be able to come up with options.
I have given a list of Batch Id values in a paragraph field and will click on the search button.In that we have code about the query Entity to query the results and storing in one rule input, which we will pass to grid rule as data parameter. Then the same rule input is passed as a process variable in the process flow and it is converted into a CDT structure and we will store the values in temp Table and export the data from temp table for that particular process Id. So upto 4k data we got the results but after that it is throwing time out error.
Error ss:
I would try to offload this data heavy process to a stored procedure in the database. Then show the results in a paged grid and allow the export directly from the database.