0

I have set up full-text indexing in my database along with a CONTAINSTABLE so users can search on the website against product SKUs & product names. I have set up a customization on the search, to where it strips the hyphen (-) out of a users search and in the database column when it grabs values. I have run into an issue on the search where searching '10-10' results in a broken page, with the error listing as so : 

Source:.Net SqlClient Data Provider
Message:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Stack Trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

The search works great otherwise, and I know that the hypen shouldn't be an issue, because if I search 'tw5-c' I get a few results returned. Is it possible that too many results are being returned and the connection is timing-out?

Here is the SQL CONTAINSTABLE statement:

INSERT @searchfilter (productid, variantID, SearchRank)
        SELECT p.ProductID, pv.VariantID, 100 from dbo.Product p with(NOLOCK)
            JOIN dbo.ProductVariant pv with(NOLOCK) on p.ProductID = pv.ProductID 
                JOIN dbo.ProductStore ps with(NOLOCK) on p.ProductID = ps.ProductID and ps.StoreID = @storeID
                WHERE 
                ( REPLACE(p.SKU,'-','') LIKE '%' + @searchstr + '%' )
                OR
                ( REPLACE(p.Name,'-','') LIKE '%' + @searchstr + '%' )
        UNION
        SELECT p.ProductID, pv.VariantID,
               case
                    when psearch.[KEY] is not null then psearch.Rank
                    when pvsearch.[KEY] is not null then pvsearch.Rank
                    else 1
                end
                as SearchRank
            from dbo.Product p with(NOLOCK)
            JOIN dbo.ProductVariant pv with(NOLOCK) on p.ProductID = pv.ProductID
            -- fulltext indexing changes for more accurate searches
            join CONTAINSTABLE(Product, *, @ftssearchstring) psearch ON p.ProductID = psearch.[KEY]
            left join CONTAINSTABLE(ProductVariant, *, @ftssearchstring) pvsearch ON pv.VariantID = pvsearch.[KEY]
user3267755
  • 1,010
  • 1
  • 13
  • 32
  • based on your Query, what you are doing you should be fine from a syntax perspective but containstables isn't great for JOINS see another previous answer of mine. http://stackoverflow.com/questions/27933761/can-containstable-be-told-which-column-to-look-in-per-search-term/27934567#27934567 – Brad D Jun 18 '15 at 19:48
  • On a related note, your first query in the Union will have performance issues due to your search criteria being [non-sargable](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). I know that you said that it works, but if you look at your query plan, I think you'll find that it's always performing a table scan. – DeadZone Jun 18 '15 at 20:10
  • Is your timeout setting set to the default of 30 seconds? How big are those tables? By it's very nature, CONTAINSTABLE returns a table with no indexes. So you're performing a poorly-performing query unioned with a query that is performing multiple table scans. It seems to me that this might just be a case of poor query optimization. Can you post your query plan? – DeadZone Jun 18 '15 at 20:17

0 Answers0