What's FASTEST way to IMPORT Massive amounts of TRANSACTIONAL DATA? (Non-SQL*Loader)
Date: Wed, 01 Dec 1999 01:26:53 GMT
Message-ID: <821tgu$kpe$1_at_ash.prod.itd.earthlink.net>
SQL*Loader is fastest for raw pumping of data straight to tables but we have to do queries as we import to see if an Entity is already in the database. If they are, then update their ENTITY and ENTITY_ATTRIBUTES information in the database. If not then insert into the below type of tables.
We have tables that store information like below...
ENTITY
ENTITY_ID ENTITY_INFO1 ENTITY_INFO2 ENTITY_INFO3
ENTITY_ATTRIBUTES
ENTITY_ID
ATTRIBUTE_ID
VALUE We have text input files that have column data that have information to be put into these 2 tables.
Currently we are using java/jdbc to read input files, parse columns, and do somewhat efficient JDBC calls using stored procedures to query and import the data into the tables. The code is already somewhat efficient, not doing much excess sql, batch commits, using prepared statements to reduce db reparsing. But it needs to be much faster. We are currently using java 1.1.6, Sun's jvm.
My question is this already highly efficient? Should we...
a - Use direct method sql*loader to load into a temp table then do queries and
insert/update into the 2 main tables? Would that save us much time since we
still have to do queries and insert after data pumped into temp table?
b - Use pro*c to read data and do some queries and logic and insert/update?
is it worth it? doesn't seem like it?
c - use pl/sql to read datafile, process, update/insert into tables?
I really don't know if these alternatives will improve performance as a whole that much.
Or should we instead look at it from a systems viewpoint, changing to IBM instead of java JVM (heard its faster), or changing unix servers, or speeding up network connection between machine code and data lies and the database machine, or something else. Even if no one has a solution, at least if you can say that the way we are currently doing it is pretty darn efficient, I could focus on solely a systems approach feeling that I've done my due diligence. Help! Thanks! Received on Wed Dec 01 1999 - 02:26:53 CET