2

I use OLEDB to insert data to a DB4 .dbf file. Inserting 5,500 rows takes approximately 2.5 min. So I need a faster way to implement it as my data will have upto 80,000 rows and time taken will be too painful. Is there any alternative faster ways to do this? According to your view which is the best way?

PS: Please mention the aprox. time taken for your method if possible

Vicky
  • 1,657
  • 6
  • 23
  • 33

1 Answers1

1

Inserting multiple rows within a single query could speed up a lot your work.
For example: I've just tried inserting 100,000 rows using 100,000 INSERT and using 1,000 INSERT with 100 rows each one: I have a speed-up about 100!!
Difference is using

INSERT INTO table (....) VALUES (...)

and

INSERT INTO table (....) VALUES (...),(...),(...),(...),(...),...

So edit your inserting function to use 100 (for example) records at a time.

Marco
  • 56,740
  • 14
  • 129
  • 152
  • @Vicky: did you tried this solution? Did you find some improvement or not? – Marco Apr 13 '11 at 10:41
  • @Marco: I am not able to insert it as a single query as the column names to be inserted differs for each row :( – Vicky Apr 13 '11 at 10:49
  • @Vicky: how it is possible in a database to save rows with different column names? I didn't know it was possible. Or maybe I'm misunderstanding what you mean :) – Marco Apr 13 '11 at 10:53
  • My mistake. I was inserting each row by removing the cell (column) with empty value. So the query was diff each time. I am trying to modify it as you said. will try :D – Vicky Apr 13 '11 at 11:00
  • I am not able to do it. when I use as u told it is giving a "Missing semicolon(;) at the end" error :( – Vicky Apr 13 '11 at 15:45
  • @Vicky: oh gosh, you have to close your query with a semicolon, always!! Anyway, post an example and I'll correct that – Marco Apr 13 '11 at 17:26
  • ofcourse I did it. Still it gives the error :( Eg: INSERT INTO table (index, name) values (110, 'Vicky'), (111, 'Marco'); Are you sure you can insert multiples rows in this format in DB4 .dbf file?? – Vicky Apr 14 '11 at 10:43
  • @Vicky: edit your post inserting database schema and I'll try to create same db and insert... If you can post event only the rows you establish connection and insert records... – Marco Apr 14 '11 at 10:49
  • @Marco, I'm almost positive that OleDB does not support multi-line inserts in a single SQL query... Each has to be submitted separately. – DRapp Apr 14 '11 at 16:14
  • @Vicky: gotta try that and another way I've in my mind. Tomorrow (here is evening) I give you an answer. – Marco Apr 14 '11 at 17:36
  • @Marco: Wt about the answer u told about :( – Vicky Apr 18 '11 at 06:18
  • @Vicky: you're right, but I had some trouble with my children... I'll do that as promised... – Marco Apr 18 '11 at 06:55
  • @Vicky: using OleDB is not possible to use syntax I gave you, sorry. You can find a useful workaround [here](http://stackoverflow.com/questions/62504/is-there-any-way-to-create-multiple-insert-statements-in-a-ms-access-query) – Marco Apr 18 '11 at 07:47
  • @Marco or Vicky did you ever get this to work with the workaround you posted in the link above? – reggaeguitar Oct 29 '14 at 23:11