Moving a Field in a huge Database

Panorama X Beta 10.2
1

Troubleshooting performance issues in a shared database, I suspected that a lengthy beachball on a set of checkboxes might have something to do with being conencted to the 831st field of the db. (not my doing)

After converting to single user and a lot of jockying in showing and hiding fields, along with movefieldbefore, I got the field to the front of the database. But there it no longer works at all via the checkboxes - and it’s no longer shared for me to truly evaluate the difference.

Before I pursue this route further, am I barking up the right tree in that the field’s position in the database may have significant impact on its performance?

2

I think the field position would have a noticeable impact for operations like formulafill, sorting or even searches. Whenever Panorama accesses or modifies a field, it has to scan over all of the previous fields in the record. If you’re performing this operation thousands of times over, this time adds up. But to simply toggle a checkbox in the current record, the time should be negligible.

Note - it should make no difference whether the database is single user or shared. Panorama uses record locking, not field locking, so the number of fields makes no difference as far as sharing is concerned.

If the database has hundreds of fields then maybe you have hundreds of items on your form? That can cause performance issues. In that situation a good solution is to use tab panels and break it up so that no so many form items are visible at one time. Note that switching between tab panels doesn’t affect record locking.

3

Yeah, there are dozens of forms with as many as 8 full-page Data tiles, each covered with checkboxes and radio buttons, plus dynamic notes to the side of the tiles. According to them, speed is okay sometimes but tends to degrade. (previously many of the buttons also ran procedures on the shared system)

Their objective is to print each report at some point, thus all the Data tiles. My role is troubleshooting and advising, so it’s challenging to come up with usable advice at this point.

4

Not for nothing, but is the database huge in fields or records. With complete ignorance of the fields/data involved, I always raise an eyebrow when I hear there are so many fields in one database.

  • as an aside, I think we all began creating treating an address as four fields (street, city, state, zip ) when I’ve never had to have them separate. If I did need to search for “all in this zip code”, I could still search a single address field with Contains (that an address number and zip code number could be the same was accounted for with appropriate delimiters). -

Panorama is so fast with export/import, maybe you could break the “big” database into smaller units. For example, if you had a database with 100 things, but you only dealt with 10 things at a time. You could make 10 little things, and export from the 100 thing database to the appropriate 1 out of 10 smaller database and deal with them there. That assumes the time saving while working in 1 of the 10 is greater than the overhead of the export/import.

The only “real world” example I can thing of - and it’s DECADES old - was a UPS rate calculation database. Instead of having all the rates for all the shipping methods, for all the zip code areas, in one database, I had them split into several smaller database. When the user specified the zip code and shipping method (2nd day air, etc.) Then I imported the rates just for that.

Sort of - because, given the package weight, I can see it could be useful to see the rates for all the shipping options to one zip code. So maybe the divisions were on other categories.

The point is, sometimes several smaller things are faster than one big thing. The problem is, in design, we often think like people instead of computers. What is difficult for us - determine all combinations of ten unique digits without repeating a digit (i.e 10 factorial - 3628800 different numbers), sounds like a lot of work. It takes my computer a fraction of a second to do that.

At first, I was going to generate all those numbers and save them to a text file. When I needed them, I could read the text file. Turned out, it was much faster to calculate them on the fly then it was to read in all those records. Note we are only taking about seconds here on something the might be run once every two months. Programmers would cheer at the time savings. Supervisors would “talk to you” about wasting time. Just Say’n.

Also, the only business I know that is happy to radically redesign something that already works is a grocery store chain - when they move all the food to different locations. Or maybe MS Windows versions. You get essentially what you had before; it’s just that you can’t find anything because stuff has been moved or renamed.

5

I didn’t mention this because it seemed clear that Jim’s eyebrow was already raised. Unfortunately the person that actually designed this database probably isn’t reading the forum.

I’m going to disagree for this particular example. It’s pretty common to need to sort by city, state or zip, and to do that, you’ll need to use separate fields.

6

Yeah, I can see that for a comprehensive report. I was thinking of an office were the work was distributed by area to specific individuals. So they only needed to select records for their area.

And I’m guessing the “cost” of those extra fields is minimal. I was thinking of the “great mailing label exercise” where you had to combine all those fields (occasionally two address lines) with all their variations when one text box, with appropriate separated address data. It would do the job and still allow record selection on city/state/zip.

But you couldn’t sort on them (without more programming and maybe an extra field you’d fill with a sorting code).

It was more an “Do you really need all that info at once or could you break some things out for more speed?” idea.

7

Not to pile on, but in my databases of annual conventions for a nonprofit, it is quite useful to be able to sort/select/analyze by city, state, and zip. Do it all the time. On the other side of ledger, I inherited a local nonprofit’s data and left the address block in one clump, regretting it ever since, because it can be awkward to export, format, and print a multi-line block of data.

8

Not to throw in a “Yeah but” and defend myself kicking and screaming :smiley:

But beauty and methods are in the mind of the beholder. And in the world of “one-time” efforts. So the following isn’t to defend my point but to give a perspective that there are different ways to pluck a chicken (apologizes to vegans and vegetarians)

Your one-time effort is to construct a format for printing a block address (when needed) accommodating all the variations of “address”. that includes a possible “Atten:” line, multiple address lines, and different zip code formats. Various one, two, three, and four (or more) words/prefixes/suffixes in a person’s name, company name (or not), etc.

Now sure enough, Jim has done a grand job of making that as easy as possible with functions like sandwich() and other address-related tools that work on most “standard” names and addresses.

But building a text object with formulas that will construct a formatted address label for ALL possible elements is … non-trivial. And still - it’s a one-time effort.

Consider another way - All the address info is in one field, with perhaps the Company and Person’s name separate - because you may want them as elements in a form letter (i.e Dear [person’s first name field])

Also, your database has a spare text field (and also spare date and number fields because they come in handy). The one-time task is to create a routine that will extract - for example the state - from the address block. The address block can have delimiter characters that can be disappeared at print/display time.

This one-time effort is to pull the desired address element - like city - from the block, put it in the spare field, then maybe select - if you couldn’t do that already just selecting on the element in the address block - and sort on the spare field.

A lot of “This way or that way” depends on the nature of the data. Are they just domestic with one name field, one or two address lines, city, state, zip - and only USA addresses. Or does it incorporate international elements.

So sure. For some, maybe most, situations, separate fields for the address elements is the way to go. It’s just not the ONLY way to go.

That’s my story and I’m stick’n to it. :slightly_smiling_face:

9

Deciding how to split up your data into fields is usually the most important decision in setting up a database. The field arrangement should be carefully thought out for every application.