StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSql Server 2012 Full Text Search - Slow response on initial queries
text
Body
copied!<p>I've a website running an eCommerce shop based on Sql Server 2012 Web Edition. The database is more and less 4GB and it contains 20.000 items circa. This database is configured as "Simple" and contains a full text catalog whose size is 15MB.</p> <p>Whenever I start up the site and try to search, the store procedure that uses the FTS runs out of time after 30 secs. This behavior continues for the following 2-3 searches. After that, everything goes smoothly and results are retrieved.</p> <p>I've tried to scale up the server on which the website is hosted, but nothing changed. I've also tried to host the site database on a dedicated machine but no improvement noticed. I tried to configure a population schedule for the full text catalog, but nothing seems changed.</p> <p>I really cannot understand what could that be because when I search:</p> <ol> <li>website is obviously started and so the process is running and AppPool is configured</li> <li>database should be turned on, as on the first page there are some products that taken (if they are not in cache) from the db. I suppose that caching is not so used because the same server is used by more and less 10 other eCommerce websites.</li> <li>after 1-5 queries everything goes smoothly for a period of time</li> </ol> <p>Anyone has some ideas?</p> <p>Here is an extract from the code. </p> <ol> <li>website launches a search by invoking a stored procedure.</li> <li>Stored procedure takes 15 parameters. One of these is Keyword that is the searched text</li> <li><p>This is what the stored procedure does at the true beginning if Keyword is not null and if there is a full text catalog on the DB:</p> <pre><code>CREATE TABLE #ProductFullTextSearch ([ProductID] int NOT NULL) SET @Keywords = isnull(@Keywords, '""') IF(@Keywords = '') SET @Keywords = '""' IF (@Keywords <> '""') BEGIN IF(@SearchExact = 1) SET @Keywords = '"*' + @Keywords +'*"' ELSE SET @Keywords = '"*' + REPLACE(@Keywords, ' ', '*" AND "*') +'*"' PRINT @Keywords INSERT INTO #ProductFullTextSearch(ProductID) SELECT p.ProductID FROM Nop_Product p with (NOLOCK) LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID WHERE p.Deleted = 0 AND ((CONTAINS((p.name), @Keywords)) or (@SearchDescriptions = 1 and CONTAINS((p.ShortDescription, p.FullDescription), @Keywords)) ) INSERT INTO #ProductFullTextSearch(ProductID) SELECT p.ProductID FROM Nop_Product p with (NOLOCK) LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID WHERE p.Deleted = 0 AND ((CONTAINS((pv.name, pv.SKU), @Keywords)) or (@SearchDescriptions = 1 and CONTAINS(pv.Description, @Keywords)) ) END </code></pre></li> <li><p>That part creates a temp table which is used to insert ProductIds that respond to the query text.</p></li> <li>Stored Procedure filters products using other parameters and removing items whose ID are not contained in the temp table</li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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