CODEWITHSUNDEEP
sqloptimizationjoinuser-defined-functions
Thierry
Thierry

Reputation: 133

Sql Join on User Defined Function: how to optimize

I'm trying to optimize a query in a database. That query is similar to the following: 

select * from Account 
   inner join udf_Account('user') udfAccount 
   on Account.Id = udfAccount.AccountId

Actually the real query is much longer but the most important point is that it contains a few inner join on user defined functions (udf) which depend on the user id. (So this is constant parameter which do not change during the query evaluation).

Due to a large amount of data, my query takes approximatively 20 seconds on a production database which is not acceptable.

I have already seen that by storing the results of the functions in temporary tables and using these tables in the query reduces a lot the duration of the query.

I'm asking the following questions:

  1. Can I avoid the temporary tables. Isn't it a way to tell sql that the function can be evaluated only once ? Using temporary tables would imply some important changes in my code this is why I would be happy if I had another solution.

  2. Are there any other ways to optimize my query ?

Upvotes: 0

Views: 11548

Answers (2)

MatBailie
MatBailie

Reputation: 86765

In SQL Server, if your functions are Inline rather than Multi-Statement, SQL Server explands tham (macro-like) into your queries. It's just like they become sub-queries in your main query.

This notionally allows the optimiser to make a 'better' execution plan.

For example; Provided that the fields you are joining on are directly derived from their source tables, this should make indexes on those fields available.

Without looking at the whole query and your individual functions, it appears that you're already in a good place with regards to your syntax. The next place to look is at the indexes that exist, and aim for index-seeks rather than table-scans or index-scans.

(That's all a bit simplistic, but it's a good start for query optimisation, which is an immense topic.)


Another option is to look at using CROSS APPLY with your inline table valued functions.
(Available in SQL Server 2005 onwards)

This allows the values from tables in your queries to be used as parameters to your functions. Again, provided that the functions are inline, SQL Server expands the function inline when building the execution plan.

An example could be...

SELECT
  Account.AccountID,
  subAccount.AccountID        AS SubAccountID,
  Balance.currentAvailable    AS SubAccountBalance
FROM
  Account
CROSS APPLY
  dbo.getSubAccounts('User', Account.AccountID) AS SubAccount
CROSS APPLY
  dbo.getCurrentBalance(SubAccount.AccountID)   AS Balance
WHERE
  Account.AccountID = 1234

Upvotes: 5

alexg
alexg

Reputation: 3055

I believe you want to define what mysql calls a "deterministic" function. Depending on your flavor of SQL this will have different syntax. But ultimately the biggest optimisation would be to not use a function at all, but simply add an account column to the user table.

Upvotes: 1

Related Questions