How to transfer 1 million records

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

How to transfer 1 million records

Postby KjetilUrne » Fri Jan 25, 2013 10:02 pm

How to transfer 1 million records the most efficient way?:

On a weekly basis i transfer approx 1 million records from Oracle to Ffenics. It takes more or less 25 minutes, used to be considerable (50%?) quicker (even if i omitted some fields on the Ff-side recently), so I've just begun to search for methods to speed it up.

The procedure is simply
Code: Select all
for ForeignOracleView ; enter a record in NativeFform copy all from ForeignOracleView


"NativeFform" being an exact copy of the OracleView, but not holding just as many fields (might not mean too much, but I hoped it would reduce the write-load). The Ff-fields have all required/index-field turned off, also done under the philosophy that it would take less to check/build whatever.

I do not suspect that infrastructural changes led to the decline in performance (on the contrary we equipped the server with an extra processor lately [which i suspect Ff not to take direct advantage of, but it should at least channel some of misc overload from other activity to the "idle processor"]. Also we increased the available disc space. Sounds probably idiotic, but my gut feeling is that Ff is worn out and simply is detested by processing all these bloody records over and over again :-)

Any suggestions on best practice?
Any alternative ideas to accomplish the same?
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: How to transfer 1 million records

Postby Phil Winkler » Sat Jan 26, 2013 12:59 am

Ensure there are no lookups or field derivations on the FF form.
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: How to transfer 1 million records

Postby KjetilUrne » Sat Jan 26, 2013 9:33 am

Phil Winkler wrote:Ensure there are no lookups or field derivations on the FF form.


There aren't any at all (they're all a plain copy of the Oracle view - and as such I don't think they are capable of inheriting any properties besides file type/length/required/index [?])
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: How to transfer 1 million records

Postby Pete Tabord » Sat Jan 26, 2013 9:39 am

Hi Kjetil

Ffenics - for that matter any Windows program - doesn't use multiple processors directly. But it is multi-threaded and that allows Windows management to distribute the work among the available processors. I have no idea in detail how that happens. I doubt that multi-threading is very applicable to this process, though.

Is this database local or on a server? If it is on a server, can you possibly run this procedure on the actual server? (You might be doing that already, of course!)

Has it got any unique fields? They do get carried across.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: How to transfer 1 million records

Postby benjji » Sat Jan 26, 2013 10:24 am

are any fields indexed in the native table ?
as when a new record is added, the indexes would be rebuilding themselves each time ?

----- awaiting for Pete to confirm or deny this !!!
benjji
 
Posts: 167
Joined: Fri Mar 14, 2008 11:26 am
Location: Stoke-on-Trent & Manchester & Middlesborough
Has thanked: 0 time
Been thanked: 0 time
 

Re: How to transfer 1 million records

Postby Pete Tabord » Sat Jan 26, 2013 6:43 pm

Yes, as each record is added the index(es) are updated.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: How to transfer 1 million records

Postby KjetilUrne » Sat Jan 26, 2013 9:52 pm

@Pete: No, it's on different servers (and ...ahem ... i am starting to be open to the idea that the network is part of the picture). And of course; the Oracle resides on a 3rd resource... I will move the app the coming days to see how much time it will cut.

@benjji: No, I've stripped the Ff-side down as much as possible (in sense of no indexes, no required fields).
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: How to transfer 1 million records

Postby Adrian Jones » Mon Jan 28, 2013 10:18 am

For that number of records, and given you are using a proc to process them (rather than import them directly from a file), 25 mins sounds quite reasonable to me!

But you imply that you were getting it to run in around 17 mins, so what has changed that might affect the result? The Ff version, for example? Changes to the target form?

It is not documented to my knowledge, but 'copy all from' takes a snapshot of available fields when you compile the procedure. And having just checked this, it seems to also include the derivation of any virtuals.

If you want to check this, just open up the FRM for this copy all from procedure in a text editor to see what I mean.

So if you have made changes to the target form, you might want to recompile your procedure to make sure it reflects the current state of play.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: How to transfer 1 million records

Postby Graham Smith » Mon Jan 28, 2013 2:31 pm

Kjetil, do these records get added to records that are already there? If so, you must have many millions of records in that table.

Here is a question Pete can answer. It's my impression that the larger the file becomes, the longer it takes to add additional records.

Kjetil, have you tested to see how fast an import would run? It's been my experience that you can export the data from Oracle and import it into a database faster than doing it the way you are.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: How to transfer 1 million records

Postby KjetilUrne » Mon Jan 28, 2013 7:42 pm

At first I would like to thank you all for your contribution, interest and help. I must admit i made a full withdrawal today after performance testing with the executable and the app on the same physical device. I was really stubborn and refused the idea that it would have a greater impact, but it really did: Today's import: a catch of 919518 records (okay! I exaggerated a little :) ) in 3 minutes 34 seconds. Not bad, not bad! The whole data-analysis (import included) was down to 17 min 45 secs.


Adrian Jones wrote:For that number of records, and given you are using a proc to process them (rather than import them directly from a file), 25 mins sounds quite reasonable to me!

Each record is quite small (4 num fields [7-10], 2 date fields , 2 text fields [18 & 40 chrs], so it probably explains how it's possible. However, one scenario is that the job will 10-15 times as big in the future, so every millisecond counts.


Adrian Jones wrote:But you imply that you were getting it to run in around 17 mins, so what has changed that might affect the result? The Ff version, for example? Changes to the target form?

The main reason that I wanted to blame Ff was the following: this analysis is something i basically "should" do every week, but traditionally i've waited 3-4 months and built a huge backlog. So; in that situation I would loop the procedure, observing that loop 1 would take 'less than half an hour', loop 2 'around 1 hour' and most times i would kill loop 3 because it would seem to go on into infinity. I still don't have any good explanation for this, as all forms holding a considerable amount of records would be emptied in between.


Adrian Jones wrote:It is not documented to my knowledge, but 'copy all from' takes a snapshot of available fields when you compile the procedure. And having just checked this, it seems to also include the derivation of any virtuals.
If you want to check this, just open up the FRM for this copy all from procedure in a text editor to see what I mean.
So if you have made changes to the target form, you might want to recompile your procedure to make sure it reflects the current state of play.

At a general level; making changes in the Oracle structure (ie. adding a field in a view), makes it necessary to refresh the db-link AND the native Fform AND whatever proc that is involved to copy the data. I think. One thing I'm sure of is that I've wasted lots of time after such a change until everything loads perfectly again. :-)


Graham Smith wrote:Kjetil, do these records get added to records that are already there? If so, you must have many millions of records in that table.

No, i run a 'delete records' before starting to fill up again. However, the loop-challenge described above: could it be that records aren't physically flushed before you ie. exit Ff if a loop is involved? Yes, i agree it sounds ridiculous :-)


Graham Smith wrote:Here is a question Pete can answer. It's my impression that the larger the file becomes, the longer it takes to add additional records.

This is my impression too. Still, could it be that my situation here (starting from an empty form) is different from starting with a form with a considerable amount of records already? IOW; the last 500k records in my full total import would take less time than the 2nd run of a 50/50 import. Could easily be tested of course....


Graham Smith wrote:Kjetil, have you tested to see how fast an import would run? It's been my experience that you can export the data from Oracle and import it into a database faster than doing it the way you are.

I'm not sure what kind of import you're thinking of? Ie a brute import from a text-file? care to elaborate?
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: How to transfer 1 million records

Postby Phil Winkler » Mon Jan 28, 2013 10:13 pm

Kjetil,

You are doing: delete records in Formname ." aren't you and not: for Formname ; delete records. The 1st is the only one to use.
Phil Winkler
PLM Consulting, Inc.
pwinkler@plmconsulting.com
Phil Winkler
 
Posts: 889
Joined: Fri Sep 07, 2007 12:45 pm
Has thanked: 0 time
Been thanked: 0 time
 

Re: How to transfer 1 million records

Postby KjetilUrne » Tue Jan 29, 2013 8:41 am

Yes I always do when deleting all, Phil. Just for the record: It takes 33 sec to delete with method #2, hardly measurable (< 1 sec) when using method #1.
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: How to transfer 1 million records

Postby Pete Tabord » Tue Jan 29, 2013 11:06 am

Kjetil - are you doing the delete in a separate procedure to the copy all? It might help if you did. (Just to make absolutely sure files get closed and reopened 'clean'.)
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 

Re: How to transfer 1 million records

Postby KjetilUrne » Tue Jan 29, 2013 11:16 am

No, in the same procedure, Pete.

It did strike me that one shouldn't mix too much (in particular Reorg), so I'll keep your recommendation in mind!

Still, any sense that it should behave differently on loop#1 than the consecutive loops?
KjetilUrne
 
Posts: 602
Joined: Mon Oct 08, 2007 8:21 am
Location: Norway
Has thanked: 4 times
Been thanked: 1 time
 

Re: How to transfer 1 million records

Postby Graham Smith » Tue Jan 29, 2013 1:18 pm

Is this done as an automated procedure or do you do it yourself?

It works much faster if you run it on the server because when you do it on a workstation, every record is pulled down to the workstation then sent back up to the server. Remember, Ff is a file server db not a client server, so it is the workstation that does all the work. If you use the server as a workstation, you avoid moving all that data through the network pipes.

Delete records in zeros the record count in the RDRR file, deletes the DBM and creates a new zero length DBM. For FormName ; delete records . works one record at a time - sort of. Pete can correct me but if you lock the file exclusive before you do the delete, the latter will do the same as the former but it does take a bit longer.

It is not mandatory to put the delete records in separately but there are a few cases where it's a good idea. If you are running it on a locked database, it should not make any difference. If not then putting it in a separate procedure becomes something of a "just in case" type of thing. Like the Jewish mom said, "Couldn't hurt."

I worked on a DFD/SQL application a long time ago and as part of it, it was necessary to pull a large amount of data into DFD at the end of each week for reporting. What worked best was that we setup an export job on the SQL side and created a CSV file. That was then imported into DFD. The export took only a few seconds and the import ran considerably faster than doing a copy all from within the application.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 
 
Next

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest

cron