SQL – Reformatting to Improve Performance 7

4 12 2009

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/7780f1fd94c03ced

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

Suporting Data and table structures.

TableOne Definition:

 
Batch - VarChar
OrderNo - VarChar
Various other Columns of Data...

TableTwo Definition:

OrderNo - VarChar
Status - Number
Various other Columns of Data...

View Definition:

Select o.Batch, o.OrderNo, t.Status, ...
FROM TableOne o Left Outer Join TableTwo t
                on o.OrderNo = t.OrderNo;

Sample Data TableOne:

ABC1    123     ...
ABC1  234       ...
ABC1    345     ...
ABC1  456       ...
ABC2    567     ...
ABC2  678       ...
ABC2    789     ...
ABC2  321       ...
ABC3    432     ...
ABC4  543       ...

Sample Data TableTwo:

123     1       ...
234     1       ...
345     2       ...
456     3       ...
567     2       ...
678     1       ...
789     2       ...
321     2       ...
432     3       ...
543     3       ...

Expected Return Ref Cursor:

ABC1    4       2       1       3
ABC2    4       1       3       null
ABC3    1       null    null    1
ABC4    1       null    1       null

Reduce it to a simple SQL statement using DECODE.  If the STATUS is the expected value (1, 2, 3) for the column, return 1, otherwise return NULL.  Then count the non-null return values.  COUNT will only count non-null values:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  COUNT(DECODE(STATUS,1,1,NULL)) TOTAL_1,
  COUNT(DECODE(STATUS,2,1,NULL)) TOTAL_2,
  COUNT(DECODE(STATUS,3,1,NULL)) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

It appears that if COUNT returns 0, that you want NULL to be returned rather than 0.  Once again, use DECODE to convert 0 to NULL and all other values to the original formula:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  DECODE(COUNT(DECODE(STATUS,1,1,NULL)),0,NULL,COUNT(DECODE(STATUS,1,1,NULL))) TOTAL_1,
  DECODE(COUNT(DECODE(STATUS,2,1,NULL)),0,NULL,COUNT(DECODE(STATUS,2,1,NULL))) TOTAL_2,
  DECODE(COUNT(DECODE(STATUS,3,1,NULL)),0,COUNT(DECODE(STATUS,3,1,NULL))) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

The inclusion of STATUS < 4 leads me to believe that you are attempting to reuse a view definition for a different task.  Doing so may lead to performance issues.  It may be a good idea to eliminate the view and directly reference the exact SQL statement of interest.


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: