Home » RDBMS Server » Performance Tuning » Slow query performance (Oracle 9i, Windows XP)
Slow query performance [message #422599] Thu, 17 September 2009 11:34 Go to next message
brunom
Messages: 8
Registered: May 2006
Junior Member
Hello,
I'm confronted to the following problem:
I have two quite big tables containing invoices.

The invoice table (t_invoice) contains invoice headers.
The invoice item table (t_invoice_item) contains individual pricing items.

An invoice does not necessarily have items.
I need to list all invoices with summed prices, whether the invoice has items or not.

What I did is create two nested views. But the GROUP BY in the first one does not perform well, and I don't know how to optimise this.


CREATE VIEW v_invoice_total_price_0
AS
 -- calculates total price for invoices with items
 SELECT
  id_invoice,
  SUM(price) AS total_price,
  SUM(vat) AS total_vat
 FROM
  t_invoice_item
 GROUP BY
  t_invoice_item.id_invoice
UNION
 -- ensures all invoices are in the list
 SELECT
   t_invoice.id_invoice,
   0 AS total_price,
   0 AS total_vat
 FROM
   t_invoice;


-- makes sure there is one single row per invoice
CREATE VIEW v_invoice_total_price
AS
SELECT
  id_invoice,
  SUM(total_price) AS total_price,
  SUM(total_vat) AS total_vat
FROM
  v_invoice_total_price_0
GROUP BY
  id_invoice;


id_invoice is the PK of the t_invoice table.
There is a FK on id_invoice in the t_invoice_item table.

I would greatly appreciate any suggestion to speed up the performance of v_invoice_total_price.

Re: Slow query performance [message #422610 is a reply to message #422599] Thu, 17 September 2009 19:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Rewrite it as an outer join from INVOICE to INVOICE ITEM

Ross Leishman
Re: Slow query performance [message #422853 is a reply to message #422610] Sun, 20 September 2009 19:15 Go to previous messageGo to next message
linlasj
Messages: 98
Registered: August 2005
Location: Linköping
Member
rleishman wrote on Fri, 18 September 2009 02:34
Rewrite it as an outer join from INVOICE to INVOICE ITEM

Ross Leishman

Yes. That is a good suggestion. This is how these things are solved. When you can have things in one table but not in the other. The clue is in your request "An invoice does not necessarily have items.".

So. A small (+) nicely placed will solve the trick. You do not need the UNION. You do just need the first query.

And...just so we get things right... I think that in newer versions of Oracle there are reserved words to SUM UP every invoice. PLease study the SQL manual.

[Updated on: Sun, 20 September 2009 19:18]

Report message to a moderator

Re: Slow query performance [message #422961 is a reply to message #422853] Mon, 21 September 2009 08:43 Go to previous messageGo to next message
brunom
Messages: 8
Registered: May 2006
Junior Member
Hello both,
Thanks for your suggestion.
As a matter of fact, the query was initially written with an OUTER JOIN but is was desperately slow, so I replaced it with a UNION which increased the performance but not much. 10 to 20%.
I need more optimisation and don't know how I can obtain it.
Maybe through these SUM UP instructions your are telling me about?
Re: Slow query performance [message #422964 is a reply to message #422961] Mon, 21 September 2009 08:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I find it hard to believe that that view solution is going to run faster than:
SELECT inv.id_invoice 
      ,SUM(nvl(itm.price,0)) AS total_price 
      ,SUM(nvl(itm.vat,0))   AS total_vat
FROM   t_invoice      inv
      ,t_invoice_item itm
WHERE  inv.id_invoice = itm.id_invoice
GROUP BY inv.id_invoice;


Can you post an explain plan for both attemtps?

I've no idea what @linlasj is talking about. SUM, has been around forwever, the analytic form of SUM has been around since Oracle 9, and I can't think of anything else he could be talking about.
Re: Slow query performance [message #423458 is a reply to message #422599] Thu, 24 September 2009 07:30 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
I would recommend using inline view:

SELECT /*+ NO_MERGE(tii) USE_HASH(tii) */ inv.id_invoice, NVL(tii.total_price,0) total_price, 
  NVL(tii.total_vat,0) total_vat
FROM t_invoice      inv,
  ( SELECT id_invoice, SUM(price) total_price, SUM(vat) total_vat
    FROM t_invoice_item
    GROUP BY id_invoice ) tii
WHERE inv.id_invoice = tii.id_invoice(+)



I used hint to ensure the best (IMHO) access path.

You may get it even without hints (use/post explain).

HTH
Re: Slow query performance [message #423540 is a reply to message #423458] Fri, 25 September 2009 04:31 Go to previous messageGo to next message
brunom
Messages: 8
Registered: May 2006
Junior Member
Thank you all for your suggestions.
I can't manage to get more than 15% improvement in speed.
I think I'm going to change the application's logic instead,
and integrate the price fields into t_invoice. I'll recalc it each time I add, remove or update an item in t_invoice_item.

Thanks again.
Re: Slow query performance [message #423654 is a reply to message #422599] Sat, 26 September 2009 00:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I can't see you getting much of a better solution that the simple select solution. Try this one:

create index t_invoice_item_fk1 on invoice_item(invoice_id,price,vat);

select a.invoice_id
      ,sum(b.price) sum_price
      ,sum(b.vat) sum_vat
from  t_invoice a
     ,t_invoice_item b
where a.invoice_id = b.invoice_id(+)
/

The idea is to get an index only lookup on t_invoice_item by having everything needed for the query from table t_invoice_item in the one index. You can still create the foreign key constraint as you normally would. That should speed things up nicely.

Good luck, Kevin
Re: Slow query performance [message #423782 is a reply to message #423654] Mon, 28 September 2009 03:31 Go to previous message
brunom
Messages: 8
Registered: May 2006
Junior Member
Hi Kevin,
This is a very clever solution.
I tested it, and it is indeed much faster.
Thank you very much,
Bruno
Previous Topic: performance issue wtith single instance database
Next Topic: Performance tuning in Oracle
Goto Forum:
  


Current Time: Fri Dec 02 14:31:28 CST 2016

Total time taken to generate the page: 0.26638 seconds