I want to optimize my invoice database.

Right now, avg row length is 919.62 bytes and # of rows in a group is 2.65. The framesize is 1024. Total #of rows is over 500,000.

Will I get better performance if I increase the framesize to 4096 ? (using remaketable).

The other database has an avg row size of 225.25 and # of rows in a group of 8.27.

I read in the OpenInsight manual if the avg row size ] 50 bytes then use framesize 4096.

Rudhy

Rudhy,

This is what the OpenInsight online help says about frame size:

Specify the initial size of each frame, in bytes. By default, this is 1024. Sizes can range from 256 to 10,000 bytes (the system will automatically reduce any value specified to be greater than this). The system works very well with about 20 records per frame. For a general rule, multiply the average size of your records by 20 to achieve the optimum frame size for your environment.

I once asked Andrew at Sprezzatura for advice on frame size for my own OpenInsight tables. According to my notes he referred to the online help for recommended sizes.

I can't remember if he or anyone else have mentioned drawbacks with large sizes like 8 or 10 K.

In my earlier apps I never cared about frame size. In the one I'm developing now I'll take frame size into consideration and eventually remake my tables with optimal frame sizes. I think.

- Oystein -

Hmm interesting - we've always considered the article "Large database performance" by Mike Pope in the Sept/Oct 1988 issues of "Revelations" magazine to be the definitive document on this and we quote

"Anderson summarizes the study by recommending that developers atay away from the extremes. For most applications the default 1K frame size is ideal; large records work well in frames up to 6K or 8K"

The Sprezzatura Group

World Leaders in all things RevSoft

Could it have been Carl? Or after a few beers?

- Oystein -

Few beers seems more likely…

The Sprezzatura Group

World Leaders in all things RevSoft

- )

To elaborate further on framesize, there are many issues to take into consideration when contemplating changing a table frame size.

The idea behind changing frame size is to eliminate overflow. In order to do this correctly, the frame can never be smaller than the largest record in the file. Since you never really want the frame to be larger than 8K, this is sometimes impossible.

The next item of importance is minimising the number of records in a group. A group is a collection of frames containing the primary frame and it's overflow.

The system knows what frame the next record starts in, even if it is not the next frame in overflow. For example, consider a file with one group, 10 frames, and 3 records. Record 1 extends from frame 1 to frame 4, record 2 from frame 4 to frame 5 and record 3 from frame 5 through frame 10.

When you attempt to read record 3, the system starts at the first frame and the first record. When it determines this is not the correct record, it jumps straight to record frame 4 to check there. This is the SKIP value you can see from the DUMP window.

The largest speed hit in the system is not colating a record from a large number of frames, but from attempting to find the record when there are many records in a frame.

The hashing algorith is recursive, which is the reason for the group modulo value. Without going into to much technical detail, when the filing system resizes the file, it only moves half the records from one or two groups into the new group. This means that some records will hash to the same place they did before the action and others will not. In order to find a record, the system must continue to rehash the value until it reaches a point where it cannot rehash to find a new group.

The trick to speed is actually maintaining a minimum amount of records per group with the maximum amount of available space to hash these records into. This limits the possibility of rehashing and keeps these checks to a minimum. The way to achieve this is by manipulating the threshold value.

Another item to take into consideration is a bug flaw in the frame cache logic. The system tries to minimise disk access by caching as much of the group as possible into the frame cache buffer (there are 9 buffers available). When frame sizes change, the system needs to increase the size of the buffer so it can hold the each frame in its own buffer. The problem is, the system never decreases the buffer size.

This means that after reading from a 4K frame size, the buffer will always be 4K. When you attempt to read a 1K frame, it fills each buffer using the full 4K, so that means 4 frames per buffer. However, if the item cannot be found in the first frame, the system goes to the second buffer to attempt to read the second frame, the third buffer to read the thrid frame and so forth. In reality, these buffers contain frames 5 and 9. The system sees that there is a problem and before reporting back a group format error, re-reads the requested information starting with the approritate frame. Once again the buffers contains mismatched items and the process starts all over again.

As you can see, this can greatly slow down the system. Effectively what this means is that all files must have the same frame size. This will cause problems with the VOC file, POPUPS, MESSAGES and any other file containing small records.

This little buffer problem has been resolved with the NLM and NT Service, so we have been informed.

The Sprezzatura Group

World Leaders in all things RevSoft

Aaron .. nice job. I once remember making a new table and allowing it to set itself to 0. Then imported about 25,000 2k records into it. Initially, the whole thing was in 1 frame with huge overflow. Resizing it took FOREVER (I think it was the old bugaboo of sequentially numbered numeric keys if I recall correctly). Anyway, I rarely use a frame size above 4k and then only if I have to since the memory penalty hit (particularly in AREV 2.x) is enormous.

Anyway .. keep on truckin'

Don Miller

C3 Inc.

BTW

Besides now being confused as to whether to look at frame sizes or not…How do you change the size under OI.

Barry

When creating the file there is a "Table Attribute" button :-

pressing this allows you to set the framesize.

The Sprezzatura Group

World Leaders in all things RevSoft

Sorry, I was meaning changing an existing file.

Barry

Manually

The Sprezzatura Group

World Leaders in all things RevSoft

I remember creating a benchmark to test the relative efficiency of various frame sizes and being surprised that larger frame sizes, while they sometimes resulted in smaller files, did not have a significant net performance gain.

One other thing not previously mentioned in this thread but that could be relevant is what your network packet size is.

Hey Vector Angle ..

Good to see you again.

Don M.

Don,

One the subject of making a twist on names: For some reason I've started reading your company name as See-Thru Ink.

- Oystein -

Good point. I always forget about that little item when talking about frame sizes.

It's not as important with the NLM/NT Service as it is without. On the standard drivers your framesize should never be larger than the packetsize minus the protocol header, which means around 1512 for ethernet.

With the network services, you don't really have this issue as the full frames are not being sent across the wire.

The Sprezzatura Group

World Leaders in all things RevSoft

Oystein ..

It's almost as neat as Write-Only memory. By the way, I saw a quote from a lawyer / witness with the following dialog:

Q. You say you can't remember what happened on .. date. Is that true?

A. Yes

Q. Can you give me other examples of things you can't remember?

Don M.

View this thread on the forum...