Printed Icetips Article

We Clarion programmers have "learned" that page loaded browses are the best. That used to be the case, but SQL has changed the considerations.

In the demo app at http://www.icetips.com/downloadfile.php?FileID=59 (for C5.5 and 6.0), all the browses there are file loaded, in the sense that once the user specifies his criteria, all the records meeting it are retrieved and displayed.

Advantages of page loaded SQL browses:

1. Small result sets.

2. Faster data retrieval (for one page).

Disadvantages of page loaded SQL browses:

1. Locating records is more difficult for the user.

2. Simple scrolling becomes cumbersome and slow for the user.

3. More hits on the server.

Advantages of file loaded SQL browses:

1. The user gets all desired matching records requested at one time.

2. Scrolling is very fast, with either the keyboard or mouse.

3. The scroll bar works as users expect.

4. Resorting the browse by clicking on a list header becomes very powerful, fast and simple, with no need to reload the data with a new hit on the server. (See the ViewBattingAverages procedure in the demo app. This is easier than many Clarion programmers think it is.)

5. The browse doesn't have to be refreshed every time the user adds, inserts, or deletes a record.

Disadvantages of file loaded SQL browses:

1. For very large result sets, the data could take a while to load, especially with a slow connection.

2. For very large result sets, the workstation could start using the disk for virtual memory, losing the speed of the single result set.

The decision whether to use page loading or file loading must start first with a knowledge of what the data set is likely to be like.

Is the browse a lookup of US states? If so, file loaded wins easily.

Is the browse on all the transactions a company has ever had, with no filter? That's probably a stupid browse to begin with, and should be rethought.

If the result set is POTENTIALLY large, would intelligent use of locator fields for the user be better than page loading and making the user wade through a mass of irrelevant data?

You might think a combination of locator fields and page loaded would be the best solution. Maybe not, though. Depending on what the indexes are in the database, the locator fields might cause a full table scan at the server anyway, and introducing paging to that would make for a bigger load on the server, not a smaller one, because each page would cause a full scan.

For large tables, you can force the user to enter some locator value that would create a smaller result set. You probably wouldn't have to "force" the users to accept this, because for very large tables that probably exactly what they would want to be able to do anyway.

There is no one correct answer to this question. But unless you MUST for some reason have page loaded browses with SQL, I suggest you always start with the notion that your new browse is going to be file loaded, and work from there.

This is one of the things that does require some new thinking, and overcoming some resistance from your own instincts, because it goes counter to what we Clarion programmers have done and learned for years. But your users really would like file loaded browses--trust me.