Last post Apr 17, 2012 01:51 AM by Pankaj.Sharma
Apr 16, 2012 01:40 AM|jubin|LINK
So I have a requirement to implement an AutoComplete of names for a person search on my intranet web site.
So each character typed by an end user is going to fire an Ajax request to my ASP.Net handler which returns user name suggestions as JSON.
Total employees is about 50000 and their names is about all the data I will need available on the server side to search thru and create the JSON response.
Does it make sense to read this list of names from Database once and have them stored on the server side in memory so as to make the responses faster or would you go and read the list of usernames from SQL server database every single time (pooling enabled!)
Just want to know I am approaching this from the right direction. DB is sql server 2008 and server side is ASP.Net 2.0
Apr 16, 2012 02:30 AM|Pankaj.Sharma|LINK
I think, you are approaching it right way. But not the way you are going to store them at server side in memory. This will create a performance overhead in a long run as you are suggesting that there are 50000 employees names in DB, and possibly could be
I would suggest you to display a certain number of entries each time user presses a key say 20. So if user presses a key any time display first 20 names matching with the letters typed in by user. I mean suppose user presses P then starting showing 20 names
starting with P, then user presses letter 'a' making it 'Pa' the display all names matching Pa, so on.
Hope this help you decide the apporach.
Apr 16, 2012 12:14 PM|jubin|LINK
Agree. I was planning to limit the number of choices to 10 int eh auto complete list.
But my question is more about the server side which has to return these ten entries matching the partially typed name entry on the client side.
Do I go and query DB for top 10 matches for whatever the suer typed into the text field or do i query an in-memory List/Structure (which is preloaded with names of all 50000 employees and only reloaded once a day(configurable)).
Apr 16, 2012 12:26 PM|Dan Bracuk|LINK
You could force the user to type more than 1 character before running your search. We have a app that does the same sort of thing, and the autocomplete kicks in on the third keystroke.
Apr 16, 2012 10:44 PM|jubin|LINK
True. I will only start searching after 3 characters are typed in. But again the question remains on the server side - for every search, do I query the database to find top 10 matching names or do I query and get list of all employees once a day and store
it in memory and query against that list
Apr 17, 2012 01:51 AM|Pankaj.Sharma|LINK
Actually the exact implementation depends on the availablity of the resources, i mean if the application is deployed on your own server and you have more than enough memory then its not an issue to keep the list in memory. But if the app is going to be deployed
on a shared/third party server, you will have to be very cautious of memory consumption.
Although there is nothing wrong in your approach. But i would suggest a slightly different approach. Instead of keeping a long list of 50K names in memory, keep alphabet wise list in memory and that too if names starting with that alphabet is being queried.
Basically, on the start of the application suppose user types Tim so hit the data base load all the names starting with T and keep it in a key based collection like hashtable. Next time other user types John so first look up in memory collection if a list
for J exists then return the names from this collection else hit the database and hold the list of names starting from J.