Urgent - Lookup hampering the performance

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 2005 Forums

SSIS and Import/Export (2005)

Urgent - Lookup hampering the performance

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2011-07-25 : 07:55:56

Hi All,

I have 20 million records in a table. For the initial load i have successfully loaded from source to destination.

When i process for the second time immediately after the initial load it is getting stuck at the pre-execution part even though there is no data coming from source.

below is the method i used

Source
|
Lookup transfortmation - Full Cache mode
|
Conditional split-- To check whether the record is already exists
|
Insert/ Update

I can see after caching all the 20 million records its not moving.

I have tried to replace Lookup with Merge but i am not convinced with merge as Lookup is better than Merge as far as i know.

Can anyone suggest something which can help me.

Thanks

Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 11:19:12

if you're having proper indexes, left join using merge might perform much better compared to lookup.

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

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2011-07-25 : 23:59:15

visakh,

I have seven key columns to join. I have unique clustered index on the key columns. I am retrieving the data by sorting at SQL itself to avoid SORT transformations.Still it takes time to load the data.

Is there any other approach which will help me to load the data.

Thanks

Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 13:21:30

for subsequent loads what will be amount of data you typically deal in?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources