Query Takes time to execute

[mndprasad]

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

[amitpatel66]

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.

[mndprasad]

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.inv estment_amt,D.m ode_payment,E.b ankcode,D.docum ents,A.transact ion_no,A.appl_n o,case when D.pan_flag=1 then 'PAN,' else ' ' end,case when D.kyc_flag=1 then 'KYC.' else ' ' end FROM transaction_det ails A,scheme_master B,Investor_mast er C,purchase D,bank_master E WHERE B.scheme_code=D .scheme_code AND D.bankcode=E.ba nkcode AND A.transaction_n o=D.transaction _no AND A.authorise_fla g=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

[r035198x]

If it slows down only when you connect over the network, then perhaps your problem is with your network.

[amitpatel66]

This is my Query:

SELECT distinct A.foliono, B.scheme_name,A .invfname,D.inv estment_amt,D.m ode_payment,E.b ankcode,D.docum ents,A.transact ion_no,A.appl_n o,case when D.pan_flag=1 then 'PAN,' else ' ' end,case when D.kyc_flag=1 then 'KYC.' else ' ' end FROM transaction_det ails A,scheme_master B,Investor_mast er C,purchase D,bank_master E WHERE B.scheme_code=D .scheme_code AND D.bankcode=E.ba nkcode AND A.transaction_n o=D.transaction _no AND A.authorise_fla g=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:

[code=oracle]

sql>explain plan for <select query>;
sql> select * from plan_table;

[/code]

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..Bu t as you say when you run the query at client side it runs fast so the network should be the problem here.