Change an iterative query to a relational set-base

问题:

SQL Fiddle

I'm trying without success to change an iterative/cursor query (that is working fine) to a relational set query to achieve a better performance.

What I have:

table1

| ID | NAME |
|----|------|
|  1 |    A |
|  2 |    B |
|  3 |    C |

Using a function, I want to insert my data into another table. The following function is a simplified example:

Function

CREATE FUNCTION fn_myExampleFunction
(
  @input nvarchar(50)
)

RETURNS @ret_table TABLE 
(
  output nvarchar(50)
)

AS

BEGIN
  IF @input = 'A'
    INSERT INTO @ret_table VALUES ('Alice')
  ELSE IF @input = 'B' 
    INSERT INTO @ret_table VALUES ('Bob')
  ELSE
    INSERT INTO @ret_table VALUES ('Foo'), ('Bar')
RETURN
END;

My expected result is to insert data in table2 like the following:

table2

| ID |  NAME |
|----|-------|
|  1 | Alice |
|  2 |   Bob |
|  3 |   Foo |
|  3 |   Bar |  

To achieve this, I've tried some CTEs (Common Table Expression) and relational queries, but none worked as desired. The only working solution that I've got so far was an iterative and not performatic solution.

My current working solution:

BEGIN
  DECLARE 
    @ID int, 
    @i int = 0,
    @max int = (SELECT COUNT(name) FROM table1)

  WHILE ( @i < @max ) -- In this example, it will iterate 3 times
  BEGIN

    SET @i += 1

    -- Select table1.ID where row_number() = @i
    SET @ID =
          (SELECT 
            id 
          FROM
            (SELECT 
              id,
              ROW_NUMBER() OVER (ORDER BY id) as rn
            FROM 
              table1) rows
          WHERE
              rows.rn = @i
          )

    -- Insert into table2 one or more rows related with table1.ID
    INSERT INTO table2
      (id, name)
    SELECT
      @ID,
      fn_result.output
    FROM
      fn_myExampleFunction (
        (SELECT name FROM table1 WHERE id = @ID)
      ) fn_result

    END
END

The objective is to achieve the same without iterating through the IDs.

回答1:

if the question is about how to apply a function in a set oriented way, then cross apply (or outer apply) is your friend:

insert into table2 (
    id, name
) select
   t1.id,
   t2.output
from
   table1 t1
       cross apply
   fn_myExampleFunction(t1.name) t2

Example SQLFiddle

If the non-simplified version of your function is amenable to rewriting, the other solutions will likely be faster.

回答2:

A query like this will do what you want:

insert into table2(id, name)
    select id, (case when name = 'A' then 'Alice'
                     when name = 'B' then 'Bob'
                     when name = 'C' then 'Foo'
                end)
    from table1
    union all
    select id, 'Bar'
    from table1
    where name = 'C';

回答3:

Why wouldn't you store this data as a table? It's relational. Coding it in a function or stored procedure seems less than ideal.

In any case, I hope the following gives you ideas about how to improve your code. I realize that you said your function is more complicated than your example, but you can still use this idea even inside of the function as necessary.

INSERT dbo.table2 (ID, Name)
SELECT
   T1.ID,
   N.FullName
FROM
   dbo.table1 T1
   INNER JOIN (VALUES -- A "derived table" made up of only constants
      ('A', 'Alice'),
      ('B', 'Bob'),
      ('C', 'Foo'),
      ('C', 'Bar')
  ) N (ShortName, FullName)
      ON T1.Name = N.ShortName
;

But of course, that could just be rendered INNER JOIN dbo.NameTranslation N if it were in a real table (and then updating it would be so much easier!).

If your function absolutely can't be rewritten to be relational (it must take a single name at a time) then you would use CROSS APPLY:

INSERT dbo.table2 (ID, Name)
SELECT
   T1.ID,
   N.OutputName
FROM
   dbo.table1 T1
   CROSS APPLY dbo.YourFunction(T1.Name) F
;

However, this will not perform very well for large rowsets. Rewriting the function to be the type that RETURNS TABLE is a step in the right direction (instead of RETURNS @variable TABLE (definition)).