Doubt in a SQL Server function

Question

Post reply

Doubt in a SQL Server function

JMSM

SSCertifiable

Points: 5440
More actions
July 23, 2010 at 4:24 am

#387414

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 😉

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

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

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 1

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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

Mike01 SSChampion Points: 11395 More actions · Answer 3

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/

JMSM SSCertifiable Points: 5440 More actions · Answer 4

JMSM

SSCertifiable

Points: 5440

July 23, 2010 at 8:49 am

#1197555

Thanks a lot everybody.

Regards,

JMSM 😉

Mike01 SSChampion Points: 11395 More actions · Answer 5

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/

JMSM SSCertifiable Points: 5440 More actions · Answer 6

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 7

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.