Re: Index Files

[Ian Mills <Ian.Mills@ORIX.COM.AU>]

Wayne,

I have read you request and all the responses.

There is 2 ways we have been able to improve the performance of our
reports.

The first and most effective way was with indexes. On a number of
reports that were required, if an index didn't already exist on the DB
that suited our extract, then we created the index to match the report.
Another issue we had with indexes and SQR was that somehow the DB
optimiser didn't always choose the most efficient index. We had to fix
this by adding index hints to all of the queries in all of our reports.


Another way that improved performance slightly was to make use of the
-B flag on the select statements. This lets you choose how many rows are
extracted from the DB in each block. This is similar to the suggestion
of using an array to store the data, without having to set up an array
or worry about memory issues. The default is only 10 or 20 rows.


What I have found with all my efficiency improvements is that it
requires a bit of trial and error. For example, I modified a could of
reports to run as fast as possible. The down side was that the other
users on the system always complained that the system was running slow
whenever these reports were running. When I tried the -B option, I found
that on our network, that 200-500 rows was the most efficient.


Our best efficiency improvement came when we moved from Sybase to MS
SQL Server.


Hope I have hlped in some way.

Regards,
Ian Mills
Systems Analyst
ORIX Australia Pty Ltd



>>> wfindley@JPSCO.COM 08/06/2002 8:40:51 am >>>
Hi Everyone,

If I create indexes to match every select statement in my SQR would the
SQR
run faster seeing that I'm working with mammoth tables?

How would the SQR know to use the index?

Anxious to get process moving faster.


Help!

Wayne