0011202: Write Data into MsAccess-File is very slow

(0036312)
mhirt (administrator)
2010-01-21 01:05

Well, Access has never been very fast for high volume of data...

jliu, can you verify if something specific can be done ?

(0037119)
emaxt (reporter)
2010-01-30 18:00

Given that Access is not performance oriented for big volumes and high loads (consider sql server in that case if you want to stay with MS) and that Talend uses ODBC bridge to write to access,
I get anyway a consistent 600 rows/s for 300000, 30 field records writing from talend on a 5 years old machine.

Check you setup and you sources...
bye

(0037267)
PaulMandalka (reporter)
2010-02-02 13:09
edited on: 2010-02-02 13:09

Ok, after some furture tests with 3.2.3 it seems, that writing string-fields is very fast - I get a constant speed of 2000 rows /s.
But if I only add one Double field (in MsAccess-Table) the performance starts at a high value (2000 rows/s) and it's getting worser and worser (after 50.000 Rows it's only 300rows/s - and getting worser) - With 5 Double Fields and 2 Date fields I get a rate of only 138row/s avg speed (with 50.000 rows)

Adding more double and date-fields seems to make it even worser.

(0037771)
jliu (developer)
2010-02-09 10:52

FYI: It's a simple Access table without indices (writing the data (250000 rows) to a CSV-file and read-in the csv-file from within Access into the database is very fast (~30s)).------>use CSV file to insert table is fast.
i suggest flow step:
1.that will data writing to cvs file.
2.execute sql "INSERT INTO bug11202 SELECT * FROM [Text;Database=D:\\testTemp\\date\\Access;HDR=NO].[out.csv]" insert data to table.

i add my test job bug11202_tAccessOutput.zip.

(0037777)
PaulMandalka (reporter)
2010-02-09 11:31

Ok, that's a nice workaround to speedup the import process, thanks.

Any Ideas why the "normal" solution is so slow?

(0038355)
jliu (developer)
2010-02-24 08:32
edited on: 2010-02-24 08:33

hi,
the "normal" solutions just row by row insert table,this is inefficient when insert bulk data.

any questions feedback me.
thx.

(0038370)
wyang (developer)
2010-02-24 10:49

It is OK.

(0038405)
alevy (reporter)
2010-02-25 01:26

It would be nice if this workaround approach were packaged up into a new "tAccessBulkExec" component, with the equivalent functionality as the tMSSqlBulkExec component, such as the "Action on table" options.

Issue History
Date Modified	Username	Field	Change
2010-01-19 15:56	PaulMandalka	New Issue
2010-01-19 15:56	PaulMandalka	Assigned To	=> mhirt
2010-01-19 15:56	PaulMandalka	File Added: tMsAccess_Performance.png
2010-01-21 01:05	mhirt	Note Added: 0036312
2010-01-21 01:05	mhirt	Assigned To	mhirt => jliu
2010-01-21 01:05	mhirt	Status	new => feedback from Talend
2010-01-30 18:00	emaxt	Note Added: 0037119
2010-02-02 13:09	PaulMandalka	Note Added: 0037267
2010-02-02 13:09	PaulMandalka	Note Edited: 0037267
2010-02-09 10:52	jliu	Note Added: 0037771
2010-02-09 10:52	jliu	File Added: bug11202_tAccessOutput.zip
2010-02-09 10:53	jliu	Status	feedback from Talend => feedback from reporter
2010-02-09 11:31	PaulMandalka	Note Added: 0037777
2010-02-10 02:54	jliu	Status	feedback from reporter => feedback from Talend
2010-02-24 08:32	jliu	Note Added: 0038355
2010-02-24 08:33	jliu	Note Edited: 0038355
2010-02-24 08:35	jliu	Issue Monitored: jliu
2010-02-24 10:15	jliu	Assigned To	jliu => wyang
2010-02-24 10:15	jliu	Status	feedback from Talend => toVerify
2010-02-24 10:49	wyang	Status	toVerify => assigned
2010-02-24 10:49	wyang	Note Added: 0038370
2010-02-24 10:49	wyang	Assigned To	wyang => mhirt
2010-02-24 10:49	wyang	Status	assigned => toVerify
2010-02-25 01:26	alevy	Note Added: 0038405
2010-03-31 01:19	mhirt	Status	toVerify => closed
2010-03-31 01:19	mhirt	Resolution	open => no change required
2010-03-31 01:22	mhirt	Relationship added	related to 0012418

Notes
(0036312) mhirt (administrator) 2010-01-21 01:05	Well, Access has never been very fast for high volume of data... jliu, can you verify if something specific can be done ?

(0037119) emaxt (reporter) 2010-01-30 18:00	Given that Access is not performance oriented for big volumes and high loads (consider sql server in that case if you want to stay with MS) and that Talend uses ODBC bridge to write to access, I get anyway a consistent 600 rows/s for 300000, 30 field records writing from talend on a 5 years old machine. Check you setup and you sources... bye

(0037267) PaulMandalka (reporter) 2010-02-02 13:09 edited on: 2010-02-02 13:09	Ok, after some furture tests with 3.2.3 it seems, that writing string-fields is very fast - I get a constant speed of 2000 rows /s. But if I only add one Double field (in MsAccess-Table) the performance starts at a high value (2000 rows/s) and it's getting worser and worser (after 50.000 Rows it's only 300rows/s - and getting worser) - With 5 Double Fields and 2 Date fields I get a rate of only 138row/s avg speed (with 50.000 rows) Adding more double and date-fields seems to make it even worser.

(0037771) jliu (developer) 2010-02-09 10:52	FYI: It's a simple Access table without indices (writing the data (250000 rows) to a CSV-file and read-in the csv-file from within Access into the database is very fast (~30s)).------>use CSV file to insert table is fast. i suggest flow step: 1.that will data writing to cvs file. 2.execute sql "INSERT INTO bug11202 SELECT * FROM [Text;Database=D:\\testTemp\\date\\Access;HDR=NO].[out.csv]" insert data to table. i add my test job bug11202_tAccessOutput.zip.

(0037777) PaulMandalka (reporter) 2010-02-09 11:31	Ok, that's a nice workaround to speedup the import process, thanks. Any Ideas why the "normal" solution is so slow?

(0038355) jliu (developer) 2010-02-24 08:32 edited on: 2010-02-24 08:33	hi, the "normal" solutions just row by row insert table,this is inefficient when insert bulk data. any questions feedback me. thx.

(0038370) wyang (developer) 2010-02-24 10:49	It is OK.

(0038405) alevy (reporter) 2010-02-25 01:26	It would be nice if this workaround approach were packaged up into a new "tAccessBulkExec" component, with the equivalent functionality as the tMSSqlBulkExec component, such as the "Action on table" options.