Question
How to configure Auto Complete for huge amount of data
We have a requirement to configure Auto Complete that needs to query on a table with 100k records.
As the Auto Complete control reads all the data onto the clipboard and then filters the results based on the characters entered by the user, giving the maximum number of rows to retrieve in a Report Definition doesn't help. It slows down the system and there is so much of delay in getting back the data.
Indexes have already been created in the table for the columns that are being used in the Auto-Complete for search and display.
Can Auto-Complete control be used against a table with huge amount of data like 100k and it would grow in future.Please post some suggestions to handle this scenario.
Hi,
Thank you for providing your input. The client is not ready to have more than 1 auto complete in this scenario and we have kept the minimum number of search character to 3 but still querying against 100k of records is taking time to get back the results.
As auto complete fetches the data from table and then filters on top of it, I have been trying to figure out if there is a way to give the user entered value in the filter criteria of report definition which would fetch the results accordingly. Please let me know if you have tried this before.
Hi Anusha,
You can look into the local change provided in this support article for your requirement dealing with huge data for auto complete.
Regards
Mahesh M
Hi Mahesh,
Thank you so much for providing the details. I was just able to try this approach using data pages.
Hi Anusha,
I tried to create a sample use case with Report definition and able to perform the search based on the parameter as explained in the attachment.
Regards
Mahesh M
You're welcome, kindly update the thread if that resolved your issue. Thanks
Accepted Solution
The issue has been resolved by parameterizing the data page or report definition for Auto-Complete.
hi
If there are a large number of records then check if it can be splitted into some categories and then use 2 , 3 autocomplete to show complete data ,using paramterized Data pages .For example select country then states and then based on the states the list of cities (it will use 3 data pages).
And the minimum number of search characters, keep it 3.