File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Retrieving results through views Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieving results through views" Watch "Retrieving results through views" New topic
Author

Retrieving results through views

Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202
i am working on legacy application having the two views as A and B.

View A looks as below

A
===========
ID || DATE || AMT

View B looks like below

B
===========
ID || DATE || AMT


The query written in view C is some thing as below

select A.ID, A.DATE, A.AMT, B.AMT from A, B
where A.ID=B.ID(+) and A.DATE=B.DATE(+)
UNION
select ID,DATE,0,ID,AMT from B where DATE NOT IN(SELECT DATE FROM A)

There are around 1Lakh records in view A and View B.

While i run this module having the view C, it takes more than 3 hrs to retrieve the results.

If i run this query in TOAD it takes 2.45 hrs to give the output.

Please advice me if i can tune this in any other way.

Please suggest.

Praison Selvaraj
Ranch Hand

Joined: Jun 05, 2010
Posts: 49
Hi Eshwar,

1. Is the Id column the primary key in Table A and id column in table B the corresponding foreign key?
2. Is the date column part of the key? If not create an index on the date column.

Cheers,
Praison

SCJP, SCWCD, SCEA
Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202
thanks selva for quick reply

both A and B are different views

Not sure if ID is primary key in both views. How could i find it. Can views also have primary key. sorry for basic question, please let me know.

ID in view B is not a foreign key, as both these views are totally different.

Date is not part of the key, so i can create a index. thanks for that suggestion. However, can we have index to the views as well. please let me know

However, while you notice this query, there are three conditions getting set while the developer wrote this.

a) He needs all the records in A
b) He needs all the records that are common in A and B, hence he uses the right join (+)
c) He needs all the records in B for the date that is not matching in A

From this I was thinking, if the query could be rewtitten in some way, so that the performance could be tuned well.

Your suggestions would be helpful

Thanks
Praison Selvaraj
Ranch Hand

Joined: Jun 05, 2010
Posts: 49
Hi,

I meant, whether the underlying tables have indexes?

Create the indexes and check if the indexes are making the query to execute any faster?

Thanks,
Praison
Eshwar Prasad
Ranch Hand

Joined: Mar 21, 2008
Posts: 202
Thanks selva

I checked that underlying table have indexes.

However, my module uses only the query having views.

Could i create indexes for view as well.

Here, while i tested the query, NOT IN(SELECT DATE FROM A) takes more time than rest.

 
Don't get me started about those stupid light bulbs.
 
subject: Retrieving results through views