SQL Server – Is Function Overloading Possible?

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

1. Create a new, blank database for our tests:

   USE master;
   IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
   BEGIN
       ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
       DROP DATABASE mv;
   END
   GO
   CREATE DATABASE mv;
   GO
   

2. Create a table, a multi-statement function, and a table-valued-function:

   USE mv;
   GO
   CREATE TABLE dbo.t
   (
       t_id int NOT NULL
           CONSTRAINT PK_t
           PRIMARY KEY CLUSTERED
   );
   GO
   
   CREATE FUNCTION dbo.t_func
   (
       @t_id int
   )
   RETURNS bit
   WITH SCHEMABINDING
   AS
   BEGIN
       DECLARE @r bit;
       IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
           SET @r = 1
       ELSE
           SET @r = 0;
       RETURN @r;
   END
   GO
   
   CREATE FUNCTION dbo.t_tvf
   (
       @min_t_id int
       , @max_t_id int
   )
   RETURNS TABLE 
   WITH SCHEMABINDING
   AS
   RETURN (
       SELECT t_id = t.t_id
           , e = dbo.t_func(dbo.t.t_id)
       FROM dbo.t
       WHERE t.t_id >= @min_t_id
           AND t.t_id <= @max_t_id
   );
   GO
   

3. Insert some sample data into the table:

   INSERT INTO dbo.t (t_id)
   SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
   FROM sys.syscolumns c;
   GO
   

4. Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

   CREATE TABLE dbo.function_stats
   (
       run_num int NOT NULL
       , object_name sysname NOT NULL
       , execution_count int NULL 
       , CONSTRAINT PK_function_stats
           PRIMARY KEY CLUSTERED (run_num, object_name)
   );
   GO
   INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
   SELECT 1
       , o.name
       , COALESCE(fs.execution_count, 0)
   FROM sys.objects o 
       LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
   WHERE o.name = 't_func';
   GO
   

5. Run a query against the TVF:

   SELECT t.*
   FROM dbo.t_tvf(1, 2) t;
   GO
   

6. Capture the execution stats now:

   INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
   SELECT 2
       , o.name
       , COALESCE(fs.execution_count, 0)
   FROM sys.objects o 
       LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
   WHERE o.name = 't_func';
   

7. The function stats results:

   SELECT *
   FROM dbo.function_stats fs
   ORDER BY fs.run_num
       , fs.object_name;
   

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.

Why don't you call a stored procedure via NHibernate instead of asking it to query the view? That would allow you to write your own query and tune as needed....

I don't know NHibernate very well but this blog post looks like a reasonable resource. In case that link dies here are the basic steps:

1. Write your stored procedure to select the data in the most efficient manner.
2. Write a class that represents the data you're selecting (a DTO would be perfect).
3. Write a NHibernate mapping that tells it how to associate the fields
4. Specify the name of the stored procedure to use and any parameters when you query the database via NHibernate.

It might be a good idea to check out this article on StackOverflow related to tuning performance when using NHibernate.