May 21, 2009 at 5:58 pm
Hi,
(Apologies in advance for the long question)
I have a indexing question which I hope some of you may be able to help me with.
I have something similar to a Product table, which Products having columns say Brand, Category, Subcategory, SubSubCategory, and a lot more.
Users can search these Products, but only a subset of them, so I have created a View that has let’s say the “for sale” products (ForSaleProduct) and that has the Brand, Category, SubCategory, SubSubCategory and a additional set of columns of the Products table that can be searched on
Searching can happen on a number of columns, all optional, although they have to include at least either Brand or Category, or both. Obviously when they specify a Subcategory they have to have specified the category in their search fields and the same for the SubSubCategory.
One of the Columns in the View is a concatenation of some of the text columns on the products and the View has a full text index on this column, so a text search can also be included in the search. The View is created with SchemaBinding and has a unique index on it for the full text index.
Because all these search fields are optional I have created a stored procedure with parameters @inBrand, @inCategory, @inSubCategory, @inSubSubCategory, @etc1, @etc2, …, @inSearchText. Parameters that are not to be used in the search are set to NULL or ‘’ in the case of the search text. My stored procedure looks like:
IF @inSearchText = ‘’
SELECT …
FROM ForSaleProduct
JOIN Product ON ...
WHERE
(@inBrand IS NULL OR ForSaleProduct.Brand = @inBrand)
AND (@inCategory IS NULL OR ForSaleProduct.Category = @inCategory)
AND etc. (you probably get the picture)
ELSE
SELECT …
FROM ForSaleProduct
JOIN CONTAINSTABLE(ForSaleProduct, SearchText, @inSearchText) AS [Result] ON …
JOIN Product ON ...
WHERE
(@inBrand IS NULL OR ForSaleProduct.Brand = @inBrand)
AND (@inCategory IS NULL OR ForSaleProduct.Category = @inCategory)
AND etc. (you probably get the picture)
My question is:
With columns being used or skipped in the actual query, how can I create a proper index or set of indexes on this view so that this index can be used in the query? And can I then still use one stored procedure or do I need to split up the query in different cases, depending on the fields that are being searched on. If necessary I can probably filter it down to a number of most important cases to keep the number of different combinations to a real amount.
I have tried different combinations and looked at the execution plans, but don't really get a good picture of what is happening. In case this is important: It is all running on SQL Server 2005
Gerard
May 22, 2009 at 3:31 am
With that form of query, indexes are not going to help much. That particular query form performs badly, pretty much no matter what.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2009 at 2:57 pm
Hi Gail,
Thanks a lot for your reply. That certainly helps me a lot.
Cheers, Gerard
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply