Job slow in reading sequential file

sjordery · Post by **sjordery** » Mon Dec 06, 2010 3:24 am

Hi All,

I have a job which reads data from a sequential file and loads to sql server data base.
The job used to run faster with < 1lakh records.

After the the source file size has increased ( around 3+ lakhs of records)the job is running very slow.
When I checked director for this job I learned that it is the source sequential file which is running for more than 1 hour to read the records.

Could any body please suggest what should I do to make the sequential file run faster.
Should I check the "read from multiple nodes" option in sequential file?

The job is running in 2 nodes.

Thanks in Advance,
Sjordery.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 06, 2010 3:38 am

The first thing you need to do is to be sure you've named the correct culprit. Create a job that consists only of a Sequential File stage and a Copy stage, and measure your read speed with that.

You could experiment with multiple readers per node in this job too.

I strongly suspect that the problem is not in the Sequential File stage, and that earlier you were populating an empty, or nearly empty, table, which tends to be faster than a larger table. If you're doing Upserts, then we can extend the discussion more than somewhat.

sjordery · Post by **sjordery** » Mon Dec 06, 2010 3:47 am

Thanks Ray.
Actually the job has a copy stage in between source sequential file stage and target ODBC stage.
When I check the director it shows :

Sequential_File_0,0: Progress: 10 percent.
Sequential_File_0,0: Progress: 20 percent.

.......
.......

Sequential_File_0,0: Progress: 100 percent

To complete 100 percent it takes more than 1 hour while reading a source file with 3+ lakhs of records.
And in target ODBC stage Write Method= Write and Write Mode=Append is specified.

Ok I will try with "read from multiple node" option and reply to the post.

Thanks,
Sjordery.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 06, 2010 5:38 am

Get rid of the database stage to do your test. You will be amazed at what you see.

chulett · Post by **chulett** » Mon Dec 06, 2010 8:31 am

Exactly... I wager 400 Quatloos that it is your inserts that are the 'slow' part of this equation. You can't drive any faster than the guy in front of you.