Databases
StackOverflow2013
Postsdbo
POFull-text indexing sluggish. Looking for alternatives
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFull-text indexing sluggish. Looking for alternatives
    primarykey
    data
    text
    <p>I have a table that I've created a Full Text Catalog on. The table has just over 6000 rows. I've added two columns to the index. The first could be considered a unique identifier of sorts and the second could be considered the content for that item (there are 11 other columns in my table that <em>aren't</em> part of the Full Text Catalog). Here is an example of a couple of rows:</p> <pre><code>TABLE: data_variables ROW unique_id label 1 A100d1 Personal preference of online shopping sites 2 A100d2 Shopping behaviors for adults in household </code></pre> <p>In my web application on the front end, I have a text box that the user can type into to get a list of items that match whatever terms they're searching for in the <code>UNIQUE ID</code> or <code>LABEL</code> columns. So, for example, if the user typed in <code>sho</code> or <code>a100</code> then a list would be populated with both of the rows above. If they typed in <code>behav</code> then a list would be populated with only row 2 above.</p> <p>This is done via an Ajax request on each <code>keyup</code>. PHP calls a Stored Procedure on the SQL server that looks like:</p> <pre><code>SELECT TOP 50 dv.id, dv.id + ': ' + dv.label, dv.type_id, dv.grouping, dv.friendly_label FROM data_variables dv WHERE (CONTAINS((dv.unique_id, dv.label), @search)) </code></pre> <p>(<code>@search</code> is the text from the user that is passed into the Stored Procedure.)</p> <p>I've noticed that this gets pretty sluggish, especially when I wasn't using <code>TOP 50</code> in the query.</p> <p>What I'm looking for is a way to speed this up either directly on the SQL Server or by abandoning the full-text indexing idea and using jQuery to search through an array of the searchable items on the client-side. I've looked a bit into the jQuery AutoComplete stuff and some other jQuery plugins for AutoComplete, but haven't yet tried to mock up anything. That would be my next step, but I wanted to check here first to see what advice I would get.</p> <p>Thanks in advance.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. UStptcat
    plurals
    1. This table or related slice is empty.
    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. POFull-text indexing sluggish. Looking for alternatives
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFull-text indexing sluggish. Looking for alternatives
      1. UStptcat
      1. VTBountyStart
    3. VO
      singulars
      1. POFull-text indexing sluggish. Looking for alternatives
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you confirm that you have measured the SQL performance alone and aren't just using your web page? If you are using the web page to test it, then lots of other things could be the issue, sure you know this, just wanting to double check. Does it get faster as you type longer search strings? If yes, this implies it isn't sql
      singulars
      1. POFull-text indexing sluggish. Looking for alternatives
      1. USrlb
    2. CO`This is done via an Ajax request on each keyup` -- is there a minimum length before this occurs? This is one of my pet peeves, so I won't rant too much, but I do hate it when the page swallows up my first few keystrokes. It would probably be better from a performance angle to wait for at least three or four characters before the full text search is invoked on keyup. The first few retrievals bring back boatloads. Even if you wait for a few characters before invoking, I would not expect this to scale very well. And I know it wouldn't work very well on a db in the cloud due to cloud latencies.
      singulars
      1. POFull-text indexing sluggish. Looking for alternatives
      1. USTim
    3. COTim: yes - it triggers after 2 keystrokes. I can't make it more because there are unique IDs that are only two characters long.
      singulars
      1. POFull-text indexing sluggish. Looking for alternatives
      1. UStptcat
 

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