incremetal loading 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

SSIS and Import/Export (2008)

incremetal loading slow

Author

Topic

vipinjha
Starting Member

21 Posts

Posted - 2012-02-06 : 04:18:26

Dear All,
I have HTD table in Oracle , daily 500000(5lac) record inserting in HTD table ,
i want to take only incremental data in my staging database
so every day only 5lac data shoulkd be insrted in my staging database.
currently my process taking much time
please le me know some best idea through which i can load data fats,

regards,
Vipin jha

Vipin jha

Kristen
Test

22859 Posts

Posted - 2012-02-06 : 08:30:56

[code]
INSERT INTO STAGING_DATABASE.dbo.StagingTable(Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM MAIN_DATABASE.dbo.MainTable AS SRC
LEFT OUTER JOIN STAGING_DATABASE.dbo.StagingTable AS DST
ON DST.MyPK = SRC.MyPK
WHERE DST.MyPK IS NULL
[/code]
If you have a Create Date column, and it is indexed, then this would probably be faster
[code]
DECLARE @PreviousCreateDate DATETIME
SELECT @PreviousCreateDate = MAX(CreateDate)
FROM STAGING_DATABASE.dbo.StagingTable

INSERT INTO STAGING_DATABASE.dbo.StagingTable(Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM MAIN_DATABASE.dbo.MainTable AS SRC
LEFT OUTER JOIN STAGING_DATABASE.dbo.StagingTable AS DST
ON DST.MyPK = SRC.MyPK
WHERE DST.MyPK IS NULL
AND SRC.CreateDate >= @PreviousCreateDate
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 10:02:09

do you've lot of indexes in your destination table? if yes, that can be one of reasons for slow insertion. it might be worth dropping them before insert process and creating them after.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-06 : 10:14:05

Could export with BCP using NATIVE format, and pre-sorted into CLUSTERED INDEX order of the target table, and then use BCP to re-import on the target machine (providing the HINT that the file is pre-sorted.

vipinjha
Starting Member

21 Posts

Posted - 2012-02-07 : 00:02:45

Dear All ,
But i want to use ssis (data flow task)
my source is oracle and staging in sql server 2008

i want to load increment data through SSIS DATA FLOW TASK

REGARDS,
vIPIN JHA

Vipin jha

Kristen
Test

22859 Posts

Posted - 2012-02-07 : 03:46:02

Well then you are stuck with what SSIS can do!

Hopefully an SSIS Guru will be along shortly ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources