issue converting datetime to varchar breaks table indexes!-VBForums
Results 1 to 16 of 16

Thread: issue converting datetime to varchar breaks table indexes!

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    4,885

    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: 119
Size:  9.4 KB

    Name:  Clipboard02.jpg
Views: 110
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: 110
Size:  12.2 KB

    Name:  Clipboard04.jpg
Views: 106
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
    Slow as hell.

  2. #2
    Hyperactive Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    262

    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
    4,885

    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.
    Slow as hell.

  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
    CT
    Posts
    17,316

    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,073

    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
    CT
    Posts
    17,316

    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
    4,885

    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.
    Slow as hell.

  9. #9
    Hyperactive Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    262

    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
    CT
    Posts
    17,316

    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
    Hyperactive Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    262

    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
    4,885

    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.
    Slow as hell.

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,316

    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
    4,885

    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.
    Slow as hell.

  15. #15

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    4,885

    Re: issue converting datetime to varchar breaks table indexes!

    No.
    Disregard, I was getting another set of data.
    Problem remains.
    Slow as hell.

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

    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

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.