Database Reference
In-Depth Information
For the next step, let's try to perform type conversion without the function, as shown in Listing 10-6.
Listing 10-6.
Multi-statement functions overhead: Select without scalar function
select count(*)
from dbo.Orders
where convert(datetime,convert(varchar(10),OrderDate,121))) = '2013-03-01'
The execution time for this query is:
SQL Server Execution Times:
CPU time=75 ms, elapsed time=82 ms.
You see that the statement runs almost five times faster without any multi-statement call overhead involved,
although there is the better way to write this query. You can check if
OrderDate
is within the date interval, as shown in
Listing 10-7.
Listing 10-7.
Multi-statement functions overhead: Select without type conversion
select count(*)
from dbo.Orders
where OrderDate>= '2013-03-01' and OrderDate<'2013-03-02'
This approach cuts execution time to:
SQL Server Execution Times:
CPU time=0 ms, elapsed time=5 ms.
As you see, user-defined multi-statement function and type conversion operations, which can be considered as
system functions, introduce huge overhead, and significantly increase query execution time. However, you would
hardly notice it in the execution plans. Figure
10-3
shows the execution plan for the queries that use user-defined
functions (Listing 10-5) and date interval (Listing 10-7).
