Databases
StackOverflow2013
Postsdbo
POjQuery autocomplete - optimization question
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POjQuery autocomplete - optimization question
    primarykey
    data
    text
    <p>would love your thoughts on this one.</p> <p>I am using jQuery autocomplete in a ASP.NET MVC application to retrieve a list of records from a database - I would like to know if there is a better way than the way I am currently doing it - as follows:</p> <p>Here is the jQuery (I am calling an action method 'GetRecordByName' with the autocomplete)</p> <pre><code> $('#tb_name').autocomplete({ source: 'Home/GetRecordByName', minLength: 1, delay: 0, select: function (event, ui) { // do stuff } }); </code></pre> <p>On the back end, I am using Linq to Entities in the method to obtain the results from the database:</p> <pre><code>public JsonResult GetRecordByName(string term) { var data = records .Where(dr =&gt; dr.Key.StartsWith(term)) .Select(dr =&gt; new { dr, value = dr.Key }) .Take(5); return Json(data, JsonRequestBehavior.AllowGet); } </code></pre> <p>Currently, it's running a bit slow (~ 1-2 seconds). Basically, the DB has tens of thousands of records in it and each time you type a character in the text box, the app hits the database. I can set it to 2 or 3 characters, but it doesn't change the speed, nor do I want to require that many characters.</p> <p>I have an idea on how to optimize it: On pageload, get all possible results from the DB and send it to the client (via a hidden input or a javascript data structure), and have autocomplete use that client side data as the source. I'm sure that would be lightning fast, but it seems weird to me - are there are any drawbacks to doing it that way? </p> <p>Are there any other ways to achieve a faster autocomplete?</p> <p>UPDATE: Ok, apparently the problem was the way I was constructing my EF call. The EF call off of 'records' ultimately wasn't constructing a WHERE clause in the sql query; it was getting the whole table each time, and then the linq methods were going off that already-enumerated mass of data - this was occurring each time a letter was typed - causing the slowdown. Doh! I moved the .Where method to the data repository layer where it is enumerating after the where filters the results, and returning a Dictionary - seems to work great now. Thanks for all your help! I marked the most helpful answer which helped me investigate the issue.</p> <p>As far as the Flickr solution / bringing the whole set of data to the client, I still feel like that's weird / overkill, but I'm sure it's warranted with certain large datasets. Also will be looking more into OutputCache for other things.</p> <p>Thanks again!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdonnovan9
    1. USdonnovan9
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POjQuery autocomplete - optimization question
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POjQuery autocomplete - optimization question
      1. USpixelbobby
      1. VTFavorite
    3. VO
      singulars
      1. POjQuery autocomplete - optimization question
      1. This table or related slice is empty.
      1. VTUpMod
    1. COForgot to mention, using a ILookup in the back [private readonly ILookup<string, RecordsTable> records = Repository.GetDirectoryRecordsAll();]
      singulars
      1. POjQuery autocomplete - optimization question
      1. USdonnovan9
    2. COYou guys, thank you very much for your responses! I am looking at various options and will let you know what the best solution was for my scenario
      singulars
      1. POjQuery autocomplete - optimization question
      1. USdonnovan9
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload