INSERT without logging
Hi,
How do i do to accelerate INSERT statment?
Is there any possibility to specify SQL to do it without logging?
My soft do multiple insert and at the end th log is very huge and it takes quite a long time to execute.
Thanks
How do i do to accelerate INSERT statment?
Is there any possibility to specify SQL to do it without logging?
My soft do multiple insert and at the end th log is very huge and it takes quite a long time to execute.
Thanks
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another option is to remove your Indexes and do your insert operation .. This will fasten the insertion, but when you recreate the index, it will take more time.
ASKER
>>does the soft insert 1 line at a time?
no many Insert statment are needed to rebuild the DB (that's the meaning of this soft).
So many lines are inserted in different tables.
no many Insert statment are needed to rebuild the DB (that's the meaning of this soft).
So many lines are inserted in different tables.
what I meant is, is your application doing like this:
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
etc
if yes, change to this (execute 1 big SQL with all the inserts, 1 insert per table doing as many selects union'd as you need rows to be added:
connection.execute "INSERT INTO table1 ... SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ...
INSERT INTO table2 ... SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ... "
that will work MUCH MUCH MUCH faster
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table1 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
connection.execute "INSERT INTO table2 ... VALUES( ... ) "
etc
if yes, change to this (execute 1 big SQL with all the inserts, 1 insert per table doing as many selects union'd as you need rows to be added:
connection.execute "INSERT INTO table1 ... SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ...
INSERT INTO table2 ... SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ...
UNION ALL SELECT ... "
that will work MUCH MUCH MUCH faster
ASKER
Sorry I was not clear
the insert statment are from other tables
so there's :
INSERT INTO myTab (...,....) SELECT ...,... FROM ...
INSERT INTO myTab (...,....) SELECT ...,... FROM ...
INSERT INTO myTab (...,....) SELECT ...,... FROM ...
...
the insert statment are from other tables
so there's :
INSERT INTO myTab (...,....) SELECT ...,... FROM ...
INSERT INTO myTab (...,....) SELECT ...,... FROM ...
INSERT INTO myTab (...,....) SELECT ...,... FROM ...
...
but do you insert 1 data row at once or all rows at once?
ASKER
all
are the SELECT statements that you use for the insert running fast if you run them apart.
ASKER
It goes faster bu take also a while. I just make a test with one (don't know if it's similar for all)
But I have around 5 minutes for the INSERT and 2 minutes for the SELECT
But I have around 5 minutes for the INSERT and 2 minutes for the SELECT
ASKER
on anoher I have the same time for both
anyway thanks for your help
anyway thanks for your help
>and 2 minutes for the SELECT
how many rows in the table, and how many rows returned?
how many rows in the table, and how many rows returned?
ASKER
How many rows in the input table you mean? 6.000.000
And for example a statment who takes 20 seconds add 905554 rows
And for example a statment who takes 20 seconds add 905554 rows
> Is there any possibility to specify SQL to do it without logging?
No, only bulk insert operations are minimally logged