5

I see that sql join internally functions always on some of the three basic operations : nested loops join, merge join and hash join.

From multiple articles on the internet I see SQL casting is said to be able to impact performances severely and almost always the reason is it disables index usage.

But to me it looks it could always be avoided: Casting could disable merge join usage (and merge benefits from index usage) but hash join, that is said to be generally faster than merge, can still be utilized in the same way(looks to me). No matter in which phase of the hash join (build of probe) casting is applied, all that has to be additionally done is casting, before hashing, for the column casted.

Hash join, anyway, passes every row of the first table once, same as for the second table. Hence, from my aspect, casting of join columns, applied to hash join, has not performance impact but the casting operation itself. And the guys on the internet say casting severely impacts performances since indexes cannot be used?

Is something wrong in my thinking above? Thank you for the time

migrated from serverfault.com Dec 18 '13 at 17:50

This question came from our site for system and network administrators.

  • Hi and welcome on serverfault. I just upvoted your question, but flagged it as it is more likely to be answered on the dba sister site. – Eric DANNIELOU Dec 18 '13 at 15:10
  • Thanks. I do not see the flag. What is the url for the sister site? – Срба Dec 18 '13 at 15:17
  • The flag will result in a move if a total of five users vote for the move. No need to do anything – Dave M Dec 18 '13 at 15:33
  • 2
    Your thinking is fine. Any particular RDBMS in mind? Discussing the problems of casting will be best done with reference to particular implementations. A related question for SQL Server Should I join datetime to a date using cast or range? – Martin Smith Dec 18 '13 at 18:10
  • 1
    Well you also seem to be assuming that all joins are joining full tables. For the vast majority of OLTP queries this is not the case so the inability to seek efficiently into the few rows that are needed is a bit of a show stopper. – Martin Smith Dec 24 '13 at 14:20
3

CAST or any other function is expensive when used in a JOIN condition because it makes the transformed column(s) be non-SARGable.

The database has no way to know what the output of the function will be until it runs the function. This is as true for CAST as it is for a UDF that you write which does some fancy string manipulation logic.

The condition being non-SARGable means that the database can't use a search argument to find the value - it has to run it on every single row that matches the other criteria in the query.

For this reason, functions should be avoided in any filtering logic - JOINs, WHERE clauses, etc. wherever possible, as it essentially forces a table/index scan.

  • 3
    This depends on the CAST and the optimizer implementation to some extent though. e.g. In principle casting a datetime column column to date or an int to bigint wouldn't necessarily prevent a seek. Nor would it theoretically prevent the index being used for merge join without a sort. – Martin Smith Dec 18 '13 at 19:34
  • Another exception is if there is a computed/persistent (and indexed) column with the same expression as the cast. – ypercubeᵀᴹ Dec 21 '13 at 14:39
  • Thanks JNK. My question was, though, shouldn't non-SARGability (thanks to introducing the term to me) be avoided from any SQL engine implementer by hash-join usage in the way I have described? The hash join will,you are right,run on every single row of both tables exactly once,but hash join is generally described as more optimal than merge? Is that the issue here - that merge is much more optimal than hash in the case since if the existing indexes are used, the merge's seek will always be faster the hash's search? Thanks – Срба Dec 24 '13 at 14:25
  • 1
    @Срба I think it's misleading to imply that one join operation is superior to another without any context. Hash joins are the most flexible join type, and merge joins are the LEAST flexible. To get a merge you need to have both inputs in the same order on the same key, which is a very special case. If you're using a non-sargable condition you're not going to get great performance on medium to large data sets. – JNK Dec 24 '13 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.