none
Search performance - how to improve? RRS feed

  • Question

  • Hi everyone,

     

    I'm looking to try and improve the search performance over one of my catalogs.  The CatalogProducts table has about 86k rows.  I've got about 20 columns flagged as "FreeTextSearchable", and performance is less than ideal.

    My suspicion is that it has to do with the number of colums I've selected as searchable, but since I want to search all of these columns I need an alternative. 

    Do I create a single "web search phrase" bucket in my catalog to contain all of this info and then run the search over only that column?

     

    Thanks

     

    Rob

     

    Monday, May 5, 2008 6:08 PM

Answers

  • I wasn't aware of the performance doc before you mentioned it Max, and I found a note on page 10 of the document that mentioned the need to update the stat after data import operations (like building a catalog with the api).

    I ran this:

    UPDATE STATISTICS cb_productcatalog.dbo.S08_CatalogProducts WITH FULLSCAN

     

    Simply updating the stats took my search query times for various phrases from 15s/60s/21s/etc down to subsecond for all of them.  One more thing to do after building a catalog, but definitely worth it.

     

    Thanks again Max!

     

    Rob

    Monday, May 5, 2008 7:10 PM

All replies

  • If you read the performance doc you will notice that there were only three columns Free Text Searchable. The more fields marked as Free Text the more performance will suffer. Your only choice if you want to use the APIs is to do what you have mentioned create one column then put your data into that.

     

    -Max

     

    Monday, May 5, 2008 6:33 PM
  • I'll give the consolidated search column a try and see how it goes.

    Is this the doc you are referring to?  http://download.microsoft.com/download/3/7/e/37e3ce07-fe20-4e5b-9858-68b3718638d7/CS07PerfGuide.doc or is there another one?

    Any idea as to the size of the search column - smaller = better?

     

    Thanks Max!

     

    Rob

    Monday, May 5, 2008 6:52 PM
  • I wasn't aware of the performance doc before you mentioned it Max, and I found a note on page 10 of the document that mentioned the need to update the stat after data import operations (like building a catalog with the api).

    I ran this:

    UPDATE STATISTICS cb_productcatalog.dbo.S08_CatalogProducts WITH FULLSCAN

     

    Simply updating the stats took my search query times for various phrases from 15s/60s/21s/etc down to subsecond for all of them.  One more thing to do after building a catalog, but definitely worth it.

     

    Thanks again Max!

     

    Rob

    Monday, May 5, 2008 7:10 PM