Data Insert issue

Question

Data Insert issue

thbaig

Hall of Fame

Points: 3003
More actions
December 23, 2013 at 4:41 am

#280628

Hi,
I working on migrating data from one MSSQL instance to another.This will be ongoing process so I have to write a stored proc and schedule it.
Following are code from my Sp.
I am using open query to fetch results from first instance.
Following query populates temp table in 7 millisec with around 1.5 Million rows
SELECT *
INTO #tmpPrice
FROM OPENQUERY(MSSQL1, '
SELECT Id -- int
,Key -- Int
,Flag --TinyInt
FROM tblPricingExc');
Now I am using following query to insert records in my MSSQL2 table. It is taking hours to insert records.
WHILE @@rowcount > 0
BEGIN
INSERT INTOtblPricingExc2 --table tblPricingExc2 has an Identity column as PK.
( Key ,
Flag ,
Id
)
SELECT --TOP (10000)
tmpExc.Key ,
tmpExc.Flag ,
tmpExc.Id
FROM tblPricingExc2 exc
RIGHT JOIN #tmpPrice tmpExc ON tmpExc.Id = exc.Id -- Used right join to fetch all results
--used following to insert only those records that don't exist in tblPricingExc2
WHERE ( EXc.Id IS NULL
AND exc.Key IS NULL
);
END
I have tried TOP (without TOP), While (Without while) but it always remain same.
Can you please suggest , how can I do that faster ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

After creating your temp table, try putting an index on its Id column.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

thbaig Hall of Fame Points: 3003 More actions · Answer 2

Phil Parkin (12/23/2013)
After creating your temp table, try putting an index on its Id column.

Phil,

I have done so, but have same results.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

You could also try forcing transaction commits in your WHILE loop:

WHILE @@rowcount > 0

BEGIN

begin tran

INSERT INTO tblPricingExc2 --table tblPricingExc2 has an Identity column as PK.

( Key ,

Flag ,

Id

)

SELECT --TOP (10000)

tmpExc.Key ,

tmpExc.Flag ,

tmpExc.Id

FROM tblPricingExc2 exc

RIGHT JOIN #tmpPrice tmpExc ON tmpExc.Id = exc.Id -- Used right join to fetch all results

--used following to insert only those records that don't exist in tblPricingExc2

WHERE ( EXc.Id IS NULL

AND exc.Key IS NULL

);

commit tran

END

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

Also, you mentioned that this will be an 'ongoing process'. Yet your method does not deal with updates or deletes, is that OK?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Right joins give me a headache:

INSERT INTO tblPricingExc2 --table tblPricingExc2 has an Identity column as PK.

(Key, Flag, Id)

SELECT

tmpExc.Key ,

tmpExc.Flag ,

tmpExc.Id

FROM #tmpPrice tmpExc

LEFT JOIN tblPricingExc2 exc

ON tmpExc.Id = exc.Id

WHERE EXc.Id IS NULL;

You don't need the filter on exc.key - this could make a significant difference.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

thbaig Hall of Fame Points: 3003 More actions · Answer 6

Thanks Phil.

Begin and Commit tran worked. Does this avoid logging ?

I have insert and Update . No delete required.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 7

you can use SSIS for this, its created for the very purpose. as its MSSQL to MSSQL you can use the FASTLOAD option to load data.

thbaig Hall of Fame Points: 3003 More actions · Answer 8

Phil Parkin (12/23/2013)
You could also try forcing transaction commits in your WHILE loop:
WHILE @@rowcount > 0
BEGIN
begin tran
INSERT INTO tblPricingExc2 --table tblPricingExc2 has an Identity column as PK.
( Key ,
Flag ,
Id
)
SELECT --TOP (10000)
tmpExc.Key ,
tmpExc.Flag ,
tmpExc.Id
FROM tblPricingExc2 exc
RIGHT JOIN #tmpPrice tmpExc ON tmpExc.Id = exc.Id -- Used right join to fetch all results
--used following to insert only those records that don't exist in tblPricingExc2
WHERE ( EXc.Id IS NULL
AND exc.Key IS NULL
);
commit tran
END

its working great on empty target table. But once it has rows, it start taking time again

Phil Parkin SSC Guru Points: 246962 More actions · Answer 9

Please implement Chris' suggestion and see whether that helps.

I've just noticed that your WHILE loop does not appear to do anything, because the 'TOP' clause has been commented out.

My transaction suggestion was based on the WHILE loop being executed multiple times. I don't think it will help if there is only a single iteration.

And how are you planning on handling the updates?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin