Very slow SQL join, with limited amount of data

Vote count:

0

We have a very slow running piece of SQL, and I was wondering if anyone has any advice on speeding it up.

We are collecting the data from a large number of tables (21) into a single table for later processing. The tables are temporary tables, and exist only for the query.

All the tables share three columns (USN, DATASET, and INTERNAL_ID), and the combination of the three is unique in each table, but the same values exist in all the tables. It it possible that INTERNAL_ID is also unique, but I am not sure.

Each table contains the same six rows of data, and the output table also contains six rows.

I.e, each table has the following data, plus additional columns

USN DATASET INTERNAL_ID
20  BEN     67
20  APP     68
30  BEN     70
30  BEN     75
50  CRM     80
70  CRM     85

The server is SQL 2008 R2 with 4 x 2.3GHz cores, 32GB memory, which is sitting idle and should be more than adequate.

The INSERT INTO query itself takes approximately 3 seconds.

What can I do to either find out the reason for the code being so slow, or to speed it up. If there a maximum number of joins that I should do in a single query?

CREATE TABLE #output (
  USN         INT,
  DATASET     VARCHAR(150),
  INTERNAL_ID INT
-- More fields
)

CREATE TABLE #master (
  USN         INT,
  DATASET     VARCHAR(150),
  INTERNAL_ID INT
-- More fields
)

CREATE TABLE #ex1 (
  USN         INT,
  DATASET     VARCHAR(150),
  INTERNAL_ID INT
-- More fields
)
-- Repeat for ex2 .. ex20

-- Insert data into master, ex1..ex20

INSERT INTO #output(<columns>)
  SELECT <columns>
  FROM
    #master
    LEFT JOIN #ex1 ON #master.USN = #ex1.USN AND
                      #master.DATASET = #ex1.DATASET AND
                      #master.INTERNAL_ID = #ex1.INTERNAL_ID
    LEFT JOIN #ex2 ON #master.USN = #ex2.USN AND
                      #master.DATASET = #ex2.DATASET AND
                      #master.INTERNAL_ID = #ex2.INTERNAL_ID
    -- continue until we hit ex20

asked 1 min ago

Simon Callan

1,531

Very slow SQL join, with limited amount of data