Sql-server – How to store function names to be executed on a subset of rows

sql serversql-server-2005t-sql

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.

Best Answer

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.

Related Solutions

Sql-server – Performing a “Spread Negitive” over a dataset

Often with operations like running totals it is actually more efficient to use a cursor than some of the other methods. Don't be afraid of using a cursor unless you are seeing noticeable performance issues.

In SQL Server 2012 there are new window functions that make this better, but obviously you can't use those. There is a quirky update approach that "works" but the syntax isn't officially supported and might not work post-SQL Server 2012 - it may someday become illegal syntax and there is never any guarantee about how the ordering works. The typical set-based approaches don't scale well - with a cursor it is often better because you only have to scan each row once, whereas with a set-based solution the scanning grows non-linearly. I wish I could show you the work I've been doing on this, but the publication date of the blog post that will reveal it all is still unknown.

At the very least when you declare a cursor use:

DECLARE ... CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

This also might be useful reading:

Bad habits to kick: Thinking a while loop isn't a cursor

Sql-server – Replace cursor with set-based approach

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

Best Answer

Related Solutions

Sql-server – Performing a “Spread Negitive” over a dataset

Sql-server – Replace cursor with set-based approach

Related Question