Re: select * performance

Susan-

If you want to find out what the bottleneck is for your jdbc query, try testing with sql*plus, using autotrace and "set timing on".

There is a reasonable chance the overhead is tcp/ip packet delay. Run the identical query from sqlplus, using autotrace, and "set arraysize 1". The sql*plus client workstation ought to be on the same network segment as the jdbc client. Observe the elapsed time and the network packet and byte count statistics. Elapsed time, from the "set timing on" setting, should resemble your problem jdbc experience.

Now set arraysize 10 or higher and repeat the exercise. You may see an order of magnitude improvement if the network is the problem. Basically, few, fatter packets is way more efficient than more, smaller packets. For a "short row" type of query, you probably want to use the largest arraysize you can. Refer to jdbc doc on how to manage array fetches.

• ricky

Susan Lam wrote:
>
> I have a small table with 10k record and 2M in size. The java
> application needs to pull out everything from this table and display
> it on the interface. This means, we are doing "select * from table"
> through jdbc. The application consistently takes 30 seconds to load.
>
> If I do a "select * from table" in sqlplus it does take 30 seconds to
> retrive everything but since it's mostly waiting on the display this
> test is not accurate. A tkprof result of "select * from table" shows
> that cpu/elapse time takes only around 1 second (mostly on fetching
> part obviously). Is there any other overhead on the database side?
> Can I say the rest of 29 seconds are on the application side trying to
> retrieve and display the data? We are trying to resolve this
> performance issue and I just want to eliminate the possibility of any
> more delay(beside that 1 second) on the database side.
>
> Thanks in advance,
> Susan
Received on Sun Feb 09 2003 - 14:41:20 CST