Importing records

[Simmo]

Hi all, I'm new to the forum and hope someone has had a similar problem and can offer some advice.

I have a database of about 2000 records and 500 fields, when doing maintenance on it I make a copy first, make the changes and then import the 'live' version back in.

It used to take 30 minutes or so to load all the records back, but now takes more than 5 hours! The only significant change I made that could slow it down was to add about another 20 summary fields. Has anybody had a similar experience?

Thanks.

[LaRetta]

Hi csimmonds,

Well I can't imagine a database with 500 fields! Was that a typo? Also, why are you importing the same data back in? I'm afraid this makes no sense to me either, although you may have good reasons. If you have 500 fields within one database, I highly suggest that you re-think your structure. It will continue to slow down as it grows.

Having said that, summary fields are quite resource-intensive. Work on a layout which doesn't display any summary fields when performing tasks (such as import/export). Each time you switch (or view or scroll) a layout with summary fields, they all must be recalculated (redrawn and redisplayed) before anything else can take place.

But I'm quite serious about your structure. I don't believe I've ever heard of one database containing that many fields.

LaRetta

[Simmo]

Hi LaRetta,

Thanks for the response.

I didn't think 500 fields (518 actually) was excessive, my database is a work flow solution which tracks circuit designs from concept to construction, it started life as a simple register but just grew and grew, and before I knew it, 518 fields!

I have to import on a regular basis because to prevent any down time on the database I take copy, make changes and import the 'live' database records back in again, I don't know of any other method?

Thanks again for the comments.

Chris

[LaRetta]

Chris said...

it started life as a simple register but just grew and grew, and before I knew it, 518 fields!

Yep, that's how it happens! I still don't believe you need that many fields in one database. I'll bet you over 3/4 of those fields should be in another db (or more). I don't mean to alarm you in any way but I still think your 'urban sprawl' is going to cause you major problems throughout its life (and yours).

Are there any related files? I would be curious to see a cloned empty copy of your file - or a list of your field names. I suggest you seriously consider a rebuild instead of continually fixing the fix. We would be more than happy to assist you by offering alternatives to your structure. And we'd even help you do it, Chris ... at least I would.

I cringe at the thought of what you'll be going through working with such a db. Even if every field is 1:1, much of that data can be kept in a related file to ease the pressure on your existing db.

As far as your import procedures, well, I think you're talking about making design changes. In that case, it makes sense. If you give us as much info as you can, it will help us make the best suggestions for your structure. Just consider it anyway, okay?

Cheers,

LaRetta

[kenneth2k1]

One of my solutions has:

604 fields

245 layouts

460 scripts

And only 1 relationship!

[-Queue-]

I could do some serious calculations and cut down your fields by at least a third, your layouts by half, and possibly your scripts by 1/4 to 1/2, hehe.

[kenneth2k1]

So could I - but screw it!

To be fair, it was my first solution, so some of the fields/layouts/scripts are vestigial now. Still works like a charm!

Ken

[kenneth2k1]

Unless of course you are doing pro bono work?

[-Queue-]

All the time, baby. It all depends on what sort of problems I have to solve and the numerical amount of satisfaction which my brain gleans from it.

I wouldn't mind at least checking out the field defs to see how they could be combined and structured more efficiently. Plus, I'm just ******* curious to see this massive file in action as I would be intrigued by a train wreck.

[LaRetta]

Queue said ...

I wouldn't mind at least checking out the field defs to see how they could be combined and structured more efficiently.

Hey Queue! Sounds like an efficiency addiction to me - perfect for this line of work! I know it well! When a business says they have problems, my heart starts pounding and I shake with an adrenalin rush. Problem-solvers are drawn to FileMaker like flies to honey.

L

[-Queue-]

Yeah, and FileMaker doesn't lady canine at me for trying to solve all of its problems and ignoring my own, as most females I know are apt to do.

[kenneth2k1]

Oooh! Was that an open invitation to pile on females?? I'm game!

If FM was a female, it would be a most amazing one. It would be a high-priced skank that's been around. But it would also tell you exactly what is wrong, instead of saying "nothing" and ignoring you. And you (mostly) know what to expect.

Just kidding, LaRetta (not really)

Anyways, the reason they're are so many fields is because it is a marketing db and has many many letters. Each Letter requires 3 fields: 1. A default letter to revert back to 2. The actual letter 3. A calc of the letter that substitutes merge field tags with the field's data.

These letters print based on strategies. So there are also many other calc fields that tell when a certain letter prints. And the many scripts will find on these fields and determine when another letter should print. There are other various items that I don't need to explain and you probably don't care about.

In hindsight, I would have 4 files: 1.Prospects 2.Agents 3.Campaigns 4.Letters.

But right now I only have 2: 1.Prospects (which houses the campaigns and letters) 2.Agents.

And all is well in sunny San Diego

I appreciate your willingness to help. I guess we are all doing pro bono work to some extent!

Ken

[LaRetta]

No offense taken, Ken! Bad behaviours aren't gender-specific any more than are good behaviours.

LaRetta

[kenneth2k1]

Touche

[-Queue-]

ROFL No, I wasn't encouraging a free-for-all gender bash. I was referring to specific instances in my experience, not generalizing on females as a whole.

Nice comeback, though, LaRetta.

[Fenton]

I don't know if 500 fields is excessive or not. Seems like that some of it could be off-loaded to related files. That however is not going to speed up imports, since you'd still have to import the data into them.

It seems odd that summary field would slow down importing so much, UNLESS they are on a visible layout. This is a known "gotcha." Try a simple layout and see if speed improves.

And get them to buy you a fastercomputer :-)