I have a mapping in Informatica Powercenter which combines data from two sources. One source has around 22 million rows of data while the other have >389 million rows of data. Will it be better performance-wise if I add Sorter transformation or is it better to add number of sorted ports in the Source Qualifier? Also, what factors that makes one way better than the other(in case of sorter transformation vs adding number of sorted ports in SQ)?
If both tables are from same DB, without a doubt - sort in the SQ using number of sorted ports.
Informatica sorter brings whole data into infa server and then sorts it. So, sorting 300M resultant data is going to take lot of time + resource.
Now, joining 389 M and 22M table in source and sort the result in source itself will take less time and resource. Informatica doesn't have to bring any data into its server.
Now, if they are from different data bases, then, sorting them in source qualifier will give perf boost while joining. You have to join them using joiner to get whole data set. And i think data order will be same if your sort key is same as join key and you do not have to sort again using sorter. Issue is joining both will take time but it will be comparatively faster than using infa sorter to sort.