Results 1 to 12 of 12

Thread: [RESOLVED] eleminate surgable dates

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Resolved [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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: eleminate surgable dates

    Perhaps that code could have been formatted better so that we could read it more easily.

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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....)
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  8. #8
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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>

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  11. #11
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    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>

  12. #12

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: eleminate surgable dates

    Thanks
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width