home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Shareware 1 2 the Maxx
/
sw_1.zip
/
sw_1
/
TEXT
/
PDX_ALL.ZIP
/
TI527.ASC
< prev
next >
Wrap
Text File
|
1991-09-11
|
27KB
|
727 lines
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 1/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
Paradox provides an environment in which even a relatively
inexperienced database user can work quickly and productively.
Part of its power comes from the fact that it hides unnecessary
detail from the user. As Paradox is executing an interactive
command from the user, it may also be building tables that allow
major changes to be undone, updating the display or environment
as appropriate, managing memory, checking the integrity of
tables, or ensuring that consistency is maintained between a
table and its forms and reports. The extra services that Paradox
performs as a part of many operations allow the user to
concentrate more on the work to be done than on the steps needed
to get there. This makes Paradox easier to learn and use, but no
less powerful; in fact, Paradox's unobtrusive assistance makes it
more powerful for many users than competitive products.
Consider a menu selection that makes a given record number the
current record. Even for such a simple operation, Paradox does
more than it is asked to do: it also refreshes the display to
show the new current record (in context in its table, if the user
is in "table view"). This is a task that might require a second
command in another database product. The effect of anticipating
the user's likely next request and performing it automatically is
to maintain the continuity of the session, allowing the user to
accomplish more work with less effort.
When reviewing Paradox 3.0's performance, it is important to take
into account that a Paradox command may do more work and deliver
more benefits than are obvious at first. In this paper, we
provide a narrative on the many ways that Paradox provides extra
support behind the scenes, along with tips on specific ways to
measure performance without inadvertently including the extra
support as well. We have tried to organize the discussion by
functional areas, but some topics cut across several areas. In
particular, benchmark results for almost every operation are
affected by Paradox's virtual memory management, so we discuss
this topic first.
Memory Management________________________________________________
Paradox's virtual memory manager makes good use of as much memory
as is available, both to hold recently-used blocks of data in
memory and to store PAL procedures. Consequently, timing results
are sensitive to the amount of memory available, relative to the
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 2/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
size of the tables used. When tables are fairly small and there
is ample memory, many operations will find most or all of the
data they need already in memory, and will run quickly. Paradox
2.0 can also use expanded memory -- either LIM 3.2 EMS or EEMS --
and Paradox 3.0 adds support for LIM 4.0 EMS.
One of the benefits of Paradox's memory management is that the
second and later accesses to a record are usually much faster
than the first. This is an advantage in everyday use that may
not show up in benchmark tests, which often start from a clean
state or do sequential operations on large files. In general
use, many operations do access the same fairly small group of
records that previous operations have loaded into memory. Paradox
can be expected to perform well in these cases.
Especially in Paradox 386 and Paradox OS/2, you should tune both
the amount of memory taken and its distribution between storage
of recently-used blocks of data, and storage of PAL procedures
according to your application's needs. Because these products
offer address spaces far larger than DOS's one megabyte, the
amount of memory available to Paradox at start-up can vary
widely. This gives you more leeway: if you are running a very
complex application, you should favor procedure storage space; if
you are managing very large tables, you should favor space for
data blocks. Note that Paradox OS/2 cannot tell how much
physical memory is available, so it takes relatively little
memory by default, on the assumption that you will be running
several other sessions as well. Knowing how much memory is
available and how many sessions you are likely to run, you can
apportion memory for better performance.
Memory Management Benchmarking Tips______________________________
Virtual memory
Remember that tests that start with memory clear do not let
Paradox's memory management work to best advantage. Some
tests should reflect the fact that in everyday work, it is
common to repeatedly use the same small set of data.
EMS
Use expanded memory, preferably expanded memory following
the LIM 4.0 or AST EEMS spec., if possible.
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 3/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
Paradox 386 and Paradox OS/2
If using Paradox 386 or Paradox OS/2, match your command
line options to your environment and to the kind of work you
will be doing in Paradox.
Queries
Paradox's Query-by-Example facility provides many examples
of extra services that are provided without explicit request
from the user. Probably the most fundamental is its
handling of duplicate records. Queries often yield
duplicates, even when based on keyed tables, and usually you
do not want duplicates in the answer. Paradox was designed
with the idea that query results, in addition to being
correct, should be presented in an easy and usable format.
Accordingly, Paradox suppresses duplicate records in the
ANSWER table by default and, in the course of doing so,
provides another service: it sorts the ANSWER table. (You
can use CheckPlus instead of Check in the query statement to
omit the sort and retain duplicates, if desired.) In
Paradox 3.0, you can select the sort fields and choose
ascending or descending sort for each, making a subsequent
sort step unnecessary.
FIND queries also provide more support for the user than is
evident initially. When a FIND query is performed, the only
outward consequence is that the cursor is positioned to the
first record that satisfies the query. However, Paradox
also builds an ANSWER table containing all records that
match the selection criteria, making it easy to locate the
desired record even if it is not the first match. If you
know you do not want to see any record but the first match,
it is faster to use Image/Zoom/Record instead (and note that
here too Paradox positions to the desired record, so you can
see it in context).
One final example: Whenever a query changes a table,
Paradox keeps a log of the affected records in the CHANGED,
INSERTED, or DELETED table (depending on the type of query).
In the event of a mistake, the entire operation can quickly
be undone using this log table. As a result, it is safe to
make complex changes to a table without writing procedural
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 4/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
code and without exhaustive testing -- if you make the wrong
changes, you can always reverse them and redo the operation
correctly. Overall productivity is improved as a result.
Query Benchmarking Tips__________________________________________
Duplicates
If a query will not select duplicates -- for instance, if
the table's key field(s) are selected -- use CheckPlus to
prevent sorting.
Finding just one record
Use Image/Zoom/Record or the PAL LOCATE command to find a
single record in a table.
Controlling the ANSWER table's layout
Use query capabilities such as column rotation,
CheckDescending, and the AS operator, to give the ANSWER
table the desired layout. This can eliminate a subsequent
sort or restructure step.
Indexes
Although Paradox queries always give the same results
whether the queried tables are indexed or not, they
sometimes run much faster when appropriate indexes do exist.
We turn to this topic next.
Paradox offers a variety of indexing alternatives. First,
you may chose to define a primary key for a table (in which
case it is sorted and no two records may have the same key
values) or you may leave the table unkeyed (in which case it
is not ordered and duplicate records are allowed). Second,
you may create a secondary index for any field in a table,
whether it has a primary key or not. Finally, you may chose
to rebuild a table's secondary indexes whenever the table is
changed (for indexed tables only) or only when the table is
queried. No matter what kind of data a table contains or
how it is queried and updated, one of these alternatives
will be suitable.
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 5/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
A table that will be queried should generally have a primary
index, since this will speed up searches on key values.
Non-key fields that are often the basis of selection
criteria will benefit from having secondary indexes. You
will notice that it is not even necessary to tell Paradox
which fields to create secondary indexes for; just ask for
QuerySpeedup on the most common queries, and Paradox will
decide where indexes are most needed. This is a prime
example of Paradox's ability to increase rather than limit
users' choices by hiding detail. For most applications, it
is better to request maintained secondary indexes (these are
selected in the Custom Configuration Program, or by using
the MAINTAINED option of the INDEX command). There are
exceptions, however; for instance, a large table that is
only updated overnight and is queried heavily during the day
is a good candidate for non-maintained secondary indexes.
Index Benchmarking Tips__________________________________________
Building an index
As we will see below, Modify/Restructure can create a
primary index, but does too much additional work to give you
an accurate measure of indexing time. To see how quickly
Paradox builds an index, use PAL's INDEX command, which
builds a secondary index on a specified field.
Using an index
When you want to measure query performance using an index,
on the other hand, it is important that you do not
inadvertently include the time taken to rebuild the index.
You will want to use maintained indexes here.
Changing a Table's Structure
Another area in which Paradox provides more support than is
evident at first glance is Modify/Restructure. From the
user's point of view, Restructure allows changing a table's
primary key, adding, deleting, or reordering fields, or
changing field names and lengths. In the course of making
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 6/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
these changes, Restructure does three other important kinds
of work behind the scenes:
o Unless requested not to, Restructure builds a
PROBLEMS table containing any records in which data
would be lost or truncated as a result of changes
to field types or lengths. Like other temporary
tables, PROBLEMS is presented to the user in sorted
order.
o It also removes references to deleted fields from
forms, reports, image settings, and validity
checks. Users have as much flexibility as possible
to change existing tables without losing the forms
and reports they have defined for them, yet are
guaranteed that these forms and reports are
consistent with the new table structure. [expand:
users have object-oriented view -- field either
exists or it doesn't -- and this gives flexibility,
integrity, and simplicity]
o Finally, Restructure always rebuilds a table
completely: it brings records back into physical
sequence, consolidates partly-full blocks of data,
returns the space freed by this rebuilding to DOS,
and rebuilds primary and secondary indexes. Clearly
it is not appropriate to compare Paradox's time to
change a field's type, for instance, to another
product's time for the same change, unless the
other product also provides the same integrity-
checking and table-rebuilding capabilities.
Restructure Benchmarking Tips____________________________________
Changing table structure
You cannot get a time for a simple table change (delete a
field, change a field's data type, add a primary index,
etc.) that does not include the additional work discussed
above. Since these operations are not done frequently once
a table contains data and is being used regularly, Paradox's
design favors thoroughness and data integrity over speedy
execution, in this case.
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 7/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
Compacting a table
You can force Paradox to rebuild a table as described above
by entering the Restructure subsystem and pressing Do_It!
without making any changes. If the table contains many
deleted records, this will compact it and, thus, speed up
operations that use it. Many Paradox users regularly
restructure their tables for just this reason, and it may be
appropriate to do so when benchmarking as well.
Building an index
As we have seen, the PAL command INDEX, which builds or
rebuilds a secondary index on the field you select, gives
the best measure of indexing time.
Renaming fields in ANSWER
Use the AS operator in a query to rename fields in the
ANSWER table. This is especially useful for CALC queries or
when two tables in the query have fields with the same name.
Import/Export and Reporting
Sometimes Paradox provides several ways to perform the same
general operation, each with its own options and advantages.
Which alternative you choose will depend on your specific
circumstances and objectives. When optimum performance is
the objective, it is especially important to select the
alternative that best satisfies your needs. We will look at
two examples, one that involves inputting data into Paradox,
and one that involves report output.
Paradox imports data in more formats than most other
database products, and can in every case determine the
structure of the target table without the user's
involvement. When importing data in a well-defined format,
such as DIF, this is straightforward. For other formats,
such as 1-2-3 spreadsheets and comma-delimited ASCII files,
Paradox must pass through the file to be imported twice,
once to select data types and once to import the data. This
is slow, but gives the best possible support for interactive
use. In an application, if the format of the incoming data
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 8/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
is known and the data is clean, you can instead use
Ascii/Import/AppendDelimited. In this case, you provide the
target table, and Paradox only makes one pass through the
data, building a PROBLEMS table if any input records do not
have the necessary structure.
Users often want to borrow reports from an existing table
for a newly-created table, most often to associate an
already-defined report with a new ANSWER table. In Paradox
2.0, Copy/JustFamily is usually the best tool to associate
forms or reports with a table; to empty the target table and
add records into it is slower. In Paradox 3.0, an
individual form or report may be copied from one table to
another.
Import/Export and Report Benchmarking Tips_______________________
Joining tables
In Paradox 3.0, you can use external tables in a report
specification to satisfy most requests for reports that draw
data from several tables. Only the most complex reports
require a query step to assemble the data before reporting.
Sorting
Grouping in a report implies sorting; there is no need to
sort a table before reporting on it.
Copying reports to an ANSWER table
In general, copying a single report from one table to
another is the fastest way to use an existing report with a
new ANSWER table.
Ascii Import
For fast import into an ASCII table, define the target table
ahead of time and use the AppendDelimited option to fill it.
PAL
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 9/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
A program in Paradox's programming language, PAL, can be as
simple as a small macro or a few recorded keystrokes. At
the other extreme, PAL provides many features of
contemporary programming languages, such as arrays, DO and
FOR statements, procedures with arguments and private
variables, and many functions for testing system status.
Using these features, developers can write complex and
sophisticated Paradox applications.
It is possible to write a PAL application without using
procedures. However, programs that use procedures, beside
being cleaner and easier to debug, usually run faster. Once
an application is completed, it is generally desirable to
store its procedures in procedure libraries. Procedures
from libraries usually run faster than those in scripts (as
PAL programs are often called), especially in large
applications, because libraries allow procedure swapping.
PAL Benchmarking Tips____________________________________________
Procedures
Keep PAL procedures fairly small, store them in procedure
libraries and use procedure calls that allow swapping, and
let Paradox manage loading and releasing of procedures.
Network Topics
Paradox's support for local-area networks is a natural
extension of its single-user feature set. One of its main
objectives is to allow concurrent operation wherever
possible. Many users can read a table -- for the purpose of
viewing it, querying it, reporting from it, or graphing it
-- simultaneously. Although only one user can edit a table
at a time, a second update mode, CoEdit, is provided to
allow several users to change a table's contents
concurrently. In CoEdit, users lock only the records they
are changing; other users are prevented from trying to
change a record as long as it is locked, but may lock and
change any other records. Still, other users may view,
query, or report from the table while it is being coedited.
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 10/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
Paradox never allows concurrent access that would put the
integrity of the data at risk, however. To prevent such
access, it places locks on resources -- tables, forms,
reports, and other Paradox objects -- that are in use. Other
users may or may not be allowed to share these resources,
depending on what type of lock is in force and what type of
locks they require for the operations they want to perform.
Here again, we see how unobtrusively Paradox works to allow
you to concentrate on the work to be done and not on the
mechanics of getting it done. While you continue to work as
you would on a single-user installation, Paradox ensures
that needed resources are available and locks them to
prevent inappropriate concurrent use by others. You never
have to inquire if a table or record is locked before you
try to use it, and you are never penalized if you fail to
check what other users are doing before you begin your work.
As long as there is no conflict, you do not need to pay
attention to what other users on the network are doing --
and as soon as there is a conflict, Paradox tells you.
Moreover, Paradox does not just tell you that a resource is
unavailable; it also tells who is using the resource you
need.
You can, however, ask Paradox to keep you posted on changes
other users are making to the tables you are viewing or
using. By adjusting the AutoRefresh interval, you can have
their work reflected on your screen almost instantly, so you
will always know as you begin updating a record that the
values you are looking at are completely up-to-date. Or you
can turn off AutoRefresh. In either case, Paradox informs
you of changes you need to know about -- for example, if you
attempt to lock a record when your display of it is out-of-
date, Paradox will update your display before it lets you
begin entering your changes.
Network Benchmarking Tips________________________________________
Locking
In addition to the implicit locking discussed above, it is
also possible to lock tables explicitly through PAL. In an
PRODUCT : PARADOX NUMBER : 527
VERSION : ALL
OS : DOS
DATE : September 11, 1991 PAGE : 11/11
TITLE : Performance Notes for Paradox 3.0 Reviewers
application, it is better to lock explicitly and test that
the lock succeeded.
Using CoEdit
CoEdit in a shared directory gives maximum concurrency, but
the additional overhead of record-locking inevitably causes
some performance degradation. Edit gives better
performance. CoEdit with exclusive use of a directory can
be faster than Edit, because it can omit both the record-
locking and the transaction log that Edit maintains.
Improving network performance
Our default time of three seconds for AutoRefresh assumes a
fairly powerful network, and gives up a fraction of its
resources in order to keep all users up-to-date. For a
heavily-loaded network, you may want to set the AutoRefresh
interval higher, to work in forms view instead of table
view, or to use DataEntry instead of CoEdit. Any of these
will decrease network traffic.