How best to move large data sets between tables?

[parkjammer]

Questions

• (1) Have you had to move large data sets using XBASIC?
• (2) What was your preferred method and why?

Details about my challenge
My mobile app talks to back-end databases and will occasionally need
to move large data sets of 50,000 to 3,000,000 records between tables.

Normally this would be done by enterprise-level integration tools like
Informatica by my app expects to perform some of these actions in
special circumstances when a formal integration tool is not present.


Existing simple but inefficient method
I can do this using a simple but inefficient method where the data is stored locally in a file or table.
It is slow but bypasses in-memory objects (like XML document objects) and allows handling of any
size of query result as long as there is sufficient hard drive space. This also has the advantage
of being able to track the movement of data from point A to point B by using the webserver as
an intermediary hub (even though this is rarely necessary).

1. Open connection from SOURCE TABLE
2. Fetch a few rows at a time
3. Write data to a TEMPORARY FILE (e.g., XML)
4. ---
5. Open connection to DESTINATION TABLE
6. Read data from TEMPORARY FILE
7. Store a few rows at a time to the destination table
8. ---
9. Successful data transfer!

What I can't do

• Use in-memory variables (e.g. XML, STRING, BLOB)... because there will always be memory limitations

What else may be possible?

• Using local ".dbf" files... although they have a 2GB size limitation. Granted, I may use this method in the short term.
• Hold two connections open and pull small amounts from one and then insert directly into another
• A function that moves entire data sets (like doing an "INSERT INTO as SELECT * FROM..." over a DBLINK in Oracle). Not sure how this might be done in XBASIC.

Opinions and suggestions are welcomed!

Thanks!

[Steve Workings]

Re: How best to move large data sets between tables?

I'm partial to the bulk insert method. Been doing it for some time now on tables ranging from a handful to 3.5 million records, and can be driven with Xbasic.

[parkjammer]

Re: How best to move large data sets between tables?

Steve,

Thanks for the input.

I believe my challenge with the bulk-insert method is that it is only helpful one direction... when inserting into a target table.
You still have to extract the data first.

Of course, I could be thinking of the typical Oracle "bulk insert" where the data has to be in a file first. Perhaps you can
"bulk insert" directly from a connection in XBASIC.

I will look it up!

Thanks!

[copernicusccl]

Re: How best to move large data sets between tables?

Hi,

I am not sure what your trying to do but I would suggest if you are using Oracle or mySql then you can use:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl WHERE filter;

With Oracle you can also avoid using temp space in memory.

Regards,

UO

[parkjammer]

Re: How best to move large data sets between tables?

UO,

My challenge is that I am not creating a new table or inserting records into a table
within the same database (connection-string).

I will be moving data from Database ABC to Database XYZ.

In an Oracle realm with a DBlink I could perform the "create table" or "insert into" in
Database XYZ while drawing data from Database ABC. It's a one-shot action that
touches two databases.

In XBASIC, I seem to have to do one of three things:

1. Pull data from ABC, put it in an interim file, then push it to XYZ.
2. Pull data from ABC, put it in a memory object (e.g. Table object), then push it to XYZ (not a good option for very large data sets)
3. Open two connections at the same time (which is fine) and use a loop to pull from ABC and push to XYZ

There's nothing inherently wrong with any of the methods... I'm simply trying to refine my
options to the best practice, whatever that may be.

Note: the only reason I note using a "loop" in the third option is that I'm not sure how big/fast/reliable/limited
the ResultSet objects may be. I can either go record-by-record or page-by-page (different sizes), but that
is likely better than trying to do the entire set at once.

Thanks for the input!

[copernicusccl]

Re: How best to move large data sets between tables?

Hi Alan,

Another option to look at if your using Oracle is external tables, I would resist doing it on a record by record basis and would try and confine any processing to server.

Cheers

Uday

[parkjammer]

Re: How best to move large data sets between tables?

UO,

Again, good suggestion.

Unfortunately I am not restricted to Oracle. I'm truly allowing mixed-mode ETL. Options for
data movement include Oracle, MySQL, SQL Server, MariaDB, XML files, TXT files, file-by-FTP, etc.

So if I'm moving records from a remote SQL Server database to another remote Oracle database,
I need to be able to move through open connections, which in turn implies one of the prior options.

I was trying not to complicate my original question by providing all the variations... but I guess
it helps in the end.