insert 17 million rows...slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

insert 17 million rows...slow

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-16 : 11:19:02

Greetings

trying to insert 17million rows into my staging database is a crawl. I have


PK           nonclustered
external_id  clustered
DF_created_by default constraint getdate()
DF_created_by default constraint (suser_sname())
DF_GUID       default constraint (newsequentialid())
I am doing 

insert into stgRelease
(
Field1,
Field2,
Field3,
Field4,
external_id
)
select Field1,
Field2,
Field3,
Field4,
external_id
From xTable
Where external_id not in (select external_id from stgRelease)

I tried to do it in batches in a WHILE block but still a crawl. Do I need to drop some indexes and constraints?

Any input would be much appreciated.

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2010-12-16 : 11:26:56

"not in (select external_id from stgRelease)"

If that is a big list it will be slow IME.


FROM xTable AS X
WHERE NOT EXISTS (SELECT * FROM stgRelease AS R WHERE R.external_id = X.external_id)

would be my preference, or possibly


From xTable AS X
   LEFT OUTER JOIN stgRelease AS R
        ON R.external_id = X.external_id
WHERE R.external_id IS NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-16 : 11:39:45

Do you need to load all the rows from the Source to your Destination? And are all the Source rows new? If so, you should SSIS; Drop your PK/Clustered index, load all your rows setting teh batch size on the OLEDB Destination, then recreate your PK/Clustered index.

As Kriten mentioned that IN clause is killing your performance (among other things). If you need to check the for the existance of a row first you should, use the Merge statement (or possibly a left outer join).

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-16 : 13:30:04

Yes I do need to load all the rows. I think I will go ahead and try both.

Thanks!!

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources