Querying all records are true in sql-server - is casting expensive performance wise

Question

I have a table with a column of bit values. I want to write a function that returns true if all records of an associated item are true.

One way I found of doing it is:

Select @Ret = CAST(MIN(CAST(IsCapped as tinyInt)) As Bit) 
    from ContractCover cc
    Inner join  ContractRiskVersion crv on cc.ContractRiskId = crv.ContractRiskId
    WHERE crv.ContractVersionId = @ContractVersionId
    AND cc.IsActive = 1
    return @ret

But is the casting to int to get the minimum expensive? Should I instead just be querying based on say:

(count(Id) where IsCapped = 0 > 0) returning false rather than doing the multiple casts?

In the execution plan it doesn't seem like calling this function is heavy in the execution (but I'm not too familiar with analysing query plans - it just seems to have the same % cost as another section of the stored proc of like 2%).

Edit - when I execute the stored proc which calls the function and look at the execution plan - the part where it calls the function has a query cost (relative to the batch) : 1% which is comparable to other sections of the stored proc. Unless I'm looking at the wrong thing :)

Thanks!!

Answer 1

I would do this with an exists statement as it will jump out of the query from the moment it finds 1 record where IsCapped = 0 where as your query will always read all data.

CREATE FUNCTION dbo.fn_are_contracts_capped(@ContractVersionId int)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @return_value bit

    IF EXISTS(
      SELECT 1 
        FROM dbo.ContractCover cc
        JOIN dbo.ContractRiskVersion crv 
          ON cc.ContractRiskId = crv.ContractRiskId
       WHERE crv.ContractVersionId = @ContractVersionId
         AND cc.IsActive = 1
         AND IsCapped = 0)
      BEGIN
        SET @return_value = 0
      END
    ELSE
      BEGIN
        SET @return_value = 1
      END

    RETURN @return_value
  END

Compared to the IO required to read the data, the cast will not add a lot of overhead.

Edit: wrapped code in a scalar function.

Answer 2

Casting in the SELECT would be CPU and memory bound. Not sure how much in this case--under normal circumstances we usually try to optimize for IO first, and then worry about CPU and memory second. So I don't have a definite answer for you there.

That said, the problem with this particular solution to your problem is that it won't short-circuit. SQL Server will read out all rows where ContractVersionId = @ContractVersionId and IsActive = 1, convert IsCapped to an INT, and take the min, where really, you can quit as soon as you find a single row where IsCapped = 0. It won't matter much if ContactVersionId is highly selective, and only returns a very small fraction of the table, or if most rows are capped. But if ContactVersionId is not highly selective, or if a high percentage of the rows are uncapped, then you are asking SQL Server to do too much work.

Second consideration is that scalar-valued functions are a notorious performance drag in SQL Server. It is better to create as an in-line table function if possible, eg:

create function AreAllCapped(@ContractVersionId int)
returns table as return (
select 
  ContractVersionId = @ContractVersionId 
, AreAllCapped = case when exists (
      select * 
      from ContractCover cc 
      join ContractRiskVersion crv on cc.ContractRiskId = crv.ContractRiskId
      where crv.ContractVersionId = @ContractVersionId
        and cc.IsActive = 1
        and IsCapped = 0
      ) 
    then 0 else 1 end
)

Which you then can call using CROSS APPLY in the FROM clause (assuming SQL 2005 or later).

Final note: taking the count where IsCapped = 0 has similar problems. It's like the difference between Any() and Count() in LINQ, if you are familiar. Any() will short-circuit, Count() has to actually count all the elements. SELECT COUNT(*) ... WHERE IsCapped = 0 still has to count all the rows, even though a single row is all you need to move on.