improve query (where with case when)

i have this query:

select sum(coalesce(PaidDeals.Value,0)) from
            T_Deals Deals inner join T_PaidDeals PaidDeals on PaidDeals.DealId = Deals.DealId and PaidDeals.Valid = 1
                  where  Deals.IdMain = @IdMain  and
                  datediff(dd, 0, case when @UseDate = 1 then Deals.DealDate
                                  when @UseDate = 2 then PaidDeals.PaidDate end) = datediff(dd, 0, getdate())

so that if the parameter @useDate  =1 it will compare based on the deal date, otherwise will compare with the paid date.. but this query takes like 30sec with the case when, but like 15sec with the case when (no matter if i use datediff(dd, 0, Deal.DealDate) or datediff(dd, 0,UpdateTable.Date)..

so is using dynamic sql my only choice ? or is there anyway to improve it ?

also im using this query in a update like i create a temp table with X dates depending on the selected range with 2 col (Date, TotalValue) and then run an update temptable set TotalValue =  queryabove does it matter also ?

does anyone have any ideia how i can improve it ?

LVL 10
eguilhermeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
use dynamic SQL...for sure.  Do you need help w/ it?
0
crisco96Commented:
If it's taking a long time you ight want to make sure you have indexes/foreign keys on the following fields:

PaidDeals.DealId
Deals.DealId

And make sure the following is indexed
Deals.IdMain
0
chapmandewCommented:
indexes aren't going to help you here w/ the way your query is setup.  you need to write dynamic sql for this.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Download your Guide
eguilhermeAuthor Commented:
those fields you mentioned are already indexed and with the relationship (PaidDeals.Dealid / Deals.DealId) and so is indexed the idMain..

what i find strange, is that if i remove the case when and just do the datediff with the field, it will go almost twice as fast... why is that ?
0
chapmandewCommented:
because SQL has to do the comparison at runtime, so it doesn't really know how to handle the query plan.  
0
LowfatspreadCommented:
try this

select sum(case convert(char(8),getdate(),112)
                when convert(char(8),case @usedate when 1 then deals.dealdate else paiddeals.paiddate end,112)  
            then coalesce(PaidDeals.Value,0) else 0 end)
from  T_Deals Deals
inner join T_PaidDeals PaidDeals
 on PaidDeals.DealId = Deals.DealId
and PaidDeals.Valid = 1
 where  Deals.IdMain = @IdMain
0
Anthony PerkinsCommented:
Do it this way:
IF @UseDate = 1 
    SELECT  SUM(COALESCE(PaidDeals.Value, 0))
    FROM    T_Deals Deals
            INNER JOIN T_PaidDeals PaidDeals ON PaidDeals.DealId = Deals.DealId
                                                AND PaidDeals.Valid = 1
    WHERE   Deals.IdMain = @IdMain
            AND Deals.DealDate >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
            AND Deals.DealDate < DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

ELSE 
    IF @UseDate = 2 
        SELECT  SUM(COALESCE(PaidDeals.Value, 0))
        FROM    T_Deals Deals
                INNER JOIN T_PaidDeals PaidDeals ON PaidDeals.DealId = Deals.DealId
                                                    AND PaidDeals.Valid = 1
        WHERE   Deals.IdMain = @IdMain
                AND PaidDeals.PaidDate >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
                AND PaidDeals.PaidDate < DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
Anthony PerkinsCommented:
Let's try that again:
IF @UseDate = 1 
    SELECT  SUM(COALESCE(PaidDeals.Value, 0))
    FROM    T_Deals Deals
            INNER JOIN T_PaidDeals PaidDeals ON PaidDeals.DealId = Deals.DealId
                                                AND PaidDeals.Valid = 1
    WHERE   Deals.IdMain = @IdMain
            AND Deals.DealDate >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
            AND Deals.DealDate < DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

ELSE 
    IF @UseDate = 2 
        SELECT  SUM(COALESCE(PaidDeals.Value, 0))
        FROM    T_Deals Deals
                INNER JOIN T_PaidDeals PaidDeals ON PaidDeals.DealId = Deals.DealId
                                                    AND PaidDeals.Valid = 1
        WHERE   Deals.IdMain = @IdMain
                AND PaidDeals.PaidDate >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
                AND PaidDeals.PaidDate < DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
This way you avoid haveing to resort to Dynamic SQL and you take advantage of your indexes.  

If it has to be in one SQL statement than do it like this:
SELECT  SUM(COALESCE(PaidDeals.Value, 0))
FROM    T_Deals Deals
        INNER JOIN T_PaidDeals PaidDeals ON PaidDeals.DealId = Deals.DealId
                                            AND PaidDeals.Valid = 1
WHERE   Deals.IdMain = @IdMain
        AND ((@UseDate = 1
              AND Deals.DealDate >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
              AND Deals.DealDate < DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))
             )
             OR (@UseDate = 2
                 AND PaidDeals.PaidDate >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
                 AND PaidDeals.PaidDate < DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))
                )
            )

Open in new window

0
eguilhermeAuthor Commented:
i modified this a little bit,  and now its great.. my only "problem" is that if in the future i need to use more dates to compare, then a dynamic sql might be better..

for now i ll be using this

thx all
0
chapmandewCommented:
anthony, if it has to be one query, there is no good way to take advantage of the indexes.  
0
Anthony PerkinsCommented:
>> there is no good way to take advantage of the indexes.  <<
Is that because of the OR ?
0
chapmandewCommented:
yeah, the OR (most likely) gets you there.  There are cases where the selectivity of the field doesn't really care...such as in this case:

use tempdb
go
create table #t(fld1 int)

insert into #t(fld1)
select row_number() over(order by newid())
from sys.columns c, sys.columns cc

create nonclustered index idx#tfld1 on #t(fld1)

dbcc show_statistics (#t,idx#tfld1 )  --show density, should be 1

select fld1
from #t
where fld1 = 4 or fld1 = 5000

the statistics know that the values are unique, so it can do a seek.  So, really its a case by case basis.  With the dates used above along w/ the variable, I kinda doubt it will do a seek, but I could be wrong.
0
Anthony PerkinsCommented:
I am sure you are right.  Thanks for the additional input.
0
chapmandewCommented:
sure...anytime.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.