September 4, 2007 at 5:57 pm
I had to put together a query to filter results based on any one of a dozen or more "filters" supplied by the end user. Each filter can be turned on or off, and can pass multiple values. The query takes several hundred thousand rows of data and combines it into "like" styles. This is the query that I put together, and it is working exactly as it was intended to. My question is: Is this the correct method or did I make a mountain out of a mole hill?
select distinct style
from (select Style, max(itmclscd) as ITMCLSCD, max(USCATVLS_3) as GENDER, max(USCATVLS_4) as CATEGORY, max(USCATVLS_5) as SUBCAT, max(USCATVLS_6) as SHARED, max(IntroDate) as IntroDate, max(PhaseOut) as PhaseOut, max(OnSite) as OnSite, max(WebCloseout) as WebCloseout, max(PinkRibbon) as PinkRibbon, max(USA) as USA, max(GOR) as GOR, max(Promo) as Promo, max(CorpCloseout) as CorpCloseout
from (SELECT rtrim(itmshnam) as Style, ITMCLSCD, USCATVLS_3, USCATVLS_4,USCATVLS_5, USCATVLS_6,
case when b.PT_WINDOW_ID = 'GENERAL ATTRIB' and b.PT_UD_Number = '1' then '1' else 0 end as IntroDate,
case when b.PT_WINDOW_ID = 'GENERAL ATTRIB' and b.PT_UD_Number = '2' then '1' else 0 end as PhaseOut,
case when b.PT_WINDOW_ID = 'GENERAL ATTRIB' and b.PT_UD_Number = '3' then '1' else 0 end as OnSite,
case when c.PT_WINDOW_ID = 'GENERAL ATTRIB' and c.PT_UD_Number = '4' then Total else null end as WebCloseout,
case when c.PT_WINDOW_ID = 'GENERAL ATTRIB' and c.PT_UD_Number = '5' then Total else null end as PinkRibbon,
case when c.PT_WINDOW_ID = 'GENERAL ATTRIB' and c.PT_UD_Number = '6' then Total else null end as USA,
case when c.PT_WINDOW_ID = 'GENERAL ATTRIB' and c.PT_UD_Number = '7' then Total else null end as GOR,
case when c.PT_WINDOW_ID = 'GENERAL ATTRIB' and c.PT_UD_Number = '8' then Total else null end as Promo,
case when c.PT_WINDOW_ID = 'GENERAL ATTRIB' and c.PT_UD_Number = '9' then Total else null end as CorpCloseout
FROM IV00101 as a left join ext00102 as b on a.itemnmbr = b.PT_UD_Key left join ext00103 as c on a.itemnmbr = c.PT_UD_Key) f
group by style) g
WHERE ITMCLSCD = @CLASS
and GENDER = Coalesce(case @cbGender when 1 then @GENDER else null end,GENDER)
and CATEGORY = Coalesce(case @cbCategory when 1 then @CAT else null end, CATEGORY)
and SUBCAT = Coalesce(case @cbSubCat when 1 then @SubCat else null end, SUBCAT)
and SHARED = Coalesce(case @cbShared when 1 then @Shared else null end, SHARED)
and isnull(IntroDate,0) = Coalesce(case @cbIntro when 1 then @Intro else null end, isnull(Introdate,0))
and isnull(PhaseOut,0) = Coalesce(case @cbPhaseOut when 1 then @PhaseOut else null end, isnull(PhaseOut,0))
and isnull(OnSite,0) = Coalesce(case @cbOnSite when 1 then @Onsite else null end, isnull(OnSite,0))
and isnull(WebCloseout,'0') = Coalesce(case @cbWebCloseout when 1 then @WebCloseout else null end, isnull(WebCloseout,'0'))
and isnull(PinkRibbon,'0') = Coalesce(case @cbPinkRibbon when 1 then @PinkRibbon else null end, isnull(PinkRibbon,'0'))
and isnull(USA,'0') = Coalesce(case @cbUSA when 1 then @USA else null end, isnull(USA,'0'))
and isnull(GOR,'0') = Coalesce(case @cbGOR when 1 then @GOR else null end, isnull(GOR,'0'))
and isnull(Promo,'0') = Coalesce(case @cbPromo when 1 then @Promo else null end, isnull(Promo,'0'))
and isnull(CorpCloseout,'0') = Coalesce(case @cbCorpCloseout when 1 then @CorpCloseout else null end, isnull(CorpCloseout,'0'))
group by style
order by style
September 6, 2007 at 1:49 am
It will work.
That said, this kind of catch-all search query is very prone to poor execution plans due to parameter sniffing and cached plans. You may find that for certain searches its very fast and for others it's very slow.
Your 'isnull(<column>,0) structures in the where clause prevent index seeks on that column, also increasing the possibilities of this running very poorly.
You say it's several hundred thousand rows of data. With that volumn, this could easily become a performance nightmare.
Despite my general dislike of dynamic SQL, if you absolutely must have this kind of general query, dynamic SQL may perform better. There are however other considerations. http://www.sommarskog.se/dynamic_sql.html
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
September 6, 2007 at 2:48 am
I second that, GilaMonster !
You may be better of putting some effort into analysing top-x predicate combinations and providing good accesspaths for those queries. You may end up with # queries !, but they will be designed for good performance.
browse the forum(s) on SSC and you'll find a number of threads handling "dynamic where clauses"
Read the doc GilaMonster provided the url for, for most of us this is an eyeopener and a very good ref.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply