0

I have two tables with the same structure like so (let us call them initial_table1 and initial_table2):

tempid  name    num_records
1       a       2
2       b       2
3       c       2
4       d       2
5       e       2

I want to perform two operations to arrive at my desired end result (lets call it final_table).

  1. Perform a union on intial_table1 and initial_table2.
  2. Duplicate each row in the resulting table by num_records.

I achieve this by creating an auxiliary table of numbers and joining on the range over 1 and num_record. The union is straightforward creating a temporary table on the fly using UNION ALL. What I have shown below is the statement I have come up with:

WITH
    N1 AS (SELECT N1.[n] FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 ([n])),
    N2 AS (SELECT L.[n] FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.[n] FROM N2 AS L CROSS JOIN N2 AS R),
    N AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) AS [n] FROM N3) -- table of numbers going from 1 to 100 million sequentially
SELECT * INTO [dbo].[final_table] FROM
    (
    select * from initial_table1
        union all
    select * from initial_table2
    ) AS tmp
INNER JOIN (
    SELECT
        [n]
    FROM 
        n
    ) AS numbers
ON
    numbers.[n] BETWEEN 1 AND [tmp].[num_records];

This works well but the query times are quite high. The execution plan says the table insert step takes the highest toll (91% of cost). Both initial tables have about 100,000 rows. Can this query be optimized in some way or is the CTE approach not the most optimum in this case? I am using Microsoft SQL Server 2012

  • The percentages are just estimates, so those might not be correct always. But what's the point of the numbers table? Can't you just use identity field in final_table? – James Z Apr 8 '15 at 6:35
  • 2
    The point of the numbers table is to create the duplicates. But why would anyone need duplicate rows in a table, escapes me. And what is the maximum value in tne num_records column? Can it really be a million or 100 millions? Why would you want a million identical rows? – ypercubeᵀᴹ Apr 8 '15 at 7:28
  • Try to comment the Insert part, leave only the select ,and see if this part is running ok – Sabin Bio Apr 8 '15 at 7:32
  • 2
    For the efficiency issue, if the problems is caused by the numbers table, and you want this operation to be performed repeatedly, why don't you create a permanent numbers table? – ypercubeᵀᴹ Apr 8 '15 at 7:34
  • 1
    But CTEs are not cached, if that's what you meant. Every time you run this query, a 100M rows table has to be somehow instantiated in memory. (100M or 10K table, not sure, your comments say 100M but the code will result in 10K I think.) – ypercubeᵀᴹ Apr 9 '15 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.