CODEWITHSUNDEEP
c#sql-server
Manjuboyz
Manjuboyz

Reputation: 7056

Will casting and converting in Join will cause any performance impact?

I am working on new stored procedure and everything was set, during code review I got comment to address where it says I should not be casting/converting in joins instead asking me to convert DateTime to int date then compare those two.

Code:

INNER JOIN SchedData SD 
        ON SD.EmployeeID = CAE.EmployeeID
            AND CAST(CAST(SD.AssignDate AS VARCHAR(8)) AS DATETIME) BETWEEN @StartDate and @EndDate + 1

Explaination :

SD.AssignDate

is of Int date like 20100801

@StartDate and @EndDate

are parameters of DateTime.

when I searched online in SQL Forum, they say it will not make much/any difference, that is what I have commented back in review, but I just want to re-confirm from the guys who are well known in SQL, I am using MS Sql.

here is the link which says about cast in Sql Forum

SQL

Please let me know which is feasible to work w.r.t performance.

If there is a better way, I would be glad to implement that code.

Upvotes: 1

Views: 2051

Answers (3)

Nick.Mc
Nick.Mc

Reputation: 19204

When you cast a field it usually makes it non-sargable. (look it up). When it's non-sargable it can't make use of an index on the column AssignDate (although I've heard it's getting better at it in later versions of SQL). If you instead do the convert on the parameters, then you aren't doing a convert on the field and it becomes sargable and therefore able to utilise an index.

The link you posted is about casting in the select. It's not about casting on join or predicate columns which is what you're doing here.

For example change your code to this:

AND SD.AssignDate BETWEEN 
CONVERT(INT,CONVERT(VARCHAR(8),@StartDate,112)) 
AND
CONVERT(INT,CONVERT(VARCHAR(8),@EndDate,112))

Now you don't have a function around a field, and it can utilise a index on that column if it exists.... or if one exists in future

I suggest you use CTRL-L to observe whats happening and compare queries.

Here's some interesting background reading, basically saying that some functions around columns are OK.

http://blogs.lobsterpot.com.au/2010/01/22/sargable-functions-in-sql-server/

Upvotes: 2

The Shooter
The Shooter

Reputation: 733

As far as I can understand and guess 

AND CAST(CAST(SD.AssignDate AS VARCHAR(8)) AS DATETIME) BETWEEN @StartDate and @EndDate + 1

should be part of where clause and not inner join.

Also if 

SD.AssignDate

is already a date time field then there is not point converting it to varchar then converting it to date time.

Upvotes: 0

objectNotFound
objectNotFound

Reputation: 1783

from your own link :

As a general rule, doing CASTs or CONVERTs on items in your SELECT clause cost almost nothing. (Note that CASTs or CONVERTs in WHERE or ON clauses can be very expensive since they will keep SQL Server from making effective use of indexes).

Best way is to convert the Datatype of that column into a proper Date or Datetime if the no.of rows is reasonably small.

Upvotes: 0

Related Questions