Re: Equivalent of Get Next Extended to retrieve only unique keys?

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@xxxxxxxxxxxxxxxxxxxx
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:
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@xxxxxxxxxxxxxxxxxxxx
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:
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@xxxxxxxxxxxxxxxxxxxx
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***


.