Home » SQL & PL/SQL » SQL & PL/SQL » Tuning SQL
Tuning SQL [message #274529] Tue, 16 October 2007 05:38 Go to next message
sellafrica
Messages: 68
Registered: July 2005
Location: Braynston
Member

hi,

One of the developers complains that this sql is slow and wanted help to tune it,what can be done as first steps?

select vwWIP.jobNumber,
CASE WHEN vwWIP.dateOfEntry BETWEEN '2007.10.01' AND '2007.10.31' THEN vwWIP.debitBase ELSE 0.0 END as wipPeriod1
from vw_WIP vwWIP


Thank You
Re: Tuning SQL [message #274533 is a reply to message #274529] Tue, 16 October 2007 06:02 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

first convert this to a date
Quote:

BETWEEN '2007.10.01' AND '2007.10.31'
use to_date.


regards,
Re: Tuning SQL [message #274537 is a reply to message #274533] Tue, 16 October 2007 06:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does the view vw_WIP return a lot of rows?
This query is going to look at every single one of them, so if there are a lot, then it will take some time.
Re: Tuning SQL [message #274550 is a reply to message #274537] Tue, 16 October 2007 07:04 Go to previous messageGo to next message
sellafrica
Messages: 68
Registered: July 2005
Location: Braynston
Member

The view has a lot of rows as i have been told,i dont have access to the box currently,would there be a way to speed it up since having a lot of rows?

Thanks for the resposes
Re: Tuning SQL [message #274553 is a reply to message #274550] Tue, 16 October 2007 07:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

The view


AHA!!!!!

Have a look at the execution plan. There would have been little chance of speeding it up if it was a table. But since it's a view, all bets are off until you have looked at the execution plan.


Re: Tuning SQL [message #274563 is a reply to message #274553] Tue, 16 October 2007 08:10 Go to previous messageGo to next message
sellafrica
Messages: 68
Registered: July 2005
Location: Braynston
Member

Explain Plan from toad

SELECT STATEMENT ALL_ROWS
Cost:320,428 Bytes:159,949,849 Cardinality:5,159,674

HASH GROUP BY
Cost:320,428 Bytes:159,949,849 Cardinality:5,159,674

TABLE ACCESS FULL TABLE FINANCEENTRY
Cost:276,101 Bytes:159,949,849 Cardinality:5,159,674
Re: Tuning SQL [message #274575 is a reply to message #274529] Tue, 16 October 2007 08:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, you have a developer who thinks it is strange that returning 5 million rows takes time.
icon7.gif  Re: Tuning SQL [message #274583 is a reply to message #274529] Tue, 16 October 2007 08:45 Go to previous messageGo to next message
sellafrica
Messages: 68
Registered: July 2005
Location: Braynston
Member

:)Yes, so there is there no way to have this query rewriten to make it a bit faster?
Re: Tuning SQL [message #274592 is a reply to message #274583] Tue, 16 October 2007 09:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Only if he's willing to select fewer rows.

Is he actually getting all the rows back?

Can you post the details of the view? It's possible there's something stupid in it that is slowing things up.

Re: Tuning SQL [message #274743 is a reply to message #274592] Wed, 17 October 2007 01:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If your company is willing to invest in hardware, you can make it a lot faster Very Happy. No seriously, if the machine is heavily used this has implications towards performance as well.

MHE

[Updated on: Wed, 17 October 2007 01:11]

Report message to a moderator

Re: Tuning SQL [message #275056 is a reply to message #274743] Thu, 18 October 2007 03:28 Go to previous messageGo to next message
sellafrica
Messages: 68
Registered: July 2005
Location: Braynston
Member

Hi,

Here is a view,

CREATE OR REPLACE VIEW VW_WIP
(JOBNUMBER, DATEOFENTRY, CREDITBASE, DEBITBASE)
AS
select f.jobNumber, f.dateOfEntry, f.creditBase, f.debitBase
from financeEntry f
where f.accountNumber ='11210000' or f.accountNumber='11220000' or f.accountNumber='34210000'
/
Re: Tuning SQL [message #275118 is a reply to message #274529] Thu, 18 October 2007 06:47 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
And how about an explain plan for the select statement of the view ?
Re: Tuning SQL [message #275129 is a reply to message #275118] Thu, 18 October 2007 07:27 Go to previous messageGo to next message
sellafrica
Messages: 68
Registered: July 2005
Location: Braynston
Member


ObjectName Rows Bytes Co
SELECT STATEMENT Optimizer Mode=All_Rows 5M 276101
Table ACCESS FULL Financeentry 5M 172M
Re: Tuning SQL [message #275149 is a reply to message #275129] Thu, 18 October 2007 09:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that an index on financeEntry.accountNumber may well work wonders.
Re: Tuning SQL [message #275855 is a reply to message #274529] Tue, 23 October 2007 01:16 Go to previous messageGo to next message
sellafrica
Messages: 68
Registered: July 2005
Location: Braynston
Member

Thanks for all the responses,Can you just explain to me why you think creating an index on financeEntry.accountNumber may do wonders?
Re: Tuning SQL [message #275939 is a reply to message #275855] Tue, 23 October 2007 08:02 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sellafrica wrote on Tue, 23 October 2007 02:16

Thanks for all the responses,Can you just explain to me why you think creating an index on financeEntry.accountNumber may do wonders?


Because of this:
where f.accountNumber ='11210000' or f.accountNumber='11220000' or f.accountNumber='34210000'


and the fact that it is currently doing a full table scan (unless a huge percentage of your rows meet that condition).
Previous Topic: problem with substitution vairable
Next Topic: create a new table
Goto Forum:
  


Current Time: Sun Dec 04 14:47:11 CST 2016

Total time taken to generate the page: 0.14268 seconds