Posted: 16 Jan 2019 10:01 EST Last activity: 31 Jan 2019 16:28 EST
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.
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.
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.