[RBASE-L] - Re: View question - Razzak's Reply

[A. Razzak Memon]

At 10:43 AM 8/7/2003 -0500, you wrote:

I would like to inquire about a performance question
regarding views.  I have view that drives a report and
the performance is not very good.
I believe all linking columns are indexed properly etc.
T1 and T2 have indexed InvoiceNumber columns.
T2 and T3 have indexed ItemCode columns
The question is "does the table order matter"?

The view has 3 tables and I have them in this order:

InvoiceHeader   ~10,000 rows    T1
InvoiceDetail   ~100,000 rows   T2
SalesCategory   ~300 rows.      T3


Bob,

As you know, VIEW is simply a stored SELECT command.
No data is stored with a view; when a view is used,
the data is dynamically generated so it is always
current.
What is the relationship between InvoiceHeader,
InvoiceDetail and SalesCategory?
Have you checked the redundancy?

What do you get when SELECT COUNT(*) FROM ViewName ?

The relationship between each table is very important.

There are three basic relationships between tables in
a relational database:
One-To-One:
There is only one row in each table where the linking
column(s) contain matching data. The linking column
in each table is unique. When two tables with a one-
to-one relationship are joined, the result is a "one"
table.
One-To-Many:
In one of the tables, the linking column is unique,
there is only one row for each value. In the other
table, there can be one row or many rows for the
linking column. When the tables are joined, the
result is a "many" table. The number of rows returned
is the number of rows from the "many" table.
Many-to-Many:
In both tables, there can be many rows for the linking
column(s). The linking column value is not unique in
either table. The result of a join between two "many"
tables is a "many" table. The number of rows returned
is the number of rows in one table multiplied by the
number of rows in the other table.
After verifying the relationship, if everything still
looks good, then, have you tried to PROJECT that VIEW
as TEMPORARY TABLE and base your report on that TEMP
Table?
Example:

View Name:              ViewA
Related Report: ReportA
01.     PROJECT TEMPORARY tTableA FROM ViewA USING ALL

02.     Database Explorer > Reports > Highlight "ReportA"
        Right Click > Copy Report
        Report Name:     tReportA
        Table/View Name: tTableA
Now you have the exact report based on a TEMPorary Table
with exact same data from ViewA.
PRINT tReportA ...

Any difference in performance?

If that works, you can automate the entire routine in
a command file.
Notes:

01.     Remember, any TEMPORARY Tables and/or VIEWS are
        DROPped when you DISConnect the database.
02.     Before Designing/Running that report, you MUST
        create referenced TEMPorary Table(s)/View(s).
03.     TEMPorary Table(s)/View(s) are specific to each
        session of R:BASE and that specific user.
Hope that helps!

Very Best R:egards,

Razzak.