Skip to main content

Hi Everyone,

We're populating a table with over 9 million addresses. These addresses serve an auto-complete lookup. Whenever a user starts typing, the lookup displays results that match what the user typed thusfar. This worked great when we had 10 addresses in the database. Now that the database is filled with over a million addresses, the lookup grinds to a halt whenever the user starts to type, uses the caret or clicks the looking glass to open the selection form. How do we handle tables containing a large amount of records with an auto-complete lookup?

The database is still getting filled. Once filled, we'll create an index on the table. This will speed it up. What other things can we do to keep the UI user-friendly and responsive? Can the looking glass and/or the caret be removed from the field, for instance?

We're using Universal, but the Windows GUI suffers from the same lag. Our database is an Azure hosted database.

Thanks,
- Alex.

Why wait with adding the index until its filled? You can enable the index and it will be build while filling, you might need to do some rebuilding but doing regular maintenance on indexes is smart in any situation. Azure will create indexes automatically if you enable that feature but that will take time before they recognize regular queries and suggest indexes.

Since you are doing text searches you probably also benefit from a full-text index.

Opening the table using the hourglass, you can use paging to only load the data of a single page within the opened popup. Filtering could be used to get to the desired result there.

Talking about the dropdown, we support two types of controls. Comboboxes will loading all options directly. A suggestion control will trigger the user to type a key-word first. This last option is the best option on a data set like this.

Now we reduced the amount of data transferred to the client. When the call remains slow, indexes on the database or prefiltering will indeed optimize the call for data. 

Why wait with adding the index until its filled? You can enable the index and it will be build while filling, you might need to do some rebuilding but doing regular maintenance on indexes is smart in any situation. Azure will create indexes automatically if you enable that feature but that will take time before they recognize regular queries and suggest indexes.

Since you are doing text searches you probably also benefit from a full-text index.


Thanks André! We held off creating the index until all data would be inserted, which is adviced in a couple of blogs we were reading about it. Azure recommended an index, the index is being made at this moment. Thanks! We'll recreate the index when all is done, and we'll add the full-text index as well.

Opening the table using the hourglass, you can use paging to only load the data of a single page within the opened popup. Filtering could be used to get to the desired result there.

Talking about the dropdown, we support two types of controls. Comboboxes will loading all options directly. A suggestion control will trigger the user to type a key-word first. This last option is the best option on a data set like this.

Now we reduced the amount of data transferred to the client. When the call remains slow, indexes on the database or prefiltering will indeed optimize the call for data. 



Thanks Erik! Playing with the suggestion control now.

Hey @Alex Kerschkamp, Have the replies helped you out or do you need more help regarding the performance?

Hi Mark,

The addresses are still importing. Adding the indexes to the table resulted in the record insertions lasting ten times as long somehow 🐢 When the insertions are done, somewhere next month,  we'll be looking at this issue again. One option is to generate a full address column for each address, so we can use the "contains" suggestion combo on one single column. But, like I said, we'll be looking at this once we have the address table full and complete. To be continued 🙂 ...

Best regard,
- Alex.

Terms & ConditionsCookie settings