Doubt in a SQL Server function

  • Hello,

    I've got a doubt in a SQL Server function that you may help me.

    As you can see in the code, since i use variables @startdate in the select statement, the statement will freeze eternally.

    I can't understand why, can you help me?

    declare @sd as datetime

    declare @sd-aux as datetime

    set @sd = cast('2010-07-23 07:00:00' as datetime)

    set @sd-aux = cast('2010-07-23 07:05:00' as datetime)

    print @sd

    print @sd-aux

    --This statement will function in less than 1 second

    SELECT COUNT(DISTINCT vUsrCrtd)

    FROM dbNAME.dbo.tblNAME WITH(NOLOCK)

    WHERE dUCrtd between '2010-07-23 07:00:00' and '2010-07-23 07:05:00'

    --This statement will NOT FUNCTION and stays freeze

    SELECT COUNT(DISTINCT vUsrCrtd)

    FROM dbNAME.dbo.tblNAME WITH(NOLOCK)

    WHERE dUCrtd between @sd and @sd-aux

    Thanks and regards,

    JMSM 😉

  • It seems to be correct. The only thing u need to do is to change variable @sd-aux to something like @sd_aux. Thats it.

  • i don't think you can have variable names with the dash in them...it's interpreted as trying to minus something from the value @sd

    change your variable to @sd adn @sdaux and you should be fine.

    also you should not use the NOLOCK hint. that's bad practice and could give you incorrect results.

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near '-'.

    Msg 128, Level 15, State 1, Line 12

    The name "aux" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • your code doesn't work. Need to change the dash to underscore. Since you didn't provide table layouts, I used a sys.tables and it functions as expected.

    declare @sd as datetime

    declare @sd_aux as datetime

    set @sd = cast('2010-07-23 07:00:00' as datetime)

    set @sd_aux = cast('2010-07-23 07:05:00' as datetime)

    print @sd

    print @sd_aux

    --This statement will function in less than 1 second

    SELECT COUNT(DISTINCT name)

    FROM sys.databases WITH(NOLOCK)

    WHERE create_date between '2010-07-23 07:00:00' and '2010-07-23 07:05:00'

    SELECT COUNT(DISTINCT name)

    FROM sys.databases WITH(NOLOCK)

    WHERE create_date between @sd and @sd_aux

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks a lot everybody.

    Regards,

    JMSM 😉

  • Did any of this resolve your issue? Can you post your solution or point to what the solution was?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hello again,

    Sorry for i didn't respond with the solution.

    My solution was asa follows.

    Thanks and regards,

    JMSM 😉

    declare @sd as datetime

    declare @sdaux as datetime

    set @sd = cast('2010-07-23 07:00:00' as datetime)

    set @sdaux = cast('2010-07-23 07:05:00' as datetime)

    print @sd

    print @sdaux

    --This statement will function in less than 1 second

    SELECT COUNT(DISTINCT vUsrCrtd)

    FROM dbNAME.dbo.tblNAME WITH(NOLOCK)

    WHERE dUCrtd between '2010-07-23 07:00:00' and '2010-07-23 07:05:00'

    --This statement will NOT FUNCTION and stays freeze

    SELECT COUNT(DISTINCT vUsrCrtd)

    FROM dbNAME.dbo.tblNAME WITH(NOLOCK)

    WHERE dUCrtd between @sd and @sdaux

  • I saw this same situation you describe recently. When hard coding the date values I got a seek and everything was fine. When I put the same date values into variables the optimizer chose a scan on the index, so it was a difference between about 0.10 seconds and 43 seconds. Ouch. I can't say I understand it and I'm still looking into it.

    Also, we've had occasion in the past to see the optimizer pick a better execution plan for >= and <= statements as opposed to the between. It is unusual, but it's another thing that I don't understand and when I have odd issues it's one of the things I change.

    I would be curious to see the execution plans for your two statements (assuming the second one will eventually complete.)

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Table DDL (CREATE TABLE statement(s)) for the table(s) involved including defined indexes would also help.

    I'd be interested to know if there is an index on the dUCrtd column.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply