Performance issue when import data from a text file to oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Performance issue when import data from a text file to oracle

  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Performance issue when import data from a text file to oracle

    Dear all,

    I encountered the following problem when I try to import data from text file to oracle in web interface.

    -- I use java program to read the text file and dynamically generate the "insert" statement line by line to import the data to an oracle table. There are about 12,000 records in the text file, and it will take about 1 hour to import all the records.

    May I know is the performance issue because of "dynamically generate the statement" or "the configuration of my oracle database"? And how can I resolve it.

    Any idea? Thanks very much!

  2. #2
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    The best solution is to use the SQL Loader utility with in Direct mode. This mode bypasses most of the Oracle database processing. It reads the datafile, converts the data into datablocks and writes them to disk. It saves lot of time and good for bulk data loading.
    Agasimani
    OCP(10g/9i/8i/8)

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    It depend on what your Java does with your text file.
    You can find out time taken for inserting operation and time taken for creating SQL statement. When you run Java there is default autocommit after each statement. And you read line by line your text file, so most probably problem is in I/O operations.
    I made SQL package which generate 'insert' statement for entire schema, the size of schema is around 600Mb, but it runs already for 3-4 weeks, weak place in my case is I/O, but I am not in a hurry.
    Best wishes!
    Dmitri

  4. #4
    Join Date
    Nov 2002
    Posts
    170
    If you are in 9i then you can use external table. It's very fast(in secs) but you cannot create index and write to this table. If you want to write to this table then you can create table with select from the external table. If it's a continuous process then I think external table will work out best.

  5. #5
    Join Date
    Sep 2002
    Posts
    30
    agasimani: Yes, agree with you. I think SQL Loader should be the most efficient way for loading the data. However I need to manipulate the records when importing, like add some more columns for each record. Can SQL Loader do this? Thanks!

    kgb: You are really patient! There is a artical http://home.clara.net/dwotton/dba/java_insert.htm said turn off the auto-commit will improve the speed 10 times. But it seems it didn't work well to me. Hope it helps you. Thanks!

    dbasupuser: Thanks! I will explore it. But can you tell some more about how to use the external table? Thanks!

    Any more advice??? And how can I check if my oracle server is efficient or not? What are the important parameter for it? Thanks very much!

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ly
    agasimani: Yes, agree with you. I think SQL Loader should be the most efficient way for loading the data. However I need to manipulate the records when importing, like add some more columns for each record. Can SQL Loader do this? Thanks!

    Any more advice??? And how can I check if my oracle server is efficient or not? What are the important parameter for it? Thanks very much!
    Use SQLLDR Utility & load data in Some TEMP Table.

    Use ur logic implemented in java & write PL/SQL to manipulate data & put it into ur working permanent table.

    It wud require 5 Mins(approx) to load data of 12000 records from Text file & Pl/SQL wud at the most take 10 mins to implement wat so ever ur logic to manipulate data....
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You are using bind variables in the insert statement, aren't you?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2002
    Posts
    30
    Originally posted by slimdave
    You are using bind variables in the insert statement, aren't you?
    I use prepared statment and the insert statement I used is "insert into ... values (v1, v2, v3..)". And v1, v2, v3 ... is variables which receive the data read from the text file. Is this what your "bind variables" mean? Thanks!

  9. #9
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122

    External Tables

    Oracle allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. By providing with metadata describing an external table, Oracle is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.

    You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.

    The DBMS_STATS package can be used for gathering statistics for external tables. The ANALYZE statement is not supported for gathering statistics for external tables.

    You create external tables using the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement. You are not in fact creating a table; that is, an external table does not have any extents associated with it. Rather, you are creating metadata in the data dictionary that enables you to access external data.

    The following example creates an external table, then uploads the data to a database table.

    EXAMPLE: Creating an External Table and Loading Data
    The file empxt1.dat contains the following sample data:

    360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
    361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
    362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
    363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda


    The file empxt2.dat contains the following sample data:

    401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
    402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
    403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
    404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard


    The following SQL statements create an external table in the hr schema named admin_ext_employees and load its data into the hr.employees table.

    CONNECT / AS SYSDBA;
    -- Set up directories and grant access to hr
    CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS '/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/data';
    CREATE OR REPLACE DIRECTORY admin_log_dir
    AS '/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/log';
    CREATE OR REPLACE DIRECTORY admin_bad_dir
    AS '/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/bad';
    GRANT READ ON DIRECTORY admin_dat_dir TO hr;
    GRANT WRITE ON DIRECTORY admin_log_dir TO hr;
    GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
    -- hr connects
    CONNECT hr/hr
    -- create the external table
    CREATE TABLE admin_ext_employees
    (employee_id NUMBER(4),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    job_id VARCHAR2(10),
    manager_id NUMBER(4),
    hire_date DATE,
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    department_id NUMBER(4),
    email VARCHAR2(25)
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY admin_dat_dir
    ACCESS PARAMETERS
    (
    records delimited by newline
    badfile admin_bad_dir:'empxt%a_%p.bad'
    logfile admin_log_dir:'empxt%a_%p.log'
    fields terminated by ','
    missing field values are null
    ( employee_id, first_name, last_name, job_id, manager_id,
    hire_date char date_format date mask "dd-mon-yyyy",
    salary, commission_pct, department_id, email
    )
    )
    LOCATION ('empxt1.dat', 'empxt2.dat')
    )
    PARALLEL
    REJECT LIMIT UNLIMITED;
    -- enable parallel for loading (good if lots of data to load)
    ALTER SESSION ENABLE PARALLEL DML;
    -- load the data in hr employees table
    INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
    hire_date, salary, commission_pct, department_id, email)
    SELECT * FROM admin_ext_employees;


    Hope it helps u
    NK
    ====================================================
    Stand up for your principles even if you stand alone!
    ====================================================

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    For bind variables and Java, see Tom Kyte's advice
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width