Hi,

I am using input auto complete from rich widgets. The textbox, on double click, should load all the records. The issue is that the volume of records is huge (approx 50k). While the SQL query fetching those records works fine, there are issues with rendering the records on UI, its pretty slow.

Any suggestions how this can be improved? I have already removed the leading '%' from the sql query and as of now, user needs to input minimum 4 characters to trigger the auto complete.

Thanks.

Dear Anuj,


I found one more forum question with similar issue.


Please have a look. There are some valuable suggestions mentioned there which will be useful for you.

https://www.outsystems.com/forums/discussion/43949/input-autocomplete-too-slow/


Regards,

Palak Patel

Thanks, Palak.

I have used like clause in the SQL query and querying just one column for distinct records. So Indexing won't be of any use here. I have already implemented minimum character length as well. Can't think of anything else.

Thanks for the link though.

Ok. 

Can you also define maximum records in output?

Because if you will render all records definitely it will be slow.


Let's say you will only show top 100 records matching with your input text. 


So in your SQL you will get only top 100 records. 


If user want to narrow down search then he/she will type more characters. 


Hope you got what I am trying to explain.


Regards,

Hi Anuj,

As per the best practice you should not bring all 50K records on the screen as this definitely leads to performance issues. You already put the 4 character restriction but i would also suggest to put maximum record restriction in both double click and search with 4 character also.


Regards,

-PJ-

Thanks, Guys.

I cannot put a restriction on the number of records as they are important from business perspective.

As of now, the 4 character limit is at least workable. I will see if  can implement something else.