# How to compare performance of two versions of a function?

I just saw this function definition:

``````create function dbo.f (@a int,  @b int)
returns integer
as
begin
return  case when
not exists (Select * from t1 where t1.col1 = @a)
AND @b > 0
then 1 else 0 end
end
GO
``````

Seeing a not exists I thought attention full table scan and tried to improve it

``````create function dbo.f (@a int,  @b int)
returns integer
as
begin
return  case when
exists (Select * from t1 where t1.col1 = @a)
OR @b > 0
then 0 else 1 end
end

GO
``````

My feeling is, that this transformation could have been done by an optimizer. It seems to be straight forward, but how can I be sure if he does?

Comment on Igor's answer: (comparison fixed thanks to Matts comment)

This inspires me to the following:

``````create function dbo.f (@a int,  @b int)
returns integer
as
begin
IF @b <= 0
RETURN 0

IF exists (Select * from t1 where t1.col1 = @a)
RETURN 0

RETURN 1
end
GO
``````
-
i have added a comment to my code – garik Jan 11 '11 at 11:07
The only thing I would say is what does the profiler tell you? MS included that tool for a reason, and I don't know what t1 looks like, nor could I guess because for sufficiently simple t1's, it will not make a big difference. My \$0.05 – jcolebrand Jan 11 '11 at 14:28

One thing you could do to compare these two versions is to rename one of them to f_old and execute them both inside the same batch, while including the actual execution plan and IO statistics. From there, it is a simple matter of comparing the plans and IO stats of each.

One thing you need to be wary of is that in your first statement you had IF @b > 0. In your second statement you have IF @b < 0. What happens if @b = 0? You have not covered that possibility.

Finally, I imagine you will find that your thoughts regarding NOT EXISTS vs. EXISTS is correct. EXISTS will process until the condition is met. NOT EXISTS requires a table scan (if not indexed) and an index scan (if indexed). On a small table, you will not see a huge performance gain, but it is always best to design your logic around EXISTS, as opposed to NOT EXISTS.

Matt

-
Agreed: @b <= 0, about EXISTS and NOT EXISTS it's a big question... not exists (Select * from t1 where t1.col1 = @a) or exists (Select * from t1 where t1.col1 <> @a). i hope optimizer will do it (change = to <>) – garik Jan 13 '11 at 1:37

I'm pretty sure SQL server would calculate the `Select * from t1 ...` first, and only then check to see whether the result exists or not. If that's true, then both versions should be essentially identical speedwise. I wonder if you could test this by running these functions on two large tables, and see if there is a consistent different in the time it takes for the functions to return?

-