-
Dec 20th, 2021, 08:04 AM
#1
[RESOLVED] eleminate surgable dates
Hi
Code:
CASE
WHEN
C.TransC_dtmRealTransTime BETWEEN convert(datetime,convert(char(10),C.TransC_dtmRealTransTime,101)) AND DATEADD(HOUR,6,convert(datetime,convert(char(10),C.TransC_dtmRealTransTime,101)))
THEN
DATEADD(DAY,-1,convert(datetime,convert(char(10),C.TransC_dtmRealTransTime ,101)))
ELSE
convert(datetime,convert(char(10),C.TransC_dtmRealTransTime,101))
END
END AS COLLECTED_DATE,
Is there a way to cut that conversions for TransC_dtmRealTransTime ( is of datetime type)
Thanks.
Last edited by sapator; Dec 20th, 2021 at 09:19 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 20th, 2021, 09:08 AM
#2
Re: eleminate surgable dates
Perhaps that code could have been formatted better so that we could read it more easily.
-
Dec 20th, 2021, 09:20 AM
#3
Re: eleminate surgable dates
Yes sir JMC right away.
I copy pasted the database to see what I have to put up with (hope the excuse is believable....)
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 20th, 2021, 09:41 AM
#4
Re: eleminate surgable dates
Wait... is all you're trying to do is see if the datetime is between midnight and 6am, and if it is, subtract a day? Just use DATEPART and pull the HOUR and look to see if it's between 0 and 5 the rest of your conversion should be ok...
Or,... try something like this:
shooting frmo the hip here... I only have 1a couple min before stand up... so... here goes...
Code:
convert(datetime,
convert(char(10),
dateadd(DAY,
case
when datepart(hour, C.TransC_dtmTrealTransTime) between 0 and 5 then -1
else 0
end), 101))
I *think* that might work... untested...
-tg
-
Dec 20th, 2021, 09:53 AM
#5
Re: eleminate surgable dates
Hey,
Is that a surgable query? As the converts are there.
Thanks.
Edit:
I also get:
The dateadd function requires 3 argument(s).
Last edited by sapator; Dec 20th, 2021 at 09:56 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 20th, 2021, 10:05 AM
#6
Re: eleminate surgable dates
Why is this item concerning you? If you take it out, or just hard wire one of the results in that field, does it change the speed of execution?
-
Dec 20th, 2021, 10:41 AM
#7
Re: eleminate surgable dates
Hmm.
Yes it really does not affect the execution.
I get a 92% table scan on some similar table but this part does not affect the execution. The thing is that it is indexed on the datetime so I'm kinda of left in the dark.
If you are interested,
This is the query:
Code:
INSERT INTO ZZ_rptCR_ZXReport
(
kin_payment,
Gross,
Collected_WorkstationCode,
PickUpUserNo,
InHouse
)
SELECT kin_payment,--Price_strDescription,TransT_intNoOfSeats,TransT_strType,P,PR,
SUM(Gross),
Collected_WorkstationCode,
PickUpUserNo,
InHouse
FROM (
SELECT DISTINCT P.Price_strDescription ,TransT_intNoOfSeats,TransT_strType,P.Price_strDescription as p,PR.Price_strDescription as pr,
CASE
WHEN
PR.Price_strDescription IS NOT NULL
THEN
PR.Price_curPrice
ELSE
P.Price_curPrice
END as gross,
t.Transt_lgnNumber,Transt_intSequence,
'3' AS kin_payment,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BH.BookingH_strPickupWorkstn
ELSE
T.Workstation_strCode
END AS Collected_WorkstationCode,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
CASE
WHEN
BH.BookingH_dtmDateCollected BETWEEN convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected,101)) AND DATEADD(HOUR,6,convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected,101)))
THEN
DATEADD(DAY,-1,convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected ,101)))
ELSE
convert(datetime,convert(char(10),BH.BookingH_dtmDateCollected,101))
END
ELSE
CASE
WHEN
TransT_dtmRealTransTime BETWEEN convert(datetime,convert(char(10),TransT_dtmRealTransTime,101)) AND DATEADD(HOUR,6,convert(datetime,convert(char(10),TransT_dtmRealTransTime,101)))
THEN
DATEADD(DAY,-1,convert(datetime,convert(char(10),TransT_dtmRealTransTime ,101)))
ELSE
convert(datetime,convert(char(10),TransT_dtmRealTransTime,101))
END
END AS COLLECTED_DATE,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
BH.BookingH_intPickupUser
ELSE
T.User_intUserNo
END AS PickUpUserNo,
CASE
WHEN
BH.BookingH_intNextBookingNo IS NOT NULL
THEN
0
ELSE
1
END AS InHouse
FROM tblTrans_ticket T
LEFT JOIN tblPrice P
ON T.PGroup_strCode = P.PGroup_strCode
AND T.Price_strCode = P.Price_strCode
LEFT JOIN tblWorkstation W
ON T.Workstation_strCode = W.Workstation_strCode
LEFT JOIN tblBooking_Header BH
ON T.TransT_lgnNumber = BH.TransC_lgnNumber
LEFT JOIN tblPrice_Package PP
ON T.PGroup_strCode = PP.PGroup_strCode
AND T.Price_strCode = PP.TType_strCode
AND T.TransT_strParentTType_strCode = PP.Price_strCode
LEFT JOIN tblPrice PR
ON PR.PGroup_strCode = PP.PGroup_strCode
AND PR.Price_strCode = PP.Price_strCode
WHERE ISNULL(T.TransT_strBarcodeRedemp,'') <> ''
OR T.STax_strCode in ('S','T','U','V','W','X','Y') OR TickNum_strStyleCode = '|TICK|S|N|N|N|'
OR ISNULL(T.TransT_strVoucherBarcode,'') <> ''
) T
WHERE Gross>0
AND CASE
WHEN
@Z_Date = '19000101'
THEN
@Z_Date
ELSE
ISNULL(COLLECTED_DATE,@Z_Date)
END = @Z_Date
AND CASE
WHEN
@PICK_UP_BOX = ''
THEN
@PICK_UP_BOX
ELSE
Collected_WorkstationCode
END = @PICK_UP_BOX
AND CASE
WHEN
@USERNO = -1
THEN
@USERNO
ELSE
PickUpUserNo
END = @USERNO
GROUP BY kin_payment,--TransT_intNoOfSeats,TransT_strType,P,PR,
Collected_WorkstationCode,
PickUpUserNo,
InHouse--,Price_strDescription
I get 92% on tbltrans_ticket
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 20th, 2021, 11:55 AM
#8
Re: eleminate surgable dates
Whether a query is SARG-able depends on the predicates in WHERE clause, not on any CASE WHEN expression in the column-list of the SELECT clause (which are practically free in price for execution terms).
You have this
Code:
WHERE Gross>0
AND CASE
WHEN
@Z_Date = '19000101'
THEN
@Z_Date
ELSE
ISNULL(COLLECTED_DATE,@Z_Date)
END = @Z_Date
AND CASE
WHEN
@PICK_UP_BOX = ''
THEN
@PICK_UP_BOX
ELSE
Collected_WorkstationCode
END = @PICK_UP_BOX
AND CASE
WHEN
@USERNO = -1
THEN
@USERNO
ELSE
PickUpUserNo
END = @USERNO
. . . for which there is no easy fix.
We usually end up with dynamic-sql for such optional filtering when upon @Z_Date = '19000101' the COLLECTED_DATE = @Z_Date filter is just skipped from the d-sql so no performance penalty is incurred. Unfortunately d-sql means recompilation on each and every execution so this is a double-edged sword in itself too.
Btw, the typical example of non-SARG-able WHERE clause is using ISNULL/COALESCE around COLLECTED_DATE column. This 100% prevents query optimizer from using whatever index there is on this column. Just try to filter directly on your columns with
COLLECTED_DATE BETWEEN @X AND @Y
or something like
COLLECTED_DATE = @Z
and then make a logical conjunction of such predicates i.e.
WHERE COLLECTED_DATE = @Z
AND OTHER_COLUMN BETWEEN @A AND @B
AND THIRD_COLUMN IN (SELECT ...)
Stay away from disjunctions (OR clauses) because these can rarely be optimized if at all. You can use functions "to the right" of the predicates e.g. DocYear = YEAR(@FilterDate) which is free performance wise.
cheers,
</wqw>
-
Dec 20th, 2021, 12:02 PM
#9
Re: eleminate surgable dates
Sometimes it can be faster to bring key selection fields into a temp table and clean them up. Include whatever primary-key fields so you can JOIN up your final SELECT.
Can you step back and rethink how you are getting your population?
-
Dec 20th, 2021, 12:38 PM
#10
Re: eleminate surgable dates
I believe a temp table can do the trick.
I have done that in another query.
P.S. I was under the impression that converting is non sargable no matter if it is not in the where clause.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 21st, 2021, 04:51 AM
#11
Re: eleminate surgable dates
An index stores sorted list of the values of a column. A predicate is sargable when the query optimizer can figure out which value to search this index for.
For instance something like MyColumn > 5 is sargable -- just search for 5 in the index and take averything after it.
But SomeFunction(MyColumn) > 5 is not sargble because there is no generic way to calculate the oposite function of SomeFunction (let's call such function ReverseFunction).
If there is a way to deduce ReverseFunction from SomeFunction then the query optimizer can rewrite
SomeFunction(MyColumn) > 5
. . . by applying ReverseFunction on both sides into
ReverseFunction(SomeFunction(MyColumn)) > ReverseFunction(5)
. . . which can be simplified by removing consequtive ReverseFunction + SomeFunction application to
MyColumn > ReverseFunction(5)
. . . which *is* sargable as in this case the function usage is "to the right" of the predicate and ReverseFunction(5) calculates the concrete value to search the index for.
cheers,
</wqw>
Last edited by wqweto; Dec 21st, 2021 at 04:55 AM.
-
Dec 21st, 2021, 09:09 AM
#12
Re: eleminate surgable dates
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|