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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
 
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:
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
All Courses

From novice to tech pro — start learning today.