Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: How to execute millions of insert statements quickly

    Hi,


    I have .sql files which contains millions(45 millions approx) of insert statements.
    which are currently taking so many hours to execute.

    Could anybody advice, how to insert execute these many insert statements quickly.


    Note:
    I can not make changes in the .sql files.


    Thanks in advance,
    Phanikanth

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    If it takes HOURS to simply insert 45 mio rows, there is something you are not telling us.

    How many columns in how many tables, and how many triggers and indexes ?
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    Thanks for quick reply.

    It is just one table. which has 11 columns, out of which primary key is defined on 6 columns.


    Regards,
    Phanikanth

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I can INSERT about 1 million rows per minute on my laptop.

    It is next to impossible to debug or tune code that can not be seen.
    You have done similar to below.

    my car won't go
    tell me how to make my car go.

    prior to INSERT do as below

    ALTER SESSION SET SQL_TRACE=TRUE;

    process resultant trace file with TKPROF & post the results along with EXPLAIN PLAN here.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I think he means that he has 45 million individual insert statements, not a statement inserting 45 million rows. With the parsing of each insert that will take a long time. One way to speed it up is to make sure that it is not echoed to the screen. run the insert and in the script set termout off and it should run much quicker.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2013
    Posts
    2
    It must be a very large .sql file to have 45 million rows in it?
    Why are they all in a .sql file like that?
    If they came from a CSV perhaps you could load them in with SQL loader faster?
    Is it a one off process or something happening regularly?

  7. #7
    Join Date
    Feb 2002
    Posts
    2
    I would drop the primary key index and run the file without it. Then re-build it - in parallel if you can.

    You don't say if there is any data in the table to start with. If there is none, it will be quicker, but if there is data in there, it could be slower depending how much data.

    hth

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Possible things you can do

    1) Make sure you run the insert sql on the database server to avoid network traffic
    2) Check for foreign keys on the table as they can slow down inserts
    3) Rather than have a file of insert statements can they generate a file of the values to insert in say csv format. Then use sqlldr or an external table to load it.
    4) Split the insert sql file into multiple files and see if you can do the loading in parallel, you will have to do some testing to find the optimum number of parallel processes.


    Alan

  9. #9
    Join Date
    Dec 2012
    Posts
    25
    As per Mr. Alan mentioned on step 4,Split the .sql file into several .sql files..it works...you can do this manually or you can use tools for doing this job....i used "gsplits" tool for this.....!!!!

    regards,

    --rush

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •