0

I have a SSIS package that inserts rows into a DB2 Table from MSSQL table.

It runs in about 2 minutes.

I have been tasked with converting this to non-SSIS solutions.

I have tried multiple solutions and all examples are very slow.

It would take about 90 minutes to insert the rows into the DB2 table using TSQL.

I have tried INSERT OPENQUERY, INSERT to linked server, dynamic SQL.

INSERT OPENQUERY(DB2, 'SELECT COL1 FROM SCHEMA.TABLE1') SELECT COL1 FROM ##MSSQLTable;

The DB2 table consists of 1 column and over 3 million rows.

Thx

bmiguelh
  • 15
  • 2
  • Inserting one row at a time is always slow...you'll need to insert 100s or even 1000s of rows at once for decent performance. – Charles Oct 31 '22 at 17:59

1 Answers1

0

I have 2 MSSQL 2012 servers running on my network. I created a test table on my "reporting" server in the AdvetureWorks2012 DB. I then created a query on my main production server that generates 3 million rows and inserts them into the reporting server table. Just the select statement to create the 3M records takes 14s to complete. The script below inserted about 1.8M records in 11 minutes.

Both servers are virtual machines running with 4 Cores @ 3GHz and 64GB RAM. The source/production server is running HDDs with about 1400 IOPS. The destination/reporting server is running un-benchmarked SSDs.

Here's the script I was using on the source server:

INSERT INTO [LinkedServerName].[AdventureWorks2012].[dbo].[TestInsert] (
    [uid]
    , test1
    , test2
    , test3
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY y.a, y.b, y.c, y.d, y.e, y.f, y.g) as [uid]
    , 1 as test1
    , 2000000 as test2
    , 30000 as test3
FROM (
        SELECT * 
        FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as tens(a)
             , (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as hundreds(b)
             , (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as thousands(c)
             , (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as tenthousands(d)
             , (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as hundredthousands(e)
             , (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as millions(f)
             , (VALUES (0),(1),(2)) as m3(g) --times 3 = 3 million records.
) as y

This is not a fast process and the data I'm pushing is trivial (4 integers). As other posts (here, here, here) have discussed, the fastest options are to use some kind of scripting to generate a .csv file at the source and then have your destination server import the file. Or, ironically, use an SSIS package.

Tim Jarosz
  • 1,133
  • 1
  • 8
  • 15