The SQL statement below targets a product catalog table with very few writes and needs to be optimize for read speed. I looked up the article on HOW and WHY to create indexes http://msdn.microsoft.com/en-us/library/ms188783.aspx, but I can't seem to find anything on if you have several columns that could be optional.

The end-user should be able to search on all, one, or none of the columns. I was thinking I could use filtered indexes for each of the targets but how would I use them for optional columns? Also would a composite index on the base table be of any help to speed up this query?

So far there is only a primary key index on an identity column, and a unique index on the product upc.

Any help would be appreciated!

;WITH Products AS
(
SELECT ROW_NUMBER()
OVER
(
ORDER BY
CASE WHEN @sortby = 'low-to-high' THEN pricesort END,
CASE WHEN @sortby = 'high-to-low' THEN pricesort END DESC,
CASE WHEN @sortby = 'titles-foward' THEN pname END,
CASE WHEN @sortby = 'titles-reverse' THEN pname END DESC,
CASE WHEN @sortby = 'added-foward' THEN createdate END,
CASE WHEN @sortby = 'added-reverse' THEN createdate END DESC,
CASE WHEN @sortby = 'rating' THEN pricesort END,
CASE WHEN @sortby = 'lastedit-forward' THEN lasteditdate END,
CASE WHEN @sortby = 'lastedit-reverse' THEN lasteditdate END DESC,
--CASE WHEN @sortby = 'popular' THEN stats_viewcount END DESC,
CASE WHEN len(@sortby) = 0 OR @sortby IS NULL THEN pricesort END
) as pindex, pid, pkey, pname, pdescription, pprice, psale, psavings, stockstate, readystate, ptype_short, ptype_display, pmfg_short, pmfg_display, cOnHand, cVirtual, pshops, pgenres
FROM dbo.vw_inventory_productdata_meta
WHERE ((@category <> 'shop') OR (@category = 'shop' AND pshops.exist('(/values/shop/@short)[1] eq sql:variable("@shop")') = 1))
AND
((@category <> 'genre') OR (@category = 'genre' AND pgenres.exist('(/values/genre/@short)[1] eq sql:variable("@genre")') = 1))
AND
stockstate = CASE @category
WHEN 'new' then 'NEW'
WHEN 'available' THEN 'AVAILABLE'
WHEN 'coming' THEN 'COMING'
ELSE stockstate END
AND
pissale = CASE @category WHEN 'deals' then 'YES' else pissale END
AND
pmfg_short = CASE @category WHEN 'mfg' THEN @value ELSE pmfg_short END
AND
oleftstate = CASE @category WHEN 'oneleft' THEN 'SINGLE' ELSE oleftstate END
AND
pisexclusive = CASE @category WHEN 'exclusives' THEN 1 ELSE pisexclusive END
AND
available = CASE @category WHEN 'inactive' THEN 0 ELSE available END
AND
pmodified = CASE @category
WHEN 'lastedit7in' THEN '7IN'
WHEN 'lastedit3out' THEN '3OUT'
WHEN 'lastedit7out' THEN '7OUT'
WHEN 'lastedit30out' THEN '30OUT'
ELSE pmodified END
AND
(@instockonly = 0 OR (@instockonly <> 0 AND cOnHand > 0))
AND
(LEN(@type) = 0 OR (LEN(@type) <> 0 AND (ptype_short = @type OR ptypeparent_short = @type)))
AND
(LEN(@genre) = 0 OR (LEN(@genre) <> 0 AND (pgenres.exist('(/values/genre/@short)[1] eq sql:variable("@genre")') = 1)))
AND
(LEN(@shop) = 0 OR (LEN(@shop) <> 0 AND (pshops.exist('(/values/shop/@short)[1] eq sql:variable("@shop")') = 1)))
AND
(LEN(@company) = 0 OR (LEN(@company) <> 0 AND pmfg_short = @company))
AND
pricesort BETWEEN @lowprice AND @highprice
AND
createdate >
CASE WHEN @stockstate = 'New' OR @stockstate = 'Available' THEN
CASE
WHEN @cdaterange = 365 THEN DATEADD(DAY,-365,GETDATE())
WHEN @cdaterange = 60 THEN DATEADD(DAY,-60,GETDATE())
WHEN @cdaterange = 30 THEN DATEADD(DAY,-30,GETDATE())
WHEN @cdaterange = 7 THEN DATEADD(DAY,-7,GETDATE())
ELSE DATEADD(YEAR,-100,GETDATE())
END
ELSE DATEADD(YEAR,-100,GETDATE()) END
AND releasedate <
CASE WHEN @stockstate = 'Coming' THEN
CASE
WHEN @rdaterange = 7 THEN DATEADD(DAY,7,GETDATE())
WHEN @rdaterange = 14 THEN DATEADD(DAY,14,GETDATE())
WHEN @rdaterange = 30 THEN DATEADD(DAY,30,GETDATE())
WHEN @rdaterange = 60 THEN DATEADD(DAY,30,GETDATE())
WHEN @rdaterange = 365 THEN DATEADD(DAY,30,GETDATE())
ELSE GETDATE()
END
ELSE GETDATE() END
)

All we (and you!) can do is speculate and theorize until you have an execution plan for this query. With the execution plan you can figure out what is slow and directly address it. Could you post your execution plan?
–
mwigdahlJul 31 '12 at 16:26

1

The problem is the execution plan currently in use only addresses a specific parameter set. So you will see performance suffer whenever parameters are defined that would require a different plan. In all honesty with this many permutations it will be very difficult to create indexes that cover all combinations well (and don't become an absolute nightmare for DML). You may be better off using dynamic SQL and the optimize for ad hoc queries server option which prevents plan cache bloat for single-use plans.
–
Aaron BertrandJul 31 '12 at 16:37

mwigdahl - The execution plan is too big to post in XML. Aaron - I will try your suggestion. I am not sure if it changes anything but there are default values for the parameters that I am using.
–
Alex ErwinJul 31 '12 at 17:15