|
View previous topic :: View next topic
|
| Author |
Message |
sreekusr
Warnings : 1
New User
Joined: 28 Aug 2006
Posts: 54
Location: Madrid
|
|
|
|
Hello All,
I have a Cursor Defined with the Below Query
| Code: |
SELECT A.COL1,
A.COL2,
MAX(A.COL3)
FROM VIEW1 A,
VIEW2 B
WHERE A.COL1 = B.COL1 AND
A.COL2 = B.COL2 AND
A.COL3 IN ('AAA','BBB','ZZZ') AND
B.Col4 = :WS-VAR1 AND
B.col5 = :WS-VAR2
GROUP BY A.COLUMN1, A.COLUMN2
ORDER BY A.COLUMN1 ASC , A.COLUMN2 DESC;
|
| Code: |
VIew1
------
Col1 Col2 Col3
10 4 AAA
10 4 BBB
10 5 ZZZ
11 1 ZZZ
12 6 AAA
12 6 BBB
13 3 AAA
VIEW2
-----
Col1 COl2 Col4 Col5
10 4 3 5
11 1 2 3
12 6 3 5
13 3 3 5
|
Considering the values of ws-Var1 = 3 and ws-Var2=5
Following is the Output of the SQL
| Code: |
A.Col1 A.Col2 A.COl3
------- ------ ------
10 4 BBB
12 6 BBB
13 3 AAA
|
Problem:CPU time for Opening the Cursor is much Higher(due to the MAx function and
GROUP/ORDER clause) and it has an impact on the performance.
CAn anyone suggest any modification to the SQL query in such a way that
the MAX function and GROUP/ORDER can be avoided,but give the Same Above result.
Thanks,
Sree |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Are VIEW1 and VIEW2 views or tables. Applying your query against actual tables would most likely be faster then using views. |
|
| Back to top |
|
|
sreekusr
Warnings : 1
New User
Joined: 28 Aug 2006
Posts: 54
Location: Madrid
|
|
|
|
HI Carg,
I am restricted to use Views instead of actual Tables.
VIEW1 and VIEW2 are Views.
Thanks,
Sree |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
views usually perform pretty well stand-alone and they can insulate the developer from requiring any knowledge about the table. But when multiple views are joined , the access path can be less than optimum.
Basically, you will need to reference the base table directly along with code similar to the logic in the view for a better performance ...
Alternatively you can try this query
| Code: |
SELECT A.COL1, A.COL2, A.COL3 FROM
(
SELECT COL1,COL2,MAX(COL3) AS COL3
FROM VIEW1
WHERE COL3 IN ('AAA','BBB','ZZZ')
GROUP BY COL1, COL2
) AS A
,
(
SELECT COL1, COL2
FROM VIEW2
WHERE B.COL4 = :WS-VAR1
AND B.COL5 = :WS-VAR2
) AS B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
ORDER BY A.COL1 ASC, A.COL2 DESC ;
|
Be sure to run EXPLAIN on both queries as I am not aware of the table indexes ... |
|
| Back to top |
|
|
bauer
New User
Joined: 03 Mar 2009
Posts: 28
Location: germany
|
|
|
|
Use Explain and check if runstats has executed.
which indices are available ? |
|
| Back to top |
|
|
|
|
