Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations on each row of accounts based on the accountType. The best method I can think of for doing this would be to create UDFs, then store the names of the UDFs in a table typeFunctions, consisting of rows of accountTypeID and functionName, as in: P, "f_ageOfAccount" and B, "f_daysSinceLastcontact".

Unfortunately, joining this table to the accounts table and looping through it to exec the function name seems very kludgy.

Is there a better way to approach this problem?

Context: Currently, the legacy version of this application stores ad hoc SQL in typeFunctions and execs the SQL, looping through the accounts with a cursor. Obviously, this has some performance implications and is not optimal when dealing with complex calculations.

share|improve this question
is it required to run one row at a time? you could probably use the cursor for each different accountTypeId then process sets of rows "where accountTypeId = x". You may need to add validations, so that you can ensure the function results don't violate business rules. e.g. consider. update accounts set balance = fn_x(accountID) where accountTypeId = 'P' and fn_x(accountId) >= 0. Say for instance you don't allow balances to go below zero. You still get benefit of set based operation, but run function across all records of a specific type which don't violate rules – John DaCosta Jul 3 '11 at 23:22
Will using a cursor like this--which would result in two to five passes depending on the client--be more efficient? – syrion Jul 4 '11 at 13:23
The other option would be to do dynamic sql using the function. – John DaCosta Aug 16 '11 at 3:26
it really depends, my suggestion was to use in case you had to use nested cursors. I would test the process doing 1. a cursor , 2. dynamic sql , 3. an SSIS Package (if process can be scheduled vs. run adhoc). – John DaCosta Aug 16 '11 at 3:30

5 Answers

up vote 2 down vote

Any time you use a scaler function you have to run the function once for each row. Complex CASE statements will usually be faster for SQL to process, even though there are painful to read.

share|improve this answer
If I use a CASE statement, we lose per-client account types and functions, unless I rewrite the stored procedure per client, which is not an option. – syrion Jun 30 '11 at 21:11
1  
You don't have to. "SELECT Something, CASE WHEN AccountType = 2 then Col3*Col2 WHEN ACcountType = 1 then Col3+Col2 else 0 end ..." This is very rough, but you get the idea. If it breaks down to you have to use functions, then the performance side effect will have to be accepted. – mrdenny♦ Jun 30 '11 at 21:15
Okay, I can see your point. However, how do I take these arbitrary actions which are connected to the type of account, and build them into a case statement that is going to be efficiently executed? I may end up with a hundred-line case statement based on the contents of a table. Can I build and compile that once, to make it more efficient? – syrion Jul 1 '11 at 13:40
If the case statement is included within the select statement it'll only need to be compiled the first time the query is executed, after that the execution plan will be cached. Putting code within a function or stored procedure doesn't cause the code to be pre-compiled. – mrdenny♦ Jul 4 '11 at 6:37
up vote 1 down vote

"I want to perform calculations on each row of accounts based on the accountType": Can you not use a cursor to do this? Cursors give your nice control when you want to do a row by row manipulation.

share|improve this answer
I can, but my understanding is that the performance is abysmal... – syrion Jul 2 '11 at 14:00
up vote 1 down vote

Unfortunately, I can't think of any better way. Since you want both Account Types and Function Names to be dynamic, you'll have to store them in the database. What you proposed would definitely solve the problem.

A second idea would be to use extended stored procedures / CLR assemblies to call the function externally (or to use those functions to get the function names). But if you thought your design was kludgy...

share|improve this answer
up vote 0 down vote

I suggest that you step back and look at it from a business-rules perspective. Does each type of account have different functions that need to be run? Are some functions shared? Do certain functions need to behave differently when presented with differing account types?

We need this information also to continue.

share|improve this answer
There are some functions which are shared, and some which are unique. I am not ruling out polymorphism based on account type, either, although it does not exist at the moment. – syrion Jun 30 '11 at 18:22
up vote 0 down vote

Why don't you create separate views for each account type and then use a computed column.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.