CODEWITHSUNDEEP
informix
cppcoder
cppcoder

Reputation: 23115

External tables in informix

I wanted to load a huge file into a table in the informix DB and it was taking forever. Previously, I was using this command:

LOAD FROM <file> delimiter "|" INSERT INTO <tablename>

After some research, I read the concept of external table in IBM site.

What is the difference between simple LOAD command and using EXTERNAL tables to load data into DB?

Is there any performance difference between the two?

Upvotes: 2

Views: 868

Answers (2)

Jack Parker
Jack Parker

Reputation: 21

+1 for Jonathans answer.

It should be noted that a load using either LOAD or INSERT from an external table will be logged and if the amount of data to load is large, may fill the transaction logs and cause a long transaction with a lengthy rollback. Look into modifying the table type of the table to be loaded to RAW (this involves dropping indexes as well - which is a good thing) before performing your load. e.g.:

drop index foo_idx1;
Alter table foo type (RAW);
insert into foo select * from ext_foo;
Alter table foo type (STANDARD); -- you will probably need to take a backup at this point.
create index foo_idx1 on foo(foo_col);

Upvotes: 0

Fernando Nunes
Fernando Nunes

Reputation: 381

The difference is huge, as well as the performance. The LOAD goes through the normal SQL layer and could be compared to the simple INSERT (with a few optimizations). It's also a client side "work" that has to communicate and exchange messages with the server.

The external tables are much different. Depending on the mode you use (express is the fastest), the process creates the data pages and bypasses the SQL layer completely. Naturally there are limitations, but for bulk loads it can make a huge difference. Also, with external tables, it's the engine itself that makes the load. With the previous "fast" method (a tool called High Performance Loader — HPL) there was a client side process, but the overall concept was similar.

Upvotes: 4

Related Questions