Hi!

Today we had a hot discussion about the performance of LOAD vs. IMPORT.
I'd like to hear what you have to say:
- Should LOAD be faster that IMPORT in every circumstance?
- Is IMPORT more suited for small files and LOAD for large files?
- Is LOAD more suited for table that have many indexes and IMPORT for tables
that have few or no indexes?

Best regards,
Kovi

On May 20, 6:31Â*am, Gregor KovaÄ� <gregor.ko... (AT) mikropis (DOT) si> wrote:
Hello Kovi,

Interesting questions, although I'm not sure they are always the rigth
ones to ask. There are factors that can influence your choice for
IMPORT or LOAD that might be more important:
- recoverability of the operation
- for moving data between databases, load from cursor has a lot of
advantages
- availability of data during/after insert
- presence of generated columns
- compression on tables
- ...

I'll still try to give a shot at your questions, though:
consistently 4 times longer than the corresponding insert. Another
thing to consider for no-so large tables: how fast can you type 'SET
INTEGRITY...'? :-)

into account the size of the file.

on a 3-column table), it's exactly the inverse. I didn't do the test
for large tables, there it might also depend on the indexing mode you
choose (incremental or rebuild). I suggest you just give it a try
yourself.

--
Frederik Engelen
Realdolmen

Kovi,

Feedback from backstage:

Load is faster than import in most of cases. Better to describe cases
where import can be faster:

1) Correct about small files. Load has a startup and shutdown cost.
2) Parallel import can catch up and beat load (ie, running lots of
parallel import command with the data split already) specifically when
XML is in the table.

More indexes will likely hurt import more than load, because the data
isn't sorted, and the index inserts are a single row at a time. Load
will be faster with larger files regardless of the number of indexes

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

On May 20, 8:29*am, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:

I've found that indexes are my enemy with the load utility - even
though I can load the data in parallel and only do incremental index
builds, the index build phase isn't parallel. Rebuilding indexes
isn't a great strategy for real-time data warehousing, and I don't yet
have enough funding to go to DPF. My current server has a decent
number of rather slow CPUs. So, in this scenario I can split a file
into 8 individual pieces then import each simultaneously and get
better overall performance. Unfortunately. Think I might be missing
anything?


Thanks,

Ken

On 5/21/2010 9:09 AM, kenfar wrote:
multi-threaded engine?

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Hi!

Thanks for the feedback.
I am/was under the impression that IMPORT does a exclusive table lock. How
can I do parallel import then?

Best regards,
Kovi

Serge Rielau wrote:

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

On Jun 1, 2:48Â*pm, Gregor KovaÄ� <gregor.ko... (AT) mikropis (DOT) si> wrote:
From the 'Data Movement Utilities Guide and Reference' e-book:

"Import acquires either an exclusive (X) lock or a nonexclusive (IX)
lock on existing target tables, depending on whether you allow
concurrent access to the table."

Did you already download this book? As you seem interested in the
matter, I really think you should take a look at it.

--
Frederik Engelen
RealDolmen