Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    delhi india
    Posts
    2

    Unanswered: convert txt file to database

    I am using a txt file as base to fill tables. each line of text file is a record. Tables are updated after analysing the record like cities of particular state in different table and fields are updated as per value like online value if online then enter true in field else false.

    i am using vc++ program to update database. everything is working file but the problem is the text file is in GBs and it took around 27-28 hours to convert one GB text file into database.

    I have used MS-SQL server import export utility but there i could not parse the values to be get inserted in respected tables/fields. Is there any there technique using which i could convert fast.

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    did you consider sqlldr it can be customized in some way and might be working even faster than many many single inserts

    see the link for 8.1.7 sqlldr
    http://download-west.oracle.com/docs.../ch04.htm#1044

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    A few minor points which might help if sqlldr isnt an option

    1) if your loading program can run on the database server itself this will help a lot as often network latencies can cause appreciable slow down. Obviously you can only do this if you server can run the VC++ prog but if not then ensure the PC you run the loading app on is as close to the database server as possible so things like firewalls arent in the way.

    2) Inserts are slowed down by indexes. So for tables which are only inserted into (not updated) then drop the indexes while loading and then rebuild the indexes afterwards. Obviously assuming users arent accessing the data during loading.

    3) Move some of the more time consuming logic (analyse your code to identify bottlenecks) to the database. So maybe load the raw data into a staging table and then have a stored proc move and transform the data afterwards. Maybe declare staging tables as global temporary tables as they generate less redo.

    4) If you are using 9i then consider multi-table inserts and MERGE as reducing the number of statements executed can help a lot aswell.

    5) If the bottleneck is the not the database then consider running parallel loading jobs which load different parts of the data simultaneously.

    6) I havent coded in VC++ in a long time but you may be able to do batch inserts/updates as you can in PLSQL or java. This can make a big difference.

    Alan

Posting Permissions

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