Data transfer for large number of Rows poor performance of package

Question

Post reply

Data transfer for large number of Rows poor performance of package

Megha P

SSCommitted

Points: 1976
More actions
April 11, 2014 at 5:38 am

#287529

Hello,
Below is my query which i am implementing in SSIS.
My data transfer is from different server.
SELECTF.FromEmailID,
A.Email_To,
A.Subject,
A.Body,
A.Created_Date,
E.EmailSentTypeID,
C.CandidateID,
A.Req_ID,
A.UserID
FROM dbo.[Trn_SendMail_Candidate] A
LEFT JOIN FromEmailMaster F ON A.Email_To = F.Email
LEFT JOIN EmailSentTypeMaster E ON A.Mail_Sent_Type = E.EmailSentTypeText
LEFT JOIN CandidateMaster C ON C.Email = A.Email_To
Below are the number of rows in table
Trn_SendMail_Candidate -- 12,89,875 Rows source table on different server
FromEmailMaster -- 7 Rows table is on destination server
CandidateMaster --565760 Rows table is on destination server
EmailSentTypeMaster --6 Rows table is on destination server
my package execution continues to execute for half an hour processed only around 2,00,000.(still remains 10,00,000 rows)
i have attached pkg screenshot .
Please suggest anything i can change for faster execution

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLCJ SSCommitted Points: 1781 More actions · Answer 1

I would have started with an effort to eliminate sort transforms here.. there are too many of them

SQLCJ SSCommitted Points: 1781 More actions · Answer 2

Additionally, would try to eliminate the unnecessary columns ... then would try if we can somehow convert Merge Join into the SQL joins ... I'm hopping here that @ destination its a data dump using fast load option .. In short would re-design the package ... we can also test performance by adjusting buffer size to accommodate maximum number of rows into the buffer...

Megha P SSCommitted Points: 1976 More actions · Answer 3

I have removed one sort by adding it in source it self..

but other sort controls are needed as i am joining main table on different fields

still i am not getting any performance gain.

Also in look up i have set option of No cache

SQLCJ SSCommitted Points: 1781 More actions · Answer 4

In lookup to get the maximum performance benefit we use Full cache or partial cache depending on the the table size... if table size is huge then partial cache would do.. but if it is small then why not use full cache mode....

It might or might not help but check if you can have intermediate table and apply join using that in SQL script.. later you may drop it ...