Import taking 2 days & only half done!

[LaRetta]

I'm importing a MER containing 200,000 records into my LineItems. Purpose is to capture order history from web sales (up through current sales). I've imported into LineItems before and it's been very slow - even when unclicking Perform Auto Enter. So this time, I decided that the 'slowness' may be because of all the TOs attached to my LineItems TO.

So I created a new table occurence of LineItems, created an associated layout, unclicked Perform Auto Enter and began my import - theory being the import wouldn't have to update all the associated table dependencies (?). I then left on trip. I'm back and ... oh good grief, the import is only half done!!! It still has 78,000,000 bytes to go! I assume it's because of the calcs in LineItems but I couldn't delete them out just to import.

Truth is ... for the first time in two years, I CAN'T work in FileMaker and it is giving me a much-needed break but it will take another two days, I believe. The counter is running and I hear/feel the HD chugging along fine so I hesitate to stop it. It's just over half way. Management wants this information. I'm afraid to stop it in case I have to just repeat it (and lose all it's done so far).

Ideas? Help please? I don't know how else to get the data in without importing but I think I'll go crazy not being able to do anything else in FileMaker for ANOTHER two days.

LaRetta

[QuinTech]

Hi, LaRetta. We have seen similar numbers in our version 6 program. Several of our clients have experienced import times of greater than 24 hours for approximately 20,000 records. It is our feeling that it is based somewhat on calc fields but even more on summary fields. (Our situation is a bit different in that we import from a FM file to a FM file, but I think my suggestion would hold true for you.)

We have reduced the import time vastly (to less than 1 hour) by importing to a clone of the file with no calc or summary fields and then into the destination file. That is, we made a duplicate of the destination file and called it "FileShell.fp5". We then changed all the Calc and Summary fields to Number fields. We then import our data into that shell, and import from there into the destination file. I don't know why, but that has really sped up the process for us. I suspect that just opening the .mer file in Filemaker will give you the calculation-less FM file you need. That might be the easy option.

If I were in your shoes, I would probably bite the bullet and abort the import; there's no evidence to suggest that the second half will import as quickly as the first half. In fact, I suspect it will take much longer. If you need to resume it, you could just figure out the record at which it stopped, winnow your .mer file as needed, and start it again. If you do so, defragging before running the second half of the import may speed things up.

J

[BobWeaver]

I suspect that it has to do with indexing the fields that are being imported. You could try turning off indexing before importing. Since you now are doing it in an unrelated table, it shouldn't matter. Later when you re-link your relationships, the indexing of the key field will have to be done, but maybe it will be quicker if the records are all there already.

[LaRetta]

Thank you Jerry. Maybe I approached this wrong ... Web Store invoice totals have been pulled into Excel for years and I imported them into FM 18 months ago. Owner didn't think detail was necessary (at that time) so I just display a calc If(IsEmpty(LineItems::InvoiceNo); "Web Store. Detail Unavailable"; "") and place it over the portal (below the first row so it doesn't disappear).

However, owner wants to capture sales per product now and I thought it best to have it in my LineItems instead of creating another table (particularly since the Invoice is combined).

Well, when I get home tonight, I'll stop the process I guess. I swear 7 is slower at importing, deleting etc. than 6 was (maybe my imagination). And I don't understand either how an intermediate table (with calcs changed to numbers) would help because eventually it'll have to go into LineItems and my calculations will evaluate anyway - very strange that it helps but it certainly is worth the try! Thank you so much!

Does it matter which TO I use when Importing or Deleting? It seems they ALL are simply representations of the table but if a TO has several joins to it then it might take longer to import/delete etc. than a TO with nothing attached to it or does that matter at all? If one were to do regular imports, would it be quicker to have a TO with nothing attached to it to use exclusively for imports & deletions? In other words, do imports only affect the one TO - or are connected TOs also *updated* somehow (table dependencies)? Am I nuts for even asking such questions?

LaRetta

[LaRetta]

Hi Bob,

You mean turn off indexing on every field in LineItems that is indexed or is there something else you mean? I'll try everything suggested at this point!

"Since you now are doing it in an unrelated table, it shouldn't matter. Later when you re-link your relationships, the indexing of the key field will have to be done, but maybe it will be quicker if the records are all there already."

It's a new LineItems TO I created and it's unrelated yes. But I didn't plan to re-link THIS TO later (only delete it). I have many LineItems TOs in my graph (too many, blush) with many different relationships. But the reindexing will happen in only the LineItems TABLE, correct? IOW, will the reindexing take 8 times as long because I have 8 LineItem TOs or only once on the table itself? I sure wish I understood how FM *sees* all of this ... does anyone know where I might find out about Table Dependencies and such? I think it would help me in many different circumstances.

LaRetta

[QuinTech]

LaRetta said:

I don't understand either how an intermediate table would help

Nor do I. I am mystified by it. But it seems to have worked well for us.

LaRetta said:

Does it matter which TO I use when Importing or Deleting?

I have no idea. We are stuck in version 6 for at least several more months, and this import procedure in v7 is one of the things we have not even begun to think about.

Bob said:

I suspect that it has to do with indexing the fields that are being imported.

This is our suspicion, too, but in our case it seems that summaries have a lot to do with it. We notice that when we do an export from this complex file, it will go through a ton of iterations calculating summaries. I believe we have turned off indexing and found it didn't make a noticeable difference; but this could be because the summaries slowed things down so much, that we didn't notice the time savings due to removal of indexing.

LaRetta, I'm curious: Do you have a bunch of (or ANY) summary fields?

[LaRetta]

Jerry asked ... Do you have a bunch of (or ANY) summary fields?

We have one Summary field = Total of Quantity. I am quite intrigued by this scenario. If testing didn't take days I'd have a ball playing with options and trying different things. But I think it's important enough that I'll take the time (just wish I had a second computer at home I could run it on for the tests).

I also have 11 unstored calcs in LineItems and, oh dear Lord I just noticed, almost EVERY field in LineItems is indexed!!! How'd that happen!!??!!

Time to re-group, me thinks ...

LaRetta

[comment]

Does it matter which TO I use when Importing or Deleting?

I don't think so. If data is entered into a field, then the data is in the field, regardless of TO.

And I don't understand either how an intermediate table (with calcs changed to numbers) would help

I don't know IF it would help, but if it does, I have a guess WHY:

Filemaker writes almost everything directly to disk. The order of writing and chunk sizes can make a big difference in speed. So it could very well be that import all - calculate all - index all would be faster, even dramatically faster, than a loop of import one - calculate one - index one.

Pure speculation, of course...

[LaRetta]

Thank you, Michael - your speculation makes good sense.

I plan to run some tests tonight (on smaller chunks of 80,000 or so). Removing the Summary then importing; removing the Indexing then importing; and importing from intermediate (and every combination thereof). For some reason, I had an experience where using a different TO made a HUGE difference during an import but now I can't recall why (I was quite tired at the time). Maybe I was also pulling to a related TO and, in one TOG it was one away, while in the other import it was two away. Make sense? Probably not. I even recall posting about it. I think I'll also research that further when I get time.

Thank you all so much for helping me! I'll let you know my findings. I would hate for anyone to have to stare at a progress bar for four days and (worse) be without FileMaker!! Without FM at home, I've been sweating, getting the shakes and my nose has started running.

LaRetta

[BobWeaver]

Unless something has changed in FM7, Summaries only calculate when displayed on the current layout, and the summary calculation should not happen until after the import is finished. So, I don't think the summaries are the culprit.

I remember someone mentioning in a previous post that doing an export where the fields were based on a different TO than the current layout caused a huge delay. Could be the same thing for an import. So make sure that the current layout and import fields are the same TO.

[LaRetta]

... someone mentioning in a previous post that doing an export where the fields were based on a different TO than the current layout caused a huge delay.

Yes! That was me!! And it still intrigues me (now that you remind me) why a 'representation' of the same table would make a difference!! Maybe there are more to table occurences than always meets the eye, ie, are they treated by FM always like another file? Are they considered 'closed' until a layout references them -- all sorts of wonderings. I remember it (well) now (grin) this same LineItems file too. And that's what springs to mind -- okay not the details but the 'other' TO issue and how it sped up when switching the source. I have it on my long list of Must Figure Out. I wish I understood how FM internally decides and handles these things (Table of Dependencies and all of that).

I'm 99.9% sure I matched the layout TO and the Import Destination TO. I knew it was critical because of my prior experience with TOs and exporting (you know ... the one I had forgotten). I can check when I stop the import because the same map will pop up.

Well, I decided NOT to stop the import ... for tonight. I realized I needed this break. What can one do if they can't work on the program and their computer is running dawg-slow because FM has all resources tied up? Well, I've had a few nights doing regular life stuff (and doing nothing) ... even got out my guitar!

I think understanding this is a very important issue I will attack ... tomorrow.

LaRetta

[comment]

If a TO were "another file", then it should be possible to import from another TO of the same table. It isn't, and that should be a hint of the underlying structure.

I am not sure how layout choice would affect this, except of course the issue of what's ON the layout. It would be interesting to compare import speed between a layout that contains summaries, for example, and one that is blank (but same TO).

[xochi]

Please read this thread: http://www.fmforums.com/threads/showflat...true#Post102196

In my experience, 7.0v3 improved things somewhat, but FM7 imports are still tricky -- put the wrong calculated or summary field in and performance can really drag.

One further tip : I believe the performance hit is due to re-indexing fields. It seems that if any of your calc / autoenter / lookup fields reference a self-relation, this will mean that the field index will be rebuilt for every record. Also, I've found that performance grinds to a halt if the index is larger than the cache size. And, i've found that the "flush cache on 'idle' setting" seems to consider "in the middle of an import operation" as "idle".

So, to help performance, set your Cache to as large as possible, and set the flush to "1 hour" or longer.

FileMaker really needs to rewrite their import code : right now, it looks like they evaluate auto-enter fields on a per-record basis while importing. This kills performance. What they should do is turn off field indexing during import, and when the import concludes, the auto-enter calcs and indexes should be evaluated.

[QuinTech]

Given the weight of opinion pointing towards unindexing fields as a method of speeding up an import, I am interested in testing this for myself.

Anyone know of a good, quick way to unindex several hundred fields? (I had hoped "Save a compressed copy" might do this, but it doesn't, at least not in FM6.)

J

[LaRetta]

I wanted to run additional tests first but I can't give up my system that long. Here's what happened: I stopped the import after 3 1/2 days. It had only imported 120,000 records. I was on the proper LineItems TO, Form layout with no fields, my map was correct and I didn't have checked 'Perform Auto-Enter'.

I dropped that same .mer onto Developer shortcut. It took 40 minutes to import the 200,000-record mer!! I then turned off indexing on every field in my LineItems (yeah Jerry, a pain for sure and I'm unaware of ways to force all fields to unindex). Using Omit Multiple, Show Omitted I then pulled the remaining 80,000 records from that fp7 to my LineItems. It finished in 3 hours!

Of course it took 1 1/2 hours to prepare the process (import to fp7, turn off indexing in LineItems fields, exit Define Fields, import, turn indexing back on and exit Define Fields again) but it takes no math whiz to see this kind of comparison ... 90+ hours vs. 12. How much was saved by turning off indexing; how much saved by using an fp7? Unsure. But performing both together sure made a winning combination! I plan to test both individually as soon as I can, as well as testing summary fields (and also importing, exporting and finding based upon different LineItem table occurrences, grin).

FM should turn off indexing while importing or allow us a checkbox with the option (as it does now on Auto-Enter and Lookups). xochi, thanks for the input also. I've never messed with cache before.

Michael, I mis-spoke before (my mind gets ahead of my brain, ROFLMAO!). I meant Table not table occurrence. Example: Export Contacts::ContactName from LineItem TO. Is Contact table open? Is it 'open' because it's file is open? Are all tables opened? Or is each table like a file and only opens if its field is referenced on a layout? Geez. Whether opened or not; whether referenced through 3 relationships or one - does it make a speed difference?

Pulling the Contact Name from related table through one LineItem TO appears to be different than pulling it through a different LineItem TO. I know Lookup response times vary. So when exporting Contact Name, choose the LineItem TO with most direct route (with fewest filtering). Make sense? Probably not ... my perspective is a bit strange.

A TO is a representation of a certain point of view and the complexity of various relationships can affect import/export (and Finds) of related fields. I've changed TOs (when the results would be the same) and one will drag while the other will zip. You can go to New York from Los Angeles via London if not careful. And you all have saved me hours on this and probably hundreds of future hours!! Ah well I can drive myself quite batty with speculation ... it's all intriguing stuff.

LaRetta