473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Query Takes time to execute

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
4 2906
amitpatel66
2,367 Expert 2GB
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
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
r035198x
13,262 8TB
If it slows down only when you connect over the network, then perhaps your problem is with your network.
Jul 3 '08 #4
amitpatel66
2,367 Expert 2GB
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

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

Similar topics

11
by: Eugenio | last post by:
Excuse me in advance fo my little English. I've got this stored procedure **************************************************************************** ********** declare @Azienda as...
3
by: robboll | last post by:
Whenever I query my database using Enterprise Manager, the response time is about a second. When I access the same data via a Cold Fusion webpage, it takes about 15 seconds (or more) to resolve. ...
4
by: Richard Hollenbeck | last post by:
The following query takes about one second to execute with less than 1,000 records, but the report that's based on it takes from 15-30 seconds to format and display. That's frustrating for both me...
7
by: rednexgfx_k | last post by:
All, Problem Summary: I've running about 30 make table queries via VBA in Access 2000, and my database goes from 14,000k to over 2,000,000k. In addition, the longer the procedure runs, the...
4
by: Saradhi | last post by:
Is there any way to estimate the time required to execute a T-SQL statement? I need to set the command time out by calculating the time for T-SQL statement. Can any one give an example?
6
by: Rory Campbell-Lange | last post by:
The following query on some small datasets takes over a second to run. I'd be grateful for some help in understanding the explain output, and to remake the code. Looks like the sort is using up...
1
by: Peter Alberer | last post by:
Hi there, i have a problem with a query that uses the result of a plsql function In the where clause: SELECT assignments.assignment_id, assignments.package_id AS package_id,...
3
by: serge | last post by:
How do I determine which method I should use if I want to optimize the performance of a database. I took Northwind's database to run my example. My query is I want to retrieve the Employees'...
2
by: Himmel | last post by:
Hello! The reference database I currently use runs queries that pull data from hundreds of tables in order to create user-friendly form view. The problem is that these queries can take upwards of...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.