SQL User Defined Functions Are Slower Than Inline Logic
In one of my other posts on comparative SQL performance and optimization, Jason Rushton stated that in his experience, using a SQL user defined function in a query performed up to 4 times slower than the equivalent inline logic. Gus also backed this up with his opinion that UDFs should be used only when necessary.
If you stop and think about it, user defined functions in any language are going to be slower than using the equivalent inline logic; it adds more processing instructions and overhead to the overall algorithm and more processing = more slower. To see this in action, I set up this little test:
<!---
Get all the IDs as strings using a SQL user
defined function, ToString().
--->
<cftimer
type="outline"
label="User Defined Function">
<!--- Start running total. --->
<cfset intTotal = 0 />
<!--- Loop over this method 10 times. --->
<cfloop index="i" from="1" to="10" step="1">
<!--- Query for IDs. --->
<cfquery name="qID" datasource="#REQUEST.DSN.Source#" result="objResult">
SELECT
dbo.ToString( b.id ) AS id_string
FROM
blog_entry b
</cfquery>
<!--- Output execution time of query. --->
#objResult.ExecutionTime# ms /
#qID.RecordCount# records
<br />
<!--- Update total. --->
<cfset intTotal = (intTotal + objResult.ExecutionTime) />
</cfloop>
<!--- Output average run time. --->
Average: #Fix( intTotal / 10 )#<br />
</cftimer>
<!--- Get all the IDs as strings using inline SQL. --->
<cftimer
type="outline"
label="Inline Logic">
<!--- Start running total. --->
<cfset intTotal = 0 />
<!--- Loop over this method 10 times. --->
<cfloop index="i" from="1" to="10" step="1">
<!--- Query for IDs. --->
<cfquery name="qID" datasource="#REQUEST.DSN.Source#" result="objResult">
SELECT
LTRIM( RTRIM( STR( b.id ) ) ) AS id_string
FROM
blog_entry b
</cfquery>
<!--- Output execution time of query. --->
#objResult.ExecutionTime# ms /
#qID.RecordCount# records
<br />
<!--- Update total. --->
<cfset intTotal = (intTotal + objResult.ExecutionTime) />
</cfloop>
<!--- Output average run time. --->
Average: #Fix( intTotal / 10 )#<br />
</cftimer>
Here, we are converting the row ID to a string, first by using the SQL user defined function, ToString() and then afterwards by putting the same logic inline. Running the above code, we get the following times:
SQL User Defined Function
178 ms
157 ms
165 ms
179 ms
139 ms
160 ms
160 ms
68 ms
154 ms
140 ms
Average time: 150 ms
Equivalent Inline SQL Logic
100 ms
142 ms
157 ms
100 ms
115 ms
156 ms
135 ms
156 ms
159 ms
100 ms
Average time: 132 ms
Note: I ran the code 10 times (listed above) giving me the average of 10 averages.
When running this on a 50,000+ record set, it looks like (as would be expected) the inline logic is performing better than the user defined function. I am not, however, seeing the 4x speed difference that Jason had mentioned. Of course, my logic is uber simple and the UDF logic is also very small.
While the difference here does not jump out and slap you on the ass, the difference in performance is there; as such, it is something to think about when you are optimizing your queries. Of course, you have to take into account the ease of coding vs. performance requirements. If putting logic inline gives you a 30 ms improvement on a report that gets run once a day, is that worth NOT getting the benefits of a UDF? Probably not. But if this is a query that either sees a huge performance hit, slows down the rest of a site, or gets run very often, every few milliseconds you can squeeze out can make a difference.
Want to use code from this post? Check out the license.
Reader Comments
CFC's are slower than inline logic as well... ;)
<ducks>
Ha ha ha :)
Assembly is faster than CF
**Grabs Pie and throws**
DW
My Mustang is faster than your Camero.
User Defined Functions can also execute faster on average then inline logic if they are immutable and the database supports result caching.
However, the real problem of User Defined Functions is not the overhead in invoking them, but that from the perspective of the planner they usually behave like a black box. They do not have an adequate selectivity definition and they do not have an adequate cost definition. For a thorough explanation you should read Optimization of Queries with User-Defined Predicates http://ee.snu.ac.kr/~shim/tods.pdf
@Jochem,
I have put that PDF in my "To Be Reviewed" folder. Skimming over it, it seems to have a lot of higher level math and things of that nature... gonna need to set aside some time to really pick through it. Thanks.
I think it's important to note that a procedure invocation in a language like SQL need not be slower than the equivalent inline logic. Specifically when the logic is complex.
SQL is parsed (generally speaking), at runtime, and reparsed every time the query is executed unless the database is really smart about caching operations. Parameterizing the query usually helps significantly, and depending on the database, may provide *massive* performance boosts. For instance Oracle bogs down really fast unless you use bind params.
However, complex logic that can be wrapped up in a UDF can provide a real performance boost if the database can determine that the UDF can be compiled and stored and then later when a dynamic query is executed, that the database needs to recompile each time, that part of the query will be excluded from the recompile.
Nothing prevents a database from inlining the logic right into the real query if the database can determine the resulting (or lack of) side effects.
We should look at a lot of factors when deciding how to structure our database calls and how to handle queries, because there's a whole lot more going on than a simple function call that's going to get in the way of performance.
I'd certainly be more worried about indexes, materialized or indexed views, and most importantly the algorithms, such as set based solutions to complex tasks, instead of worrying about the constant time execution overhead that comes from UDF invocation.
@Elliott,
All good points. I think I am still at a point where I don't have complex logic either inline or in UDFs. Perhaps I am just not experienced enough to see how much of my processing can be moved into SQL (as opposed to trying to leverage ColdFusion post-query).
Elliot - when you say "Nothing prevents a database from inlining the logic right into the real query if the database can determine the resulting (or lack of) side effects", what factors go into allowing the database to determine a lack of "side effects" and what possible side effects could there be of a UDF?
I ask because we have a stored procedure that takes a @firstName parameter and does this
SELECT x, y, z
WHERE udf_CompareText(x, @name) = 1
to offload a multistep comparison process. Scaled down a bit, the udf looks like this:
CREATE FUNCTION udf_CompareText (@param varchar(50), @valueInTable varchar(50))
...
IF (
(@param = @valueInTable) OR
(charindex(@param, @valueInTable) > 0) OR
(charindex(@valueInTable, @param) > 0) OR
(soundex(@param) = soundex(@valueInTable))
) return 1
etc.
We have an index on the firstName column. Inlining this comparison is significantly faster than doing it in the UDF. Is there any way to help my database engine (SQL Server) to, as you say, inline the logic implicitly?
I can confirm that UDFs (table functions - not scalar) are up to 4x slower than inline SQL logic. I just created an UDF with a simple select statement. It takes 60s+ to complete while the plain select statement is finished after 15s. I have no idea how this MAY happen.
inline is prone to SQL Injection attack. Its more faster that other but more vulnerable to SQL injection attacks.