Informatica: Which gives more performance when compare to fixed width and delimited file ? and why?

Question

I have simple informatica(9.1) mapping(one to one) which loads data from flat file to RDBMS

it take 5 mins to load to Oracle db and 20 mins to load same file in SQL Server 2008 R2.

Can there be any source/pointers for performance improvement

Answer 1

Fixed width,because there are no delimiters to check so the performance will increase.

It all depends on data and what you are ultimately doing with that data. Any consumer of that data will ultimately want to break down that data into fields. During the break down of those fields, you will need to check for a maximum field size as you read through the data stream.

So if you have an 80 character fixed width field, you will ultimately have 80 comparisons to see if you have reached the field max.

Delimited break down of data requires not only a check for the field max, but also a check for the end of field delimiter.

So if your delimited field is only 20 characters long, you will have only 40 comparisons made across those 20 characters you just processed.

So, the more padding you have in your fields, the less efficient fixed width becomes when processing.

An additional factor is actual bytes that are necessary to be transmitted. In almost all cases, delimited will require less character having to be transmitted.