WHich one will give better performance...flat file or DB

dsedi · Post by **dsedi** » Tue Aug 28, 2007 6:52 am

Hi

We have a requirement in which our source system type is being finalized.
Is it better to use flat file as source and retrieve data from there or from oracle directly.

Oracle is on a remote server.

Which one to go for and why

Thanks

srimitta · Post by **srimitta** » Tue Aug 28, 2007 7:09 am

1). If your data source is from any relational DB then source data from DB.

2). If you are expecting data from other than relatioanl DB (eg:UNIX box, Mainframes) then allow that data files to land on to your DS server's staging area.

Don't try to dump your Oracle data into your DS server's staging area for the sake of performance.

dsedi · Post by **dsedi** » Tue Aug 28, 2007 7:45 am

Connecting to the Remote oracle server wont be a overhead. If I use a flat file, it wont require connection the way Oracle server would need.

In our case we have Oracle installed on different server. Still, will it be better to use Oracle in place of flat file.

dsedi · Post by **dsedi** » Tue Aug 28, 2007 8:46 am

I am trying to test this using some samle load.
I am using an ODBC stage to pull out 100000 records from a table. I am then populating these records to a Sequential file. In the next job I am using this sequential file as source to populate a dataset.

This job hardly takes any time.

While, when I use the Oracle ODBC as the source and try to populate the dataset, it takes fair amount of time. I have tried changing the array sizes and still sequential file as source is performing better.

I am doing the right kind of tests

srimitta · Post by **srimitta** » Tue Aug 28, 2007 3:14 pm

Now you have two DS jobs one for creating data file from Oracle DB and second one to read data file (which is created by first job) to create dataset.

Instead you can design one job to data from Oracle to create dataset.

now it's upto you to decide which option is best for you