Re: Group and Count Performance Issue challenge

On Feb 13, 7:56 am, glama..._at_hotmail.com wrote:
> I have been challenged to optimize a stored procedure on Oracle 9i
> that will return a ref cursor with the counts of each status for each
> batch from the tables defined below. I started with a View to join
> the tables together on the OrderNo field. Then I wrote a query in the
> stored procdure that grouped the Batch values together, and then did a
> count for each Status value in each Batch like this:
>
> SELECT Batch,
> COUNT(Batch) Total,
> (SELECT COUNT(Batch)
> FROM MYVIEW
> WHERE Status = 1
> AND Batch = V1.Batch
> (SELECT COUNT(Batch)
> FROM MYVIEW
> WHERE Status = 2
> AND Batch = V1.Batch
> (SELECT COUNT(Batch)
> FROM MYVIEW
> WHERE Status = 3
> AND Batch = V1.Batch
> FROM MYVIEW V1
> WHERE Status < 4
> GROUP BY Batch;
>
> With 42 unique Batch values and 26,000 OrderNo values my dev server
> takes over 5 seconds. I built the query without the view and added
> the joins in the query and took the same amount of time. I can't
> change the table structures but the view is wide open. This is a
> conversion project from MS SQL and this same data returns from MS SQL
> in 0.09 seconds. I hope Oracle can beat MS

"Beat" is a relative term. Some things you might want to note:

Oracle is not MS SQL.

Timing done on one run may not reflect timing done on several runs. Part of the reason for this is Oracle is biased towards giving multiple
users their own views of the database, based on when transactions start relative to one another. Another part of the reason is Oracle has
algorythms to keep more-used data in memory. So while MS-SQL may "beat" Oracle counting a few hundred pieces of data on a PC off a SATA drive, the situation may be different when ten thousand users want the same few hundred pieces of data.

Oracle may be able to count the data from an index. Do you have an index? Would the index be smaller than the table? Would a substantial fraction of the data be null? Oracle can also "skip-scan" an index, which means ignore a leading field.

Oracle performance tuning uses explain plans, which tell how the optimizer is getting at the data. You want to understand the various ways to access the data and what influences the optimizer. The basics are explained in the performance tuning guide, and there are other resources available when this becomes deep. In general, in this usenet hierarchy any performance question is likely to be answered with a request to show plan (from a built-in procedure) or trace (for serious tuning) output. You should run this for your code and Charles', the results may be informative.

The biggest dependency for the optimizer (after the code, of course) is statistics, please state how and when you have generated them. The details also vary by version, so it is important to state the exact
version of Oracle, as well as, in general, your platform/OS.

There is a context switch going from SQL to PL/SQL. Also, the SQL engine is highly optimized for relational or set-based queries, so a generally good rule of thumb is to do anything you can in SQL rather than PL/SQL, if you can. This is why Charles' decode example is likely to work well. Not for this example, but in other cases UNION
is the way to go.

Oracle is also biased towards the same exact code being run over and over, so that is the basis of statements like "bind variables are good" and "you automatically have bind variables when..."

Oracle uses latching code to control who has access to what data where, so if your code is actually going to do something like keep header information in the first row of a table and then have a lot of people fight over it, you may have some redesign work to do.

Oracle has views, but it also has Materialized Views. That's another way of saying you can pre-digest some data for some purposes.

Some people here want your data and code to come with actual create table and insert statements to put the test data together, as well as complete stored procedure code. (Personally, I don't care, that's just a statement of some expectations, makes it easy for people to play around. I know I am more likely to work on something in such a situation).

jg

--
@home.com is bogus.
http://theinvisiblethings.blogspot.com/2007/02/running-vista-every-day.html