Hi everyone,
I am trying to perform Merge Join using OLE DB source task in SSIS package. I am trying to process millions of records in the package. But I don’t know why it was taking so much time to do this simple task. Then I checked the log file and noted that it was taking so much time to sort the incoming data. But, I have already used “order by” clause in the SQL source statement for the sorting purpose. Can anyone tell how to improve the performance in this task and reduce the processing time?
Thanks!
Performance is getting reduced while processing merge join in SSIS
Hi,
In this case, you have to intimate to the task that the data has been sorted already. For this you have to right click on the OLE DB source task and select the "Advanced Editor" option. In the "Advanced Editor", there will be four tabs, Connection Managers, Component Properties, Column Mappings and Input Output Properties. Select the Input Output Properties tab. You can see two panes under this tab. Under the Common Properties pane, locate the “IsSorted” option which is listed under that. Set the “IsSorted” property as True.
Setting this property would intimate the task that the source data has already been sorted. Hence, it stops the sorting process initiated by this task. Now, only the query sorting will be made by the task.
Now that one process has been cut, automatically the performance will get increased and the processing time will decrease.
Possibly avoid the sorting process by the task, because it could initiate many threads within SSIS which may leads to poor performance. Always try to sort the incoming data by firing SQL query.
