By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,288 Members | 3,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,288 IT Pros & Developers. It's quick & easy.

Query Takes time to execute

P: 20
I am creating a jsp application . . . .I am retreiving some values from my database oracle 10g..my query taking time to execute even in isql plus..am uisng tomcat5 and oracle 10gR2..can anyone pls tell me why it happens...my query fetches data from four tables...i dont know exactly why its taking so muc time say 15 seconds...when i run the application in my local host it retrievs fast when i do that in server it creates a problem..

thanx in advnace
Jul 2 '08 #1
Share this Question
Share on Google+
4 Replies


amitpatel66
Expert 100+
P: 2,367
15 seconds is really not so much time....its decent enough. By the way it depends on various factors though. few of them are:

1. the amount of data fetched (no of records)
2. the way query is implemented

And without having a look at your query we would not be able to suggest you anything. So please post your query here for reference.
Jul 2 '08 #2

P: 20
15 seconds is really not so much time....its decent enough. By the way it depends on various factors though. few of them are:

1. the amount of data fetched (no of records)
2. the way query is implemented

And without having a look at your query we would not be able to suggest you anything. So please post your query here for reference.

This is my Query:

SELECT distinct A.foliono, B.scheme_name,A.invfname,D.investment_amt,D.mode_p ayment,E.bankcode,D.documents,A.transaction_no,A.a ppl_no,case when D.pan_flag=1 then 'PAN,' else ' ' end,case when D.kyc_flag=1 then 'KYC.' else ' ' end FROM transaction_details A,scheme_master B,Investor_master C,purchase D,bank_master E WHERE B.scheme_code=D.scheme_code AND D.bankcode=E.bankcode AND A.transaction_no=D.transaction_no AND A.authorise_flag=0 and B.scheme_ty ='CASH' AND A.transac_date=(select to_char(sysdate,'DD/MM/YYYY') from dual) AND A.location_code='"+location+"'


Actually it works fine when i deployed in my client server it takes time sometimes it takes atmost one minute
1.pls tell me how can i test my query about the performance
2.is der any command in oracle to test
3.how much bytes it takes
Jul 2 '08 #3

10K+
P: 13,264
If it slows down only when you connect over the network, then perhaps your problem is with your network.
Jul 3 '08 #4

amitpatel66
Expert 100+
P: 2,367
This is my Query:

SELECT distinct A.foliono, B.scheme_name,A.invfname,D.investment_amt,D.mode_p ayment,E.bankcode,D.documents,A.transaction_no,A.a ppl_no,case when D.pan_flag=1 then 'PAN,' else ' ' end,case when D.kyc_flag=1 then 'KYC.' else ' ' end FROM transaction_details A,scheme_master B,Investor_master C,purchase D,bank_master E WHERE B.scheme_code=D.scheme_code AND D.bankcode=E.bankcode AND A.transaction_no=D.transaction_no AND A.authorise_flag=0 and B.scheme_ty ='CASH' AND A.transac_date=(select to_char(sysdate,'DD/MM/YYYY') from dual) AND A.location_code='"+location+"'


Actually it works fine when i deployed in my client server it takes time sometimes it takes atmost one minute
1.pls tell me how can i test my query about the performance
2.is der any command in oracle to test
3.how much bytes it takes
You can generate an explain plan for the same by using the comand:

Expand|Select|Wrap|Line Numbers
  1.  
  2. sql>explain plan for <select query>;
  3. sql> select * from plan_table;
  4.  
  5.  
the table plan_table will sotre your query's explain plan details showing you the table scan info index used etc..you can also look for creating an index on the column that is widely used in WHERE condition to improve the performance..But as you say when you run the query at client side it runs fast so the network should be the problem here.
Jul 3 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.