CPD bloat

You guys are awesome. I'm going to have to reread and digest your comments some more, but here's some additional info:
- I always leave the cpd in Access 97 - didn't even consider converting it.
- The build expression is 99 characters total.
- Project folder structure is three levels: root, first subfolder level, and one more sublevel after that.

Ruminating...
G

The database table for hyperlinks is a cross-reference by topic ID numbers, so its columns contain only a few digits. Same for links to graphics, pictures, image maps and so on.

The Topic List table has a lot of bloat potential. The more I get into it, the more questions I have.

1. Each topic record has a column for Build Tags and another for Context Build tags. Each contains a string of build tag names, separated by commas, presumably the tags that you have applied to the topic and/or text in it. And, presumably, the tags still apply to the topic and/or text.

So a string of very long tag names uses more memory than a string of short tag names. This can explain only a small amount of bloat, however, because a string of text doesn't use much space.

2. When you un-apply a build tag, presumably the Meta tag is deleted from the source code, and RH edits the database table. But RH slips up on housekeeping here.

I'm not certain why. But I found a lot of spurious entries for build tags in several topics.

Perhaps they were tags that had been applied and unapplied. But the table isn't updated. Call this a Type A problem.

Some of these topics were imported from earlier projects, where the tags were applied. In the new project however, the build tag didn't exist when I imported the topic. Maybe RH made note of it somewhere. It didn't automatically create a tag in the new project, and there's no way to see it unless you happen to see the old Meta tag in the source file. Call this a Type B problem.

So a long string of spurious entries of long tag names will use some undetermined amount of storage space.

3. A further complication: The Topic List table contained some spurious entries for topics where the tags were absent from the project as well as source code. In other words, there is no old build tag code in the topic, and the project doesn't have this specific build tag, but the topic record in the table has multiple entries in the build tag columns. This is a special case of Type A.

4. OK. Let's throw away the .cpd and let RH rebuild it. That should clean it up, right?

No. Go figure.

In the new .cpd file some records were cleaned up, probably Type A detritus But some were not, maybe because the old Meta tags were still in the source, the Type B case.

I'm not certain because I haven't tracked down each and every difference between the old .cpd file and the new one, against the topic source code.

5. At first I thought the folder structure wasn't a problem, because each topic folder has an ID, and each topic record refers to the folder code, one or more digits. (By the way, in rebuilding the .cpd file, RH condenses the ID numbers and assigned new ones for topics, project folders and build tags, for example.)

However, there is a column headed TopicStringID that shows the file/path string needed, I think, for "breadcrumbs." It reflects the multilevel folder structure. So whether you use it or not, the breadcrumbs feature adds something to the .cpd file size.

Probably there are other factors in running a 5 MB .cpd file up to 20+ MB.

But this leads me back to my favorite rant:

Be stingy with file names. Yes, we've come a long way from the FORTRAN and DOS contraints of 4, 6 or 8-character labels. But that desn't negate the value of economy in naming conventions.

Same goes for any other set of labels, like build tags.

Keep the project file structure shallow to avoid long strings in hyperlinks. Remember, the output TOC can have as many levels as you think necessary to organize the material, while the project directories can be different. Yes, it's hard to keep all topics in the top level folder, but a two-or three-deep structure should be enough for managing the project, even though you may have a five-level TOC in the output.

Did I mention you should use short folder names, too?

I welcome comments and rebuttal.

Harvey

The limit is 255 characters.

Don't upgrade the CPD. Keep it 97 compatible. It might not do any harm but why risk it?

Another thought --

When compacting and repairing the .cpd file in Access, did you see an option to upgrade it for compatibility with the latest release of MS Access on your computer? (See my new question in this forum about Access 2007.)

H

G --

I wasn't aware of RH rebuilding the cpd file on opening the project. Yes, it should open the file and revise it while you're working in the project, but rebuilding it from scratch (and, I'm guessing from your comment, inflating it to the pre-compacted size) is something new for me.

Peter, Dirk, Rick -- Any insights here?

G --
You confirm that you have long filenames and build tags. Are you replicating that in your test project?

H

Nice one Dirk. I have just created Snippet 78 on my site so that information does not get lost. There's also a link to the download page.

Have you tried to "Jetcomp" the CPD? Jetcomp is a free tool from Microsoft to repair Access databases, which also cleans up a lot of mess and compacts it.

It works very well on database files which have grown a lot while working on them. The Access engine is not exactly known for generating clean, slim files.

After I tried my luck on a RSC database, thus reducing the file from 24 MB to 2.5 MB, I regularly treat all my MDBs and CPDs.

To use it on a CPD, you'll have to rename the CPD into a MDB.

Hope this helps.

---Dirk Bock

Thanks for the additional thoughts and suggestions. I'm still chipping away at this. Some additional factoids, in no particular order:
1. We're configured as NTFS, with default cluster size. (According to MS, that would be 4K.)
2. Going to RH6 didn't seem to affect the problem, although the writer reported that rebuild the cpd took twice as long (somewhere in the neighbourhood of 4 hours!).
3. Preliminary comparison of this project with a much smaller sibling project shows a striking difference in cpd growth pattern. When I add a topic to the smaller project, I see a cpd size increment that is *one tenth* that of the problem project! This is not precisely an "apples to apples" comparison, but it is definitely pertinent.
4. We do use build tags rather heavily, with names running from 4 characters to as much as 30. Most are about 10-15 characters. We also have long filenames, along with several layers of folder structure. Not much in the way of comments or to-do lists. There are lots of topic-to-topic links.
5. The writer tells me it's "been awhile" since she defragged, so that was a great suggestion. In fact, I think I'll do so myself!

As you can see, I have more checking to do on this. I appreciate the extra paths you've given me to explore.

G

I agree. Rick's examples show how FAT / NTFS is a major concern, as Peter says.

More random thoughts:

Have you checked your disk fragmentation lately and de-fragged if it was recommended?

Do you have a lot of file names that are extremely long? Do you make heavy use of the Properties dialog -- To Do list, Comments, etc.? If you use build tags heavily, do they have short names?

I'm not an expert on MS Access, but the data tables are fairly simple. Most are cross-references in two columns. Some are short lists.

The longest individual record is in the Topics table, which has columns for file name (twice) and title, build tags applied (topic or text level), plus flags and entries in the Properties dialog, such as priority, comments, to do list checkoffs, and so on. Multiplied by 5,000-plus records the table could need a lot of disk memory. And I don't know how efficient Access is when a table has 75,000 to 100,000 cells (even a "null" is a piece of data).

Compacting the database in MS Access should reduce any bloat that RH may have created in its own housekeeping.

But first priority should be to follow Peter's thought.

Harvey