Export and Import of Data

[ConvertFromOldNGs]

by tyc >> Tue, 9 Jul 2002 6:59:49 GMT

Hi Jade Community,

Does anyone have any idea how to improve on the export and import of
data stored in the database. I know that Jade provides a data export
and import functionality, but it is quite slow. We have about 500
records and it takes about 1.5 - 2 hours to export everything out. And the speed seems to be degrading exponentially.

Please advise
Regards,
L.C. Lim

[ConvertFromOldNGs]

by cdshearer >> Tue, 9 Jul 2002 8:47:13 GMT

This does seem rather excessive - if your numbers are correct, this is up to 36 seconds per record! (7200 seconds in 2 hours, divided by 500 records = 36 seconds)

What exactly are you doing? Have you tried using the JADE Profiler to see where it's spending its time? How complex are your records you are extracting? (Even with extremely complex data, I can't imagine what would take 36 seconds... but perhaps you have more than 500 - it's not 500,000 is it?)

Craig

[ConvertFromOldNGs]

by tyc >> Wed, 10 Jul 2002 10:15:20 GMT

Hi Craig,
I know.. We did suspect that storing of jpg (it's a HR, Payroll
system) of the employee that was the problem.

In another system that is slow, the number of fields are a lot, but
with just 200 employees's just plain text info, Jade should easily crunch it (search functions within our system is fine).. it's just the coughing out of the data to be kept as backup that is killing.

is it possible that our export/import function needs to track all relationship (and it's quite complex relationships) that affects the speed tremendously..

do you do import/export of data? What's the best strategy in your
opinion (given Jade Database export, and the one we basically modified ourselves) seems to take just as long?

Regards,
L.C. Lim

[ConvertFromOldNGs]

by cdshearer >> Wed, 10 Jul 2002 11:16:21 GMT

OK, well it still sounds very slow, even if the employee data contains jpg images - blatting each one out to a file should be fast, unless you're doing it byte by byte!

I'm a little confused - are you saying that you don't have access to the code or it's somebody else's system?

Craig

[ConvertFromOldNGs]

by allistar >> Wed, 10 Jul 2002 21:05:41 GMT

Hi,
A way we have done data extraction/importing is to look at the Jade meta-data and export based on that. That way you should be able to extract in a format that ties up the values exported with the name of
the property. You'll need to be a bit clever about what data gets exported, because if you let it export everything off an object you
will probably end up exporting the entire database (which may not be
what you want). You need to let the process know which references
should be "deeply" exported and which ones just need a "shallow"
export. That should be able to be done generically enough.

As for importing, you need to keep in mind that the system you are importing into will probably have different oid's for the "common" or "shallow export" objects. You'll need a way of finding those objects based on some sort of key. All of that information could be stored in
the export file itself, or as part of the actual process.

The trick is in keeping the size of the export file down, you could either compress it yourself and/or build a table at the beginning of
the file that contains lookups so yiou don't have to extract the
entire property name for each object, just a byte representing that property.

I can't comment on your performance issue, but it does sound
excessive. I would suggest profiling it to see where the bottleneck
is.

Allistar. ------------------------------------------------------------------
Allistar Melville
Software Developer
Auckland, NEW ZEALAND

Greentree International,
Developer of Greentree Financial Software. ------------------------------------------------------------------