Re: Buffer size, cursors, paging and Pro*C performance?

SELECT INITCAP(name_last),

       sum(cold_starts_saved)/count(unique(log_date)), sum(distance_saved)*:distconv/count(unique(log_date)),

       sum(:SN*cold_starts_saved+:SD*distance_saved+:SW*weather_bonuses) etc...

This script has to DO A LOT OF WORK to find the counts before doing the sums.

A better test would be to run your complete query against the database from SQL*Plus and see what happens .. possibly even use the EXPLAIN PLAN  facility to show how its being optimised.

You do have indexes on this table don't you.

By 'shortening your select' and removing the counts you are removing a lot of actual database work so of course it will run faster.

What you can do is:

1 Open a cursor that returns the range of log_dates required and the count for each one.

2 Fetch a log_date and its count

3 Open a cursor that uses the log_date and its count from step 2 to pull out the set of information

4 store the results from 3 somewhere safe; preferably in a database table so you can further sort or group it in step 6.

5 if more log_dates to do then go to step 2

6 combine all results from step 4

Or you can live with the overhead of a complex query and have a simpler program.

Bruce... Received on Wed Jan 17 1996 - 00:00:00 CET