CODEWITHSUNDEEP
sqlsql-serverperformance
Matheus Lacerda
Matheus Lacerda

Reputation: 6007

Why is this SQL CAST performatic?

I was refactoring a heavy and very badly written stored procedure in SQL Server when I saw this code in a WHERE clause:

SET @SELECT = @SELECT + '
          CAST(LOG_MODULO.DataHora AS DATE) >= '''+ @DATAINI + ''' AND
          LOG_MODULO.DataHora  <= '''+ @DATAFIM +' 23:59:00'' ' ;

LOG_MODULO.DataHora is a DATETIME column, @DATAINI and @DATAFIM are VARCHAR(15) variables, and yes, this query is constructed via string concatenation.

I refactored this to:

AND LOG_MODULO.DataHora BETWEEN @DATAINI AND @DATAFIM + ' 23:59:59'

But my version takes 5x the time to run than its predecessor.

Why is this? What should be a good way to code this without changing the input?

Upvotes: 0

Views: 72

Answers (1)

Jesse
Jesse

Reputation: 873

Since you converted it out of dynamic sql, with regular SQL, I would declare @datafim2 as datetime and set to dateadd(day,1,@datafim) ... then do dataHora >= @DataIni and datahora < @datafim2. Point being make the data types of your column the same as your variables.

Upvotes: 1

Related Questions