Hi,

Some questions :
- How many rows are treated by your job ?
- Do you launch routines in your tMap ?
- Your databases are local or on network ?

And an experience :
Before launching the job, check the "Statistics" box to see where the dataflow is slow

Before launching the job, check the "Statistics" box to see where the dataflow is slow
With this, you'll be able to see where things are slow (is it on oracle or access ??)

Once you have check with statistics, try to separate access input and output in two files

it is interesting, it shows that dataflow is slowing at the entry point : your oracle database on network
Could you show a screenshot of your tOracleInput component's configuration ?

Talend Open Studio generate Java or Perl code.
None of these language manage Access database natively.
Java Access DB Components communicate through an ODBC Bridge.
It will never be as fast as a native connexion or as fast as a real JDBC connexion.

Generally speaking, Input is not the problem. Writing is always longer...
That's why when you change tOracleInput, to ODBC it doesn't change the results.

You can try to tweak Avanced Settings / Autocommit value in your tAccessOutput : increase this value.

HTH,

I only said that reading is faster than writing.

About writing to file, on my laptop with a quite slow hard disk, I can easily go up to 75000 rows per second.
My Input file has 1 000 000 rows and 11 columns with different data types (Integer, String, and Date).
I write to a simple tFileOuptutDelimited without any CSV options...
My only "special" configuration is to temporary disable my Antivirus.

Can you give me more details about your own tests ?

You don't see tOracleInput because I use the component ODBC to connect to the data warehouse Oracle. The tInput component is DWH on my screen.

For Mhirt : You can load at 75000 rows/s on what type of database ? Oracle ? You display the statistics to see the performance or not ?

Thx

I'll checked again.
I was exagerating with 2000 rows/sec :s
It's 7000 rows/sec when reading from a delimited flat file with following specs :
- Number of rows : 7 000 000
- Number of columns : 12
- My job write to an excel file, If I write to a delemited file, the number of rows/sec is growing to 15000
- HDD speed : 7200 RPM
- I have an antivirus, but I cant disable it (SBS behind )

But nevermind, I dont have any problem with this

I have an other question according to my job. To improve performance, I need to modify the commit on tAccessOutput. However I don't know if it's better with a big commit (each 20000 rows for example) or a little commit (each 10 rows for example).
I use an computer with 1Go of ram memories and my process write 422188 rows in my database Access.

suzchr,

I have a question for you ! I see that your status is Talend Team. Do it significate that you work for Talend company ?

Yes I'm working for Talend ! :-)

However I don't know if it's better with a big commit (each 20000 rows for example) or a little commit (each 10 rows for example).

In general, it's better with a big "commit every" value, but it not as simple as that.
You may have better performance with a commit every of 40000 than with a comit every of 50000.
You have to make tests to find the better value.

Somebody know how the commit is done if I write 0 like value in commit every ?

With 0 or empty, there won't be any commit at all.

HTH,

So I am realising my benchmark and the result are not good...

In fact I realize two types of benchmark. The first is the job complete and the best time is get with a commit value on the tAccessOutput at 10. The best time is 22 minutes versus 9 minutes with my loader in Access.

Then, I create the same job without write on Access (I delete the tComponentOutput). The time is 4min 40 seconds. This is very good.

Then, I create a job where I just write on Access. I write 500 000 lines generated by the tRowGenerator. The best time is get by a commit value at 125 000. This best time is 5 minutes 39 secondes. This is also efficient.

All in all, I create two job one which extract only the data and finish by the tBufferOutput component and an other which get the data of the job and write on Access. But the performance are bad. After two hours I have just write 125 000 rows.


How can I do to improve my performance ? Someone has a good idea ?

Yes, it's done but the best time get is 22 minutes. I try to use 2 jobs to improve this time.
After my test I can say that when you write on Access, the best commit value is 125 000 rows, but if I write on Access in the same job that I read in my data warehouse, with a commit of 125 000 rows the time is 6h 40minutes...

Hi suzchr,

Try to test with only one tMap,it's accept many input and output ?
you may put a better Expression key to improve performence.

Regards