Hi

This is a development question, but anyone with experience in these areas I
will gratefully receive your comments.

We have been using Btrieve for many years for our product since the 6.15
days. Its basically an ISAM database. We have made a new version of our
software to run using MSSQL accessing it via OLE DB and ADO. Now our goal
is to use the same program software for multiple databases, eg PostgrSQL,
PSQL, Oracle, MySQL etc, so that we can meet many markets. Currently we
don't use Transactions in MSSQL or Btrieve. We also use our own passive
concurrency locking method.

Our current task is to make our new programs work under PSQL using the
relational engine. We have been having numerous problems with our approach
on this and I am not yet convinced that its the fault of Pervasive. So I
have a number of questions.

1) Is OLE DB and ADO the best way to go regarding performance, i.e. would
ODBC be better and if so, why.
2) We program with C++ but dependant on the method of access, would anyone
recommend .NET or Java or anything else.
3) Our product is built in two levels, the main program controls all GUI,
our report writer etc. This makes calls to our Data Engine which retrieves
the data from the database using ADO to translate to SQL. (Forgive me for
the rubbish description, I'm systems not programming) Is it possible to make
SQL calls via our data engine and have them interpreted to Btrieve calls to
make it quicker. So far, our attempts at PSQL result in a 400% slowdown
compared to MSSQL and Btrieve.

4) Does anyone think that there is mileage in using the ODBC bridge so that
the database appears in PCC and can be accessed by crystal etc, but in our
product, having a separate Data engine that just does core Btrieve access?
Defeats the object of cutting down on programming but may be our last ditch
attempt.

I am keen to keep on with Pervasive, especially as we have just upgraded 250
users from PSQL 200i to 8.6 (Mostly Windows server, but some Netware and
Linux) and thus don't want to ditch it, but it seems like PSQL is a dog, or
is it just our approach is wrong, comments please.

On my investigation, it seems that V9.x has better OLE DB / PSQL access, but
there seem to be some Access / stability problems or our test data. Can
anyone relay how they access their Btrieve data relationally.

As a side issue, we don't want to get into triggers, and stored procedures
is just a pipe dream at the moment. All I want is something that works and
is gonna be not that much slower than the Transactional access method!

Regards
Rowland Costin

Actually, THAT sounds like the pipe dream. ;-)


When you move from a navigational database (like Btrieve, C-Tree, etc.)
to a relational database (like SQL), you MUST re-think, and in some
cases, completely redesign, your application. SQL cannot effectively
use the record-by-record approach that navigational applications use
and still retain good performance -- there is simply too much overhead
in the SQL interface. Instead, you should redesign the app to deal
with "sets of data" instead of "records", to really have a good port.


Having said that first, I can give you a few places to start.

A) As you probably know, PSQLv9.1 has much better syntax options, and
will likely run faster that PSQLV8. I would urge you to spend some
time on the stability issues that you have seen and resolve them first.
(Most of our customers have seen no problem with PSQLv9.1 to date,
although a few have complained of problems on large files.)

B) Pervaisve SQL performance is directly related to the quality of the
DDF's. If the DDF's match the Btrieve files, you'll be in good shape.
If they don't, you'll have REALLY bad performance. Use the Check
Database Wizard to validate the DDF's against the files.

C) The PSQL engine is directly related to Btrieve. As such, you need
to carefully examine your SQL code, and say to yourself "how would I
turn this call into a Btrieve request". Examine the SQL definition
from the PCC. If you have a WHERE clause such as "WHERE Field1 = 6",
then you MUST make sure that Field1 is an indexed field for best
performance. Otherwise, the system resorts to a very slow table-scan.
Also, watch your segmented keys. If you have a clause like "WHERE City
= 'Chicago'" and your index is on "State, City", then a table-scan is
needed. However, simply changing the query to "WHERE State = 'IL' AND
City = 'Chicago'" will make the result set come back immediately.

D) Use server-side cursors where you can for best results. Client-side
cursors must transfer the entire data set before returning information.

I'll leave the other questions to others here...
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - November, 2005 ***
*** Chicago: Pervasive DataExchange Class - November, 2005 ***


Eclipse wrote: