[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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
0
meciab
Asked:
meciab
  • 6
  • 5
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, you cannot insert without logging (except bulk insert, from file)

>My soft do multiple insert and at the end th log is very huge and it takes quite a long time to execute.
does the soft insert 1 line at a time?
 ==> the solution is to concatenate the insert statements into a batch and run it all at once.

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
meciab,
> Is there any possibility to specify SQL to do it without logging?

No, only bulk insert operations are minimally logged

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
meciabAuthor Commented:
>>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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
meciabAuthor Commented:
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 ...
...

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
but do you insert 1 data row at once or all rows at once?
0
 
meciabAuthor Commented:
all
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
are the SELECT statements that you use for the insert running fast if you run them apart.
0
 
meciabAuthor Commented:
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
0
 
meciabAuthor Commented:
on anoher I have the same time for both
anyway thanks for your help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>and 2 minutes for the SELECT
how many rows in the table, and how many rows returned?
0
 
meciabAuthor Commented:
How many rows in the input table you mean? 6.000.000
And for example a statment who takes 20 seconds add 905554 rows
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now