I'm writing a program that uses a Get Next Extended to pre-load position
information about records in a fairly-large database by loading x number
of records at a time. This works pretty well performance-wise, except
that I only want to retrieve unique keys instead of every record. The
filters on Get Next Extended can filter on static values or on values
within the record, but I don't see a way to be able to filter on
something like "greater than previous record retrieved" or something
like that. The alternative of looping through the keys one at a time
seems like it would be too slow.

Can anybody think of a way to achieve something like this?

Do you actually need the entire record, or can you get away with just
the Key Value? The usual way to implement what you are trying is to
use GetGreater calls repeated as frequently as needed. A GetGreater
with a GetKey Bias (+50) would give you just the key values, but
probably won't save you much in the way of round trip requests (though
it will save on the data transfer).

I cannot think of any way to do a "GG" on the GetNextExtended, though.
By its very nature, it is a GetNext function. If your level of
duplication is low, then perhaps you can do a regular GNE and filter
out the extra records. If you get TOO many duplicates in a row, you
can then do a GetGreater to skip the rest of the duplicates. The
code'll be ugly, to be sure...

Might be better off doing a simply SQL query:
SELECT DISTINCT(...) FROM ...
But you won't get Btrieve record positions from that type of query.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***


Jason Gilbert wrote:

Thanks for the reply. It was quite informative. Maybe if I provide a
little more detail about what this program needs to do, you might be
able to help me a little more with some ideas, since you obviously know
what you're talking about.

Our software deals with medical records, and we have some customers with
upwards of 500,000 records. The program I'm writing is designed to give
our customers an easier way to browse through those records by providing
a string-grid view of the database that allows them to sort by various
columns of data and to also filter on the columns. To do this, my idea
was to use the GNE function to load the position info in the background
(only retrieving one byte of each record), and then when the user
scrolls, use that stored info to jump to an appropriate position. Since
GNE also offers filtering abilities, it seemed to be an ideal solution,
since I could use the same technique to pre-load my scrollbar info with
only the info needed for records that match the filters. Retaining the
ability to filter on multiple items would probably make the GetKey.

The hitch comes with another feature that's needed with this program;
the ability to group records by patient, so that the string grid doesn't
contain dozens of entries for the same patient. The idea is to just
display one line for each patient, and then have a little plus button or
something to expand the view to show all records for that patient. This
means that the duplication rate would be relatively high.

Up until this point, we've never used the SQL capabilities of Pervasive,
but perhaps this will finally force us into it, but I'm not sure how
much that will buy us either.

Thanks again for any help you can provide,
Jason Gilbert
Comp Pro Med, Inc.

Bill Bach wrote:

A few random thoughts:

1) The SQL interface will give you an easier way to sort the data by
ANY column, and JUST get back the data fields you want to see. This
usually slows things down (and users should be cautioned that if they
sort by a non-indexed field, it will take a lot of time, but you can
use this to make the UI a LOT more flexible. Just be aware that
performance will NOT be what you are used to, due to the SQL statement
overhead. Keeping the query simple (i.e. a single table when possible)
is the best idea.

2) Why do you retrieve one byte of each record? I believe it is ALSO
possible to retrieve 0 Bytes from each record, which my be more
efficient. Alternatively, you can retrieve the primary key or some
other identifying information for access.

3) Another possibility is to use the Percentage operations, which were
intended for scroll-bar functionality. Newer database engines have a
"high-precision" version of these operations, which actually may be
helpful in your application.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***


Jason Gilbert wrote:

Thanks again for your input, and sorry for not responding sooner; I've
been sidetracked by various other things. Just a couple quick questions...

1. Do you know of any good resources for the ins & outs of accessing a
btrieve database through SQL to supplement Pervasive's documentation?

2. You mentioned some database engines offering "high-precision"
scrollbar functionality. What are some examples that you know of that
offer this? I'm afraid that this may be the only viable option.

Jason Gilbert
Comp Pro Med, Inc.

Bill Bach wrote:

1) The only real "extra" stuff is pertaining to SQL query optimization.
The easy way to think about this is that each SQL query must degenerate
to simple Btrieve commands -- reading data on a key path. If an index
(or key) does not exist, the query will likely be slower than it
should. Therefore, always be careful when you join tables, restrict
data (with WHERE) and sort (with ORDER BY).

2) Btrieve has had, for some time, GetByPercentage and FindPercentage
operations, allowing you to implement scrollbars easily. The default
range (granularity) is from 0 to 10000, or from 0.00% through the file
(i.e. the first record) to 100.00% through the file (the last record).
As the number of records increases above 10000, this becomes a bit less
accurate. PSQLv9 introduced the concept of setting the granularity of
the operation, greatly increasing the flexibility of this feature. See
the PSQLv9 SDK manuals (specifically the GetByPercentage operation) for
more information.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***

Jason Gilbert wrote:

Hello,

You could add a boolean (Duplicate) to the record. When inserting a record,
you could seek for the key. If it exists already, set the bool to 'true',
otherwise false. In the filter for the GNE, you can then filter on the
boolean to be false.

Serge

"Jason Gilbert" <dev (AT) comppromed (DOT) com> wrote