locked
Look up Combo with million of records RRS feed

  • Question

  • Hi ... Again ...

    I am wondering to create the control that with look up.

    Like POS in supermarket , I want to write a control like combo box, where the user can type anything. While the user is typing in the combo box, it has to show the drop down with the suggestion in it.

    The suggestion list data will be coming from the database. It might be about 1 million records in it.

    Normal Combo can hang down with that amount of data.

    I would like to have an idea for loop up combo

    Regard
    Hater Clay


    Wednesday, July 18, 2007 2:40 AM

Answers

  •  

    Hater,

     

    That would be pretty inefficient a thing to do.

     

    Most users also probably would not want to wade through as much information as that to make a selection, I would imagine that 10 to 20 possibilities would generally be enough for the dropdown.

     

    So regardless of what you do with the dropdown, you really need to make sure that you limit the amount of data that is returned to a reasonable amount.  Do this by selecting the top 10 or 20 from the database.

     

    Other recommendations would be to have a button to click instead, where the data is then retrieved, to significantly reduce the amount of traffic to the application.

     

    Another way, if you really have to have this functionality, and I seriously would consider your reasons for requesting it in the first place, would be to have a time that runs from the textchanged event of the combo, then have it fire after 500ms or so, which will at least cut down on the traffic.  What I mean there is start the timer when the text changes, if it is already running, stop it, and start it again.  Then response to the elapsed event.  However, as you can probably tell from my description of what to do, this isn't going to be a particularly smart of clean solution.

     

    I hope this helps you to re-assess your position with the application, and its requirements, especially if it is a POS system, as you might end up upsettign a few people with the traffic, and speed of the application!

     

    Cheers,

     

    Martin.

    Wednesday, July 18, 2007 3:26 AM
  • I don't think it's undoable to implement a single list that as you type into it suggestions are returned,  even with a million records to query.   After typing a few letters you've probably filtered down your result set considerably.  I'd suggest limiting the frequency of the callback to the server for the list, as well as the maximum number of records returned. Here's  a link showing how to do it with ASP.NET AJAX: http://www.asp.net/learn/videos/view.aspx?tabid=63&id=122

     

    Alternatively,  you might consider breaking it down to smaller chunks via category / subcategory / item type combo boxes.  Here's another link: http://www.asp.net/learn/videos/view.aspx?tabid=63&id=77Alternatively,  you might consider breaking it down to smaller chunks via category / subcategory / item type combo boxes.  Again, here's a link: http://www.asp.net/learn/videos/view.aspx?tabid=63&id=77

     

    If you do go with the AutoComplete control just be aware that querying against that much on a regular basis could be a scaleability bottleneck in your application.

    Friday, July 20, 2007 1:48 AM

All replies

  •  

    Hater,

     

    That would be pretty inefficient a thing to do.

     

    Most users also probably would not want to wade through as much information as that to make a selection, I would imagine that 10 to 20 possibilities would generally be enough for the dropdown.

     

    So regardless of what you do with the dropdown, you really need to make sure that you limit the amount of data that is returned to a reasonable amount.  Do this by selecting the top 10 or 20 from the database.

     

    Other recommendations would be to have a button to click instead, where the data is then retrieved, to significantly reduce the amount of traffic to the application.

     

    Another way, if you really have to have this functionality, and I seriously would consider your reasons for requesting it in the first place, would be to have a time that runs from the textchanged event of the combo, then have it fire after 500ms or so, which will at least cut down on the traffic.  What I mean there is start the timer when the text changes, if it is already running, stop it, and start it again.  Then response to the elapsed event.  However, as you can probably tell from my description of what to do, this isn't going to be a particularly smart of clean solution.

     

    I hope this helps you to re-assess your position with the application, and its requirements, especially if it is a POS system, as you might end up upsettign a few people with the traffic, and speed of the application!

     

    Cheers,

     

    Martin.

    Wednesday, July 18, 2007 3:26 AM
  • Hater,

     

    I agree with Martin on this one it would seem rather difficult to implement efficiently enough not to annoy an end user.

     

    But, if you are going to go ahead with a prototype I presume you are going to have all the stock items loaded into an memory as a custom collection or data set - 'all stock items', have you consider using a 'popular stock items' collection which would be a subset of the main collection that represents the most popular items according to heuristic you define (e.g. most popular items over 60 minute period), you would then search the 'popular stock items' collection first and then revert to the searching the 'all stock items' collection if not found in the 'popular stock items'.

    The thinking behind this is the '80-20 rule' or to be more formal the 'Pareto principle' - in this case 80% of the time people will buy only 20% of the different stock items .

    The problem with this is then how do you capture the 'popular stock items' from different machines running the application and how do you keep the 'all stock items' collection uptodate, remember they would all be seperate processes that would require some kind of distributed communication to keep the 'popular stock items' collections uptodate.

     

    Also how your collections are sorted and more specifically what you use to distinguish the stock items and allow the end user to search on will have a great affect on performance.

     

    I presume we are talking about a desktop application, so the actual implementation of the mechanism can be done on a back ground worker thread that is initiated by user driven event (button press), timer etc... 

     

    HTH

     

    Ollie Riches

     

     

     

    Wednesday, July 18, 2007 9:05 AM
  • I don't think it's undoable to implement a single list that as you type into it suggestions are returned,  even with a million records to query.   After typing a few letters you've probably filtered down your result set considerably.  I'd suggest limiting the frequency of the callback to the server for the list, as well as the maximum number of records returned. Here's  a link showing how to do it with ASP.NET AJAX: http://www.asp.net/learn/videos/view.aspx?tabid=63&id=122

     

    Alternatively,  you might consider breaking it down to smaller chunks via category / subcategory / item type combo boxes.  Here's another link: http://www.asp.net/learn/videos/view.aspx?tabid=63&id=77Alternatively,  you might consider breaking it down to smaller chunks via category / subcategory / item type combo boxes.  Again, here's a link: http://www.asp.net/learn/videos/view.aspx?tabid=63&id=77

     

    If you do go with the AutoComplete control just be aware that querying against that much on a regular basis could be a scaleability bottleneck in your application.

    Friday, July 20, 2007 1:48 AM
  • Well, there can be different approaches ...

    One old approach (as one said earlier too) ... provide a button and on clicking of button a grid appear from which user can select ...

    Another approach ... on the basis of user inputs generate queries ... means on each key stroke from user .... AJAX can help in implementing this approach but too many queries in result of network load...

    A custom/user define control ... that shows list like usual combo box .. but with show button .... so when user press "show" ... data populate in list...
    Monday, July 23, 2007 1:23 PM