For a past 1 year a'm trying to increase our Pervasive database (6.x
datafile and 2000 or V8 engine) perfomance. The work has 3 test objective:
1) With modification we can made to turn to SQL interface from present
Transactional API.
2) How this affect on overal application perfomance
3) With a key features of 2000 and V8 may affect to perfomance and must be
include to the ours system.

Ours system is a like ERP, developed with MS VS 6 on C++. Data store in
Btrieve 6.15 datafile with originality principle - for each month - creates
new datafiles in new folder. This provide linear perfomance for any volume
historic data. The system hase more than 500 type of datafile and all data
store as fixed record length between 500 and 4K bÕtes, and all have 4k page
size. Tested table have up to 100K records. Currently use only Btrive API.

Short resume:
1) Pervasive SQL interface does not provide more perfomance in comparison
with traditional API Find Extended. At the same time, SQL has more risk to
operation - if a table does not have property index - any SQL query can
hang system for a long, long time, without any process information (for
example - if process with FindExtended we can inform user for state of
process)
2) Transfer data to 7.x format provide up to 20% perfomance
3) Include system data does not provide visible feel perfomance, but it only
to one table based Select query
4) Data compression can provide up to 200% perfomance, because data feches
quickly from device
5) Perfomance have linearly dependents to record length.
6) If SQL engine does not keep exactly proper index for query, the execute
time will increse critical to operate system at all.
7) SQL Perfomance to satisfy me only in one case - then whole table cached
in memory !
8) Excecute DDL query unsatisfactorily at all. Simpy add column to table
force SQL engine to create new table and copy all record from source and
down system for many hour.

I suggested to Pervasive:
1) IN FIRST - you can increase speed of retrival records from OS layer. For
example - MS SQL server, for exactly equal tables and hardware, can retrival
up to 2K rows per second, but Pervasive only 200-300 rows per second (3k
length).
2) Change datafile format for normal operate DDL and work without
datadictionary - because not present real ability to compare datafile and
dictionary. You have error in this case, but not answer where error - in
datafile or dictionary ?

Perfomance of SQL Server and Pervasive ? - it CAN'T be compare, with
one except - when all Pervasive data cashed in memory or app operate over
data with limited querys - and for it datafile have optimum indices..
In other case, Pervasive is to much slow than MS SQL w/o any indices..


With regards,
Alex

Thanks Leonard

I'm did research all technical materials from Pervasive and other.
Pervasives "Query Plan Viewer" too simple and don't realy help on OLTP
database.
Sorry, I don't now how to increase IO perfomance, because I'm desision-maker
person, not proffesional programmer, but it's fact about Pervasive IO :-(

It is not Pervasive documented, with you advice to ?:
1) Concept to increase retrival row speed of transactional API, from
200-300 row/sec to ?, independently of hardware. For example convert half
fixed record to variable ?, or split tables ?
2) If we realy have designed the database and access them throw Btrieve API
, how to check row image with estimated data structure, if row length does
not change ?. How to versioning Btrieve databases ?
3) If in requirements said that "User can sort grid by All available
column", that it implement on database schema ? - create indexes for all
column ?, and for each column order ?. In SQL server it may be optimize
later, if needed, but on Pervasive each of such query potentially shutdown
system for the 1, or 10 min on small table (about 100 k recs) while whole
table does not cashed in memory. Plus, real query-s have various WHERE
clause and other
4) If datatable have only fixed record length , can we use safety use the
data compression for perfomance purpose ? (overhead CPU costs
insignificant, because is normal, then server operate with 4% CPU load)

Cheers
--
Alex D. Pichurov

"LHarvey" <lharvey (AT) austin (DOT) rr.com> wrote

On Wed, 20 Aug 2003 16:42:43 +0300, "Alex D. Pichkurov"
<alex (AT) mpc (DOT) odessa.ua> wrote:

The size of a file has less to do with Pervasive database performance
than the size of the row. This is especially true if there is a
network hop in the middle.

Grids are dependent on the database schema for implementation.
Some "grid" components are smarter than others about retrieving data.
Some grids suck down all the data from the database needed or not.
This is usually not a good way to write an application larger than an
office pool.
As far as getting any column sorted that is a function of the SQL
engine, it will use a temporary table if needed (no index available).

Data compression is safe. The other thought I have on this is, if
data compression is that effective is there actually data in the file?
Real unique data is non-compressible by nature. If the 4000 byte
record has 200 bytes of data and the rest is blanks it may be time to
take a second look at the overall database design. That would
actually be a good candidate for a simple variable length record file.

Leonard

Thank Leonard, can you tell me how:
1.You write:
We used custom MFC grid (itself develop), with show ony needed portion of
data and planing to develop ATL classes over OleDb, like "Express OLEDB
Library", for implemented Pervasive SQL Engine.

2.
May be it's simple, but I don't understand how to combine in one datafile
record fixed and variable record's portion. In Pervasive SDK I don't find
any help how to create such file, but many info how to use it (except then
ext. data type is BLOB or CLOB).
For example one of ours file DDL (see atachment ddl1.sql, the DDL from SQL
DataTransormation service, because Pervasive Control Center does not provide
it :-(
In general question - how to increase rows retrival speed from this table
?.

With regards,
Alex

--
Alex D. Pichurov
"LHarvey" <lharvey (AT) austin (DOT) rr.com> wrote

Leonard, please tell me - how I can convert my file to a simple variable
length record file ? - all VARCHAR column convert to CLOB type ?
So, what wrong with my datafile ? - Typical OLTP databases have larged
record length then DataWarehouse DB.

Already do convet datatype with Pervasive Table Degisner, and now, for any
query, receive the follow error.
ODBC Error: SQLSTATE = S1000, Native error code = -4896
The chunk offset is too big(Btrieve Error 103)

--
Alex D. Pichurov

"LHarvey" <lharvey (AT) austin (DOT) rr.com> wrote

"Alex D. Pichkurov" wrote:

IDE7200? There's your problem! A high-performance database server should
really be Ultra-Wide SCSI with a RAID1+0 array. Disk performance will ALWAYS be
a limiting factor in temp tables.

Oh,
You are quite right, this is my workstation, on server we have Ultra-Wide
SCSI Baracuda 5 disks
But the question other - how to
1) designed table for support indicated requirements (4 sort fieds and 5
filtr fields)
2) speed up row retrival from OS ( OR how convert 3k fixedsize record to
simple variable size) and use throw relational and transactional interfaces?

Also, on same hardware (7200), MS SQL too much quick then V8, and the
situation does not change on huge server.

--
Alex D. Pichurov
"Bill Bach" <bbach (AT) cncdsl (DOT) com> wrote