Results 1 to 30 of 30

Thread: issue converting datetime to varchar breaks table indexes!

  1. #1

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

    issue converting datetime to varchar breaks table indexes!

    Hello.
    I'm fixing a query that results in a table scan and I'm trying to trigger the index seek that it has.
    I'm having a very strange problem.
    Code:
    ---- etc 
    LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
                                  SUM(TransI_curValueEach) AS TransI_curValueEach,
                                  SUM(TransI_curNetTotal) AS TransI_curNetTotal,
                                  SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
                           FROM tblTrans_Inventory 
                           GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I 
    				ON I.TransI_lgnNumber = T.TransT_lgnNumber
    				AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
    			CROSS JOIN tblCinema	
    WHERE	B.BookingH_dtmDateBooked>=  '2016-12-12'
    			AND Session_dtmRealShow BETWEEN .......... etc
    B.BookingH_dtmDateBooked is DATETIME

    Result:
    Name:  Clipboard01.jpg
Views: 428
Size:  9.4 KB

    Name:  Clipboard02.jpg
Views: 422
Size:  9.4 KB

    Now when I do this:

    Code:
    declare @dateTo as date
    set @dateTo = '20161212'
    set @dateTo =  CONVERT(DATE,CONVERT(NVARCHAR,@DateTo,112))
    ---- etc 
    LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
                                  SUM(TransI_curValueEach) AS TransI_curValueEach,
                                  SUM(TransI_curNetTotal) AS TransI_curNetTotal,
                                  SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
                           FROM tblTrans_Inventory 
                           GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I 
    				ON I.TransI_lgnNumber = T.TransT_lgnNumber
    				AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
    			CROSS JOIN tblCinema
    WHERE	B.BookingH_dtmDateBooked>=  @dateTo 
    			AND Session_dtmRealShow BETWEEN .......... etc
    The plain changes entirely and I get a table scan and a HASH match (!!)

    Name:  Clipboard03.jpg
Views: 427
Size:  12.2 KB

    Name:  Clipboard04.jpg
Views: 404
Size:  9.4 KB

    How is that possible?! I have tried various conversions on the @DateTo . Is there a possibility that @dateTo is treated differently than the '2016-12-12' ?

    What can i try?
    I can post the entire code if required btw the hash match comes from this: tblTrans_Inventory
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: issue converting datetime to varchar breaks table indexes!

    What happens if you convert it to a DATETIME rather than a DATE?

    Do you need to store the column as a full DATETIME or would a DATE suffice? One issue you can run into is that a DATETIME stores the time down to the more or less millisecond range - this will create a lot of different values for every seconds worth of difference and can cause the effectiveness of the index to suffer.

    If you can't store these as just dates then it might be worth considering adding an extra column of type date and calculating it based on the date part alone and use this column for querying.

  3. #3

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

    Re: issue converting datetime to varchar breaks table indexes!

    I've tried all combinations of convert. Datetime, date, to varchar to nvarchar to, to char '' + @dateto + '' etc etc.
    Nothing works.
    I also tried Option (Recompile) suggestion that the value is not known at compilation time. Does not work either.
    Last edited by sapator; Mar 16th, 2017 at 08:13 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    Hyperactive Member
    Join Date
    May 2006
    Posts
    365

    Re: issue converting datetime to varchar breaks table indexes!

    Hello sapator,

    Code:
    declare @mydate varchar(10)
    , @realdate datetime
    , @strDate varchar(8)
    , @wrongdate varchar
    
    set @realdate = getdate()
    set @strDate = convert(varchar(8), @realdate, 112)
    set @mydate = convert(varchar(10), @realdate, 112)
    set @wrongdate = CONVERT(varchar, @realdate, 112)
    
    select @realdate as RealDate, @strDate as Stringdate, @mydate as Mydate, @wrongdate as OMG
    Code:
    Output
    RealDate	           Stringdate	   Mydate	OMG
    2017-03-17 19:24:23.257	20170317	20170317	2
    When converting dates into a varchar format you have to be extremely particular in ensuring that the varchar length suits the requirements of the date that is being converted.

    Kind regards

    Steve

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

    Re: issue converting datetime to varchar breaks table indexes!

    You should not be doing a CONVERT at all.

    Your variable needs to be a DATETIME variable - since your field/index is that data type.

    You set a DATETIME variable to a using a quoted-string - but that is certainly not a varchar() value.

    I can see no reason why the below code should not work - NO convert or cast.

    Code:
    declare @dateTo as datetime
    set @dateTo = '20161212'

    *** 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

  6. #6
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: issue converting datetime to varchar breaks table indexes!

    What can i try?
    have you tried adding an index hint for tblEntryEvent? it could be that this makes sql server prefer the first execution plan. for some reason it now does decide that the second (bad) plan is better to run the second query

    pls post the entire query

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

    Re: issue converting datetime to varchar breaks table indexes!

    To better isolate cause you should just attack that one table with a simple WHERE clause on just the index.

    Use the variable and whatever casting you think you need.

    Prove to yourself whether the data type or the casting of the variable is part of the problem or not by checking the execution plan.

    *** 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

  8. #8

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

    Re: issue converting datetime to varchar breaks table indexes!

    What I think is happening is that the optimized does not know the variable value while preparing the execution plan.
    I was trying to use Option (Recompile) and set statistics profile to get it to have the value with no luck.
    I also tried some other "dirty" tricks (LOOP, MERGE) while they worked at some point, the execution plan was changing afterwards.
    So I'm not sure what else can I do.

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

  9. #9
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: issue converting datetime to varchar breaks table indexes!

    Did you try using a column type of just DATE and seeing if that makes a difference?

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

    Re: issue converting datetime to varchar breaks table indexes!

    Usually sapator is dealing with large production tables and trying to fix slow production queries. I'm guessing that a change of the data type from DATETIME to DATE is not something that can be done...

    *** 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

  11. #11
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: issue converting datetime to varchar breaks table indexes!

    Or add a calculated column that is just a date rather than a datetime and index that, if the query is only using a date then indexing values to within tens of milliseconds is going to create a lot of indexes with a broad range of values that aren't suitable for the types of query.

  12. #12

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

    Re: issue converting datetime to varchar breaks table indexes!

    As szlamany said, I cannot alter the table structure.
    Also if that was the issue (date) then the problem would also appear on a hard coded date value (as I do in the first example) as the query would also try to match different dates.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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

    Re: issue converting datetime to varchar breaks table indexes!

    I've never had issues with DATETIME indexes myself. And I do use them a real lot.

    *** 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

  14. #14

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

    Re: issue converting datetime to varchar breaks table indexes!

    So.
    Strange thing but today the execution plan seems to work.
    So now it understands and gets the correct execution plan.
    I've checked this in 8 primary SQL servers and the execution plan is build correctly on every single one of them.
    So It may be that we are update statistics and the software does a rebuild indexes every week.
    That's what every server has in common, so without being 100% sure, it may as well be (was) the problem.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  15. #15

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

    Re: issue converting datetime to varchar breaks table indexes!

    No.
    Disregard, I was getting another set of data.
    Problem remains.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  16. #16
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: issue converting datetime to varchar breaks table indexes!

    once more:
    Quote Originally Posted by digitalShaman View Post
    have you tried adding an index hint for tblEntryEvent? it could be that this makes sql server prefer the first execution plan. for some reason it now does decide that the second (bad) plan is better to run the second query

    pls post the entire query

  17. #17

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

    Re: issue converting datetime to varchar breaks table indexes!

    Hi.
    I've tried now as you suggested but it does not make any difference just 1% raise on the table scan of tblTrans_Inventory.

    What I found is that there is a '%like% clause may be a good candidate.
    I have eliminated the line and I get all the indexes.

    Here I get a 30% table scan on tblpayment log and I get a hind to use and index. That I cannot, since I cannot add-remove indexes from those tables.

    Then, there is another strange thing. Since I cannot get an execution plan with a temp table by just clicking on the execution plan, I am running the query with included execution plan.
    I get the indexes. Then I turn the query back, using the "Like" clause and removing the temp table.
    I get correct indexes!!
    What I see is that I get the correct indexes, only if I run the query + include the execution plan (with '%like%'). If i just click to get an execution plan, I get the table scans.
    I guess if I could see the temp table plan without running the query, maybe the execution plan will have a table scan in there(?)

    Here is the complete query if anyone interested.

    Code:
    	declare @Date AS DATETIME
    
    	select  @date = '20161231'
    
    declare @dateTo as dateTIME
    --select  @dateTo = '20170311' 	
    select @dateTo = (@Date -60) 
    
    
    --AS
    --BEGIN
    	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    	--set statistics profile on
    
    	
    
    	--temp table try
    	--select  workstation_strname into #tmpwrks from tblWorkstation	
    	--where workstation_strname LIKE '%UNCOL%'
    
    
    
    	
    
    	SELECT	CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112)),
    			Cinema_strName,
    			F.Film_strTitleAlt,
    			S.Session_dtmRealShow,
    			T.TransT_dtmRealTransTime,
    			S.Screen_bytNum,
    			T.ScreenD_strPhyRowId + T.ScreenD_strSeatId,
    			B.BookingH_strCardNo,
    			B.BookingH_dtmDateBooked,
    			P.PayLog_strBankTransNumber,
    			T.TransT_lgnNumber,
    			SUM(CASE
    				--WHEN
    				--	T.STax_strCode in ('S','T','U','V','W','X','Y')			
    				--THEN
    				--	round(T.TransT_intNoOfSeats * ((T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)/(SELECT Ent_TAX + VAT FROM VISTA.dbo.ZZ_CinemaTax WHERE OperatorCode = T.CinOperator_strCode)+ (T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)),1)
    				WHEN
    					T.TickNum_strStyleCode = '|TICK|S|N|N|N|'	OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'		
    				THEN
    					round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach),4)
    				WHEN
    					COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''--Voucher
    				THEN
    					round(T.TransT_intNoOfSeats * (COALESCE(T.TransT_curRedempValueEach,0) + COALESCE(I.TransI_curValueEach,0)),4)
    				WHEN
    					T.STax_strCode in ('A')			
    				THEN
    					round(T.TransT_intNoOfSeats * (T.TransT_curValueEach),4)
    				ELSE
    					round(T.TransT_intNoOfSeats * (T.TransT_curValueEach + COALESCE(I.TransI_curValueEach,0)),4)
    			END) AS Gross,
    			SUM(CASE
    				--WHEN
    				--	T.STax_strCode in ('S','T','U','V','W','X','Y')			
    				--THEN
    				--	round(T.TransT_intNoOfSeats * ((T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)/(SELECT Ent_TAX + VAT FROM VISTA.dbo.ZZ_CinemaTax WHERE OperatorCode = T.CinOperator_strCode)+ (T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)),2) - T.TransT_intNoOfSeats * (T.TransT_curRedempTaxEach + T.TransT_curRedempTaxEach2)
    				WHEN
    					T.TickNum_strStyleCode = '|TICK|S|N|N|N|'	OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'		
    				THEN
    					round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach  - T.TransT_curRedempTaxEach - T.TransT_curRedempTaxEach2),4)
    				WHEN
    					COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''--Voucher
    				THEN
    					round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach  - T.TransT_curRedempTaxEach - T.TransT_curRedempTaxEach2),4)
    				ELSE
    					round(T.TransT_intNoOfSeats * (T.TransT_curValueEach  - T.TransT_curTaxAmount - T.TransT_curTaxAmount2),4)
    			END) AS Net,
    			SUM(CASE
    				WHEN
    					T.TickNum_strStyleCode = '|TICK|S|N|N|N|'	OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'		
    				THEN
    					round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach2,4)
    				WHEN
    					COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' OR T.STax_strCode in ('S','T','U','V','W','X','Y')
    				THEN
    					round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach2,4)
    				ELSE
    					round(T.TransT_intNoOfSeats * T.TransT_curTaxAmount2,4)
    			END) AS FDTH,
    			SUM(CASE
    				WHEN
    					COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' OR T.STax_strCode in ('S','T','U','V','W','X','Y')
    				THEN
    					round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach,4)
    				WHEN
    					T.TickNum_strStyleCode = '|TICK|S|N|N|N|'	OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'		
    				THEN
    					round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach,4)
    				ELSE
    					round(T.TransT_intNoOfSeats * T.TransT_curTaxAmount,4)
    			END) AS FPA,
    			SUM(CASE
    				WHEN
    					COALESCE(T.TransT_strBarcodeRedemp,'') <> '' 
    					OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' 
    					OR T.STax_strCode in ('S','T','U','V','W','X','Y')
    					OR T.TickNum_strStyleCode = '|TICK|S|N|N|N|'	OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
    				THEN
    					round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curNetTotal,0)),4)
    				ELSE
    					round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curNetTotal,0)),4)
    			END) AS KRATHSEIS,
    			SUM(CASE
    				WHEN
    					COALESCE(T.TransT_strBarcodeRedemp,'') <> '' 
    					OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' 
    					OR T.STax_strCode in ('S','T','U','V','W','X','Y')
    					OR T.TickNum_strStyleCode = '|TICK|S|N|N|N|'	OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
    				THEN
    					round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curSTaxEach,0)),4)
    				ELSE
    					round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curSTaxEach,0)),4)
    			END) AS FPAKRATHSEIS,
    			TransT_strStatus,
                1 AS Scanned  --COALESCE(EE.Entered,0) AS Scanned
    			,BD.Bookingd_strPickupWorkstn
    	FROM	tblTrans_Ticket T
    			LEFT JOIN tblSession S
    				ON	S.Session_lngSessionID = T.Session_lngSessionID
    			LEFT JOIN tblFilm F
    				ON F.Film_strCode = S.Film_strCode
    			LEFT JOIN tblBooking_Header B
    				ON B.TransC_lgnNumber = T.TransT_lgnNumber
    			LEFT JOIN tblPaymentLog P
    				ON P.TransC_lgnNumber = T.TransT_lgnNumber
                LEFT JOIN dbo.tblBooking_Detail BD
                    ON BD.BookingD_intNextBookingNo = B.BookingH_intNextBookingNo
                    AND BD.BookingD_intSequence = T.TransT_intSequence
    			LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
                                  SUM(TransI_curValueEach) AS TransI_curValueEach,
                                  SUM(TransI_curNetTotal) AS TransI_curNetTotal,
                                  SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
                           FROM tblTrans_Inventory 
                           GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I 
    				ON I.TransI_lgnNumber = T.TransT_lgnNumber
    				AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
    			CROSS JOIN tblCinema
    	WHERE	B.BookingH_dtmDateBooked >= @dateto
    			AND Session_dtmRealShow BETWEEN DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))) AND DATEADD(hh,24,DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))))
    			AND (P.PayLog_strAccepted = 'Y' OR B.BookingH_strSource = 'VIVA')  --VIVA Transactions are not PayLog
    			AND TransT_strType = 'P'
                AND B.BookingH_strSource IN ('CALL','VIVA','WWW','CELL')    --ONLY ETICKETS
                AND (EXISTS (SELECT * FROM dbo.tblEntryEvent EE  WHERE EE.TransT_lgnNumber = T.TransT_lgnNumber AND EE.TransT_intSequence = T.TransT_intSequence  AND EE.Workstation_strCode = BD.Bookingd_strPickupWorkstn)  --Scanned at least once
    
    -------- here I remove and add the temp table and the like clause
               OR BD.Bookingd_strPickupWorkstn  LIKE '%UNCOL%' or BD.Bookingd_strPickupWorkstn  ='')  --Or Collected by Uncollected Etickets Procedure ('=' for usher issues blank when error)
    	--	OR BD.Bookingd_strPickupWorkstn  in (select workstation_strname from #tmpwrks) or BD.Bookingd_strPickupWorkstn  ='')  
    		
    	GROUP BY  Cinema_strName,
    			F.Film_strTitleAlt,
    			S.Session_dtmRealShow,
    			T.TransT_dtmRealTransTime,
    			S.Screen_bytNum,
    			T.ScreenD_strPhyRowId + T.ScreenD_strSeatId,
    			B.BookingH_strCardNo,
    			B.BookingH_dtmDateBooked,
    			P.PayLog_strBankTransNumber,
    			T.TransT_lgnNumber,
    			TransT_strStatus,
    			BD.Bookingd_strPickupWorkstn
                  option(recompile)
    
    	--drop table #tmpwrks
    --	set statistics profile off	
    
    
    --select * from tblWorkstation where workstation_strname LIKE '%UNCOL%'
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  18. #18
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: issue converting datetime to varchar breaks table indexes!

    Here is the complete query if anyone interested.
    OMG, ist a Monster (again)...
    not sure when i find some time to decipher this.

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

    Re: issue converting datetime to varchar breaks table indexes!

    It looks to me like you have this in a stored procedure in production - is that correct?

    If so can you use a different method to get your "population of rows"?

    *** 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

  20. #20
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: issue converting datetime to varchar breaks table indexes!

    Code:
    AND Session_dtmRealShow BETWEEN DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))) AND DATEADD(hh,24,DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))))
    what the heck?


    Code:
    AND Session_dtmRealShow BETWEEN DATEADD(hh,6,@Date) AND DATEADD(hh,30,@Date)
    wouldn't that do just fine?
    Last edited by digitalShaman; Mar 23rd, 2017 at 12:48 PM.

  21. #21
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: issue converting datetime to varchar breaks table indexes!

    you say you have tried a

    Code:
    			LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
                                  SUM(TransI_curValueEach) AS TransI_curValueEach,
                                  SUM(TransI_curNetTotal) AS TransI_curNetTotal,
                                  SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
                           FROM tblTrans_Inventory (WITH (INDEX PK_tblTrans_I... whatever name it is that is used in the fast query))
                           GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I 
    				ON I.TransI_lgnNumber = T.TransT_lgnNumber
    				AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo

  22. #22

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

    Re: issue converting datetime to varchar breaks table indexes!

    Hi szlamany. What do you mean a different method (yes it's an sproc alright)?
    Mind you this is supposed to be a production query that was initially design by the 3rd party, so in theory It should work fine (it does not apparently) ,
    So I do what I can to fix it but I'm not 100% sure on every sum or round it does.
    I have already changed all the Isnull to Coalesce, trying to remove the"like" clause. Can't remove the '|CONC|S|N|N|T|' , I may be able to remove some tax codes as some of them are not used and probably the select * vs a single column select , would like to remove the initial sum on the tbltrans_inventory as it is bugging me a lot to have it like this but I'm not sure where it sums and what (would appreciate a helping hand here). But as I see, the main issue seems to be the "like" condition, that apparently when I execute the #temp table and the use the "%like%" again, it picks up the optimal previous execution plan but not sure for how long, so I think I can put the temp table as a change so I will be sure that it stays that way.

    digitalShaman. The datetime convert was there as an older DB had the date columns as varchars. So yes It can probably change but I'm not sure what will they do with the next update
    I would prefer to avoid forcing an index to it and let the optimizer do that. As it only worked once on a previous problem I had (in a megathread posted here a couple of months ago). Granted , it works if i force it the cluster index but should I be better of , if removing the like clause is fixing the problem, rather than forcing the index?

    Here is the plan as it look now btw.
    Name:  Clipboard01.jpg
Views: 185
Size:  15.7 KB
    Last edited by sapator; Mar 24th, 2017 at 03:14 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  23. #23
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: issue converting datetime to varchar breaks table indexes!

    it works if i force it the cluster index
    so with the index hint the query performs well, right? note that you do not force anything. it is just a hint and if the optimizer thinks its a bad hint it will not use it. the fact that it now uses the index Shows that the optimizer does accept the hint.
    for me this solution (+ removing the Option recompile) would be good enough and as Long as your data is evenly distributed performance should not degrade over time. If you are not happy with it, then you'd Need to rephrase the query. It is hard to assist you with this without knowing the data model Details.

  24. #24

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

    Re: issue converting datetime to varchar breaks table indexes!

    Hi.
    Maybe I didn't explain it well.
    what I'm saying is that it will work both ways, if I fix the '%like%' situation.
    So I would prefer to fix the '%like%' issue and have the optimized select the optimal solution, rather than just forcing a query hint and possibly forget it and have other issues if I come back to this query at some time and change something.

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

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

    Re: issue converting datetime to varchar breaks table indexes!

    If I was going to tackle something that looked like that I would first remove all the sum's and group by's and do a simple SELECT *.

    That would give me a record count that I know I need to come back to.

    Then using that simple new query I would start building a temp table that holds some of the PK's of the primary tables involved in all this. In places where you have NOT EXISTS I might try DELETE'ing rows in the TEMP TABLE that do not fit the criteria.

    Basically building up in steps - and seeing where if any place the build of this table gets slow or doesn't use an index properly.

    Then the production query uses this TEMP TABLE as the FROM and join's in what it needs.

    There is nothing wrong in doing several steps to get a result. It's what the query optimizer does with a big old ugly query like that anyway.

    *** 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

  26. #26

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

    Re: issue converting datetime to varchar breaks table indexes!

    Hi.
    Thanks for the info.
    Unfortunately( again ) the Crystal Report Version that is used to show the data is not supporting #temp table (another big topic a couple of months ago here).
    So I cannot use a temp table . But I could insert the data to the reporting table. But this will take the extra insert effort?

    Also if i use a temp table to hold the '%like%' values (as shown) and do an "in" instead then the execution plan works well.
    Last edited by sapator; Mar 27th, 2017 at 05:01 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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

    Re: issue converting datetime to varchar breaks table indexes!

    Wow - brick walls in all directions!

    Can you make VIEW's in the reporting database?

    Making a real table in your reporting region might cost more time - or not.

    You could still try the method I described and then try to bury it into a set of derived queries and sub-queries.

    *** 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

  28. #28

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

    Re: issue converting datetime to varchar breaks table indexes!

    Hi.
    Yes I might be able to try a view or not. I remember having tons of troubles with views last time (i think you where responding that post too) and again, not sure that CR version accepts that (although not 100% sure for the view(.
    Well here is the thing.
    Running the Crystal Report will just take one second. This have happened, also with the previous non sargable and slow insert table.
    I'm thinking that CR is paging the data else I cannot, for the life of me, explain the fast response.

    OK but basically, I have no real issue now, since the temp table with the "in" is working fine and indexing correctly.
    So I think, doing a full re-invent of the query will be time consuming for something that is working right now.
    I can do that if I find some time, so I can re-write the query correctly but I'm on a very tight schedule.

    One thing tho, and I have asked this before but I can't really grasp it.
    The sum on tbltrans_inventory. That damn thing.
    Can I get rid of it? I don't know who put it there like that. Is there a way to make this more readable? I see that is summing at the beginning. What would be the equivalent for example for one row?

    talking about this:
    Code:
    LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
                                  SUM(TransI_curValueEach) AS TransI_curValueEach,
                                  SUM(TransI_curNetTotal) AS TransI_curNetTotal,
                                  SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
                           FROM tblTrans_Inventory 
                           GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I
    and how would I go an make the equivalent on, p.e. this:
    Code:
    THEN
    					round(T.TransT_intNoOfSeats * (IsNull(T.TransT_curRedempValueEach,0) + IsNull(I.TransI_curValueEach,0)),4)
    thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

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

    Re: issue converting datetime to varchar breaks table indexes!

    That LEFT JOIN of a derived table make me think that VIEW's won' t work for you. That looks like a VIEW to me...

    If I was building this in a SPROC that would be a TEMP TABLE (or TABLE VARIABLE if a small enough number of rows).

    By JOIN'ing to this ENTIRE population of lgnNumber rows should mean this sub-query is built once and nicely applied to each main query row.

    If it is expensive to build this SUM() data AND you are building it for TOO MANY rows then I would consider trying something different.

    The other method that quickly come to mind is a sub-query right where the IsNull(I.TransI...) business is.

    But that would require TransI_lgnNumber and TransI_intPackageGroupNo be part of a compound index.


    Why is it a problem for you?

    *** 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

  30. #30

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

    Re: issue converting datetime to varchar breaks table indexes!

    It may seem like a problem may because I haven't grasp the mechanism entirely.
    should have to look further to it to understand that summing.

    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