Why would SQL cast have to be expensive if used on a join column

performance

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

Best Answer

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.

Related Solutions

Sql-server – Performance hit using CAST in T-SQL

If the cast of the column is to exactly the same datatype and length and the seek predicate is a literal it does indeed seem to disregard it or treat it as a no-op and does an index seek on equality.

Seek Keys[1]: Prefix: [tempdb].[dbo].[#test].name = Scalar Operator(N'rpc')

If the cast of the column is to the same datatype but greater length and the seek predicate is a string literal it causes an index scan. This is obviously to be avoided.

If the cast of the column is to the same datatype and the same or greater length and the seek predicate is a local variable it adds a compute scalar operator to the execution plan. This calls GetRangeThroughConvert and outputs a range.

This range is used to do an index seek and seems pretty efficient

Seek Keys[1]: 
Start: [tempdb].[dbo].[#test].name > Scalar Operator([Expr1006]), 
End: [tempdb].[dbo].[#test].name < Scalar Operator([Expr1007])

Testing Code

SELECT *
 INTO #test
  FROM [master].[dbo].[spt_values]

CREATE NONCLUSTERED INDEX [ixname] ON #test
(
    [name] ASC
)

DECLARE @name NVARCHAR(MAX)

SET @name = 'rpc'

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))= @name --Cast the same and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))=@name --Cast to longer and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))='rpc' --Cast the same and literal

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))='rpc' --Cast to longer and literal

Mysql – Why would MySQL do serial synchronous I/O

First let me clarify by confirming that MyISAM does not do asynchronous I/O, but that InnoDB does and will by default from MySQL 5.5. Prior to 5.5 it used "simulated AIO" by using worker threads.

I think it is also important to distinguish between three situations:

Multiple queries executing at once
A single query executing in parallel
Some sort of logical read ahead for table scans / clear cases where the next pages are well known.

For (1) I/O will be able to execute in parallel for this. There are some limits with MyISAM: table locking and a global lock protecting the key_buffer (index cache). InnoDB in MySQL 5.5+ really shines here.

For (2) this is currently not supported. A good use case would be with partitioning, where you could search each partitioned table in parallel.

For (3) InnoDB has linear read-ahead to read a full extent (group of 64 pages) if >56 pages are read (this is configurable), but there is room for further enhancement. Facebook has written about implementing logical-readhead in their branch (with a 10x perf gain on tablescans).

Best Answer

Related Solutions

Sql-server – Performance hit using CAST in T-SQL

Mysql – Why would MySQL do serial synchronous I/O

Related Question