You might try changing your sColumn from a String to a StringBuffer.
I was doing something similar to your example. I was trying to
retrieve rows from the database and display them in a textarea. Using
sqlplus in an xterm window it took 7 seconds to display all the data.
Trying to get the data using strings in Java was still running after
30 minutes and I killed the program. I re-did the program to use
StringBuffer and the results pop into the textarea in seconds.
Good Luck.
mike wrote:
>
> The server machine has lots of RAM and plenty of processor. The NT
> performance monitor shows CPU usage around 2% and lots of memory available
> (re no swapping). There is no other activity on this server box, no logins,
> shared drives, nothing. The data store on this server has 6 drives arranged
> as raid 5 through hardware.
>
> The java code is simple: create a connection to database, formulate and
> execute query, read results. The amount of data per row fetched is on
> average 90 bytes. Again, if you look at my previous posting you can see
> that the query execution time is great - reading the rows is terrible.
> Similar results are seen in a C++ program that uses DbTools and oracle OCI
> driver.
>
> I was just kidding about Access - obviously there is no comparison. I am,
> however, using PostgreSql on Linux and have no problem executing queries
> that move more data than my NT queries in microseconds. I really think that
> I have a gross parameter error that has something to do with reading the
> result set out of the database.
>
> import java.sql.*;
> public class DbTestA
> {
> // Start of driver
> //
> public static void main(String[] args)
> {
> try
> {
> DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver
> ());
> String[] Criteria = {"WILL", "ALA", "FRA","BAI"};
>
> PartyName theTest = new PartyName();
> for (int ii = 0; ii < Criteria.length; ii++)
> {
> theTest.setCriteria(Criteria[ii]);
> theTest.start();
> }
> }
> catch (Exception e)
> {
> System.out.println("Failed to initial test driver: " + e);
> }
>
> }
> }
>
> ////////////////////////////////////////////////////////////////////////////
> ////////
> // One inner class per test scenario
> ////////////////////////////////////////////////////////////////////////////
> ////////
> class PartyName
> {
> private String m_Criteria;
> private Connection con;
>
> public PartyName() throws Exception
> {
> DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());
> con = DriverManager.getConnection ("jdbc:oracle:oci8:@beteldev","orion",
> "orion");
> System.out.println("Made the connection");
> System.out.println(con.getMetaData());
> }
>
> public void setCriteria(String theCriteria)
> {
> m_Criteria = theCriteria;
> }
>
> public void start()
> {
> //
> // Execute Query
> //
> try
> {
>
> ResultSet dbResults = null;
>
> Statement stmt = con.createStatement();
> String SqlStmt = "select * from name where ptysurnm like '" +
> m_Criteria + "%'";
> System.out.println("Executing :" + SqlStmt);
>
> // time how long it takes to execute the query
> long queryTime = System.currentTimeMillis();
> dbResults = stmt.executeQuery(SqlStmt);
> queryTime = (System.currentTimeMillis() - queryTime);
>
> // Time how long it takes to drain the reader
> long drainTime = System.currentTimeMillis();
> long recCnt = 0;
> String sColumn;
> try
> {
> while(dbResults.next())
> {
> recCnt++;
>
> try
>
> sColumn = dbResults.getString("NAMEID");
> sColumn = dbResults.getString("PTYSURNM");
> sColumn = dbResults.getString("PTYFIRSTNM");
> sColumn = dbResults.getString("PTYSUFFIXNM");
> sColumn = dbResults.getString("PTYSALUTNDE");
> sColumn = dbResults.getString("PTYTITLEDE");
> sColumn = dbResults.getString("PTYMIDDLENM");
> sColumn = dbResults.getString("BusinessNm");
> }
> catch (Exception e)
> {
> System.out.println("getString Error: " + e);
> }
> }
> }
> catch (Exception e)
> {
> System.out.println("while(dbResults.next()) Error: " + e);
> }
>
> drainTime = System.currentTimeMillis() - drainTime;
> System.out.println("Criteria: " + m_Criteria);
> System.out.println("Query Time: " + queryTime);
> System.out.println("Drain Time: " + drainTime);
> System.out.println("Record Count: " + recCnt);
>
> System.out.println("=================================================");
>
> }
> catch (Exception e)
> {
> System.out.println(" executeQuery: Error: " + e);
> }
>
> }
> }
>
> mpir_at_compuserve.com wrote in message <7gnjvm$rgf$1_at_nnrp1.dejanews.com>...
> >PMJI, but there are some environmental factors that can cause a major
> delay:
> >1-swapping memory. I did not see what is doing the retrieval, sga size vs
> >available ram, etc. but that kind of delay can happen if the system has
> >started swapping buffers.
> >
> >2-Swapping can be aggrevated or caused if the i/o is to the same disk, that
> is
> >if you are writing to a file that is on the same disk as the db, etc. This
> is
> >particularly true if the swapping is happening within Oracle (db block
> buffers
> >in SGA vs PGA buffers)
> >
> >3-You say you have no network but are NT. You may be running locally, but
> NT
> >is still subject to assorted network and other priority interrupts if your
> >machine is on a network.
> >
> >4-How big are the rows? 1000 5K rows may take 5 seconds, but 1000 5 byte
> rows
> >shouldn't. NT's screen displays are not the fastest for text modes.
> >
> >In article <7gnemr$rho7_at_amber.alltel.com>,
> > "MScheuter" <mike.scheuter_at_alltel.com> wrote:
> >> Thanks for the response.
> >>
> >> The query is essentially:
> >> select * from CName where LastName like 'Nec%'
> >> Again the query execution time is great, in the range of 180 ms. The
> time
> >> it takes to retrieve what I consider a small amount of data, about 1000
> >> rows, is terrible. Is it reasonable for oracle to take 5000 ms to send
> back
> >> to a program 1000 rows of data with no network in the picture? I think
> not
> >> since this same operation is MS Access can once again be measured in the
> >> hundreds of milliseconds.
> >>
> >> I tend to think that there is a gross parameter error within the database
> on
> >> NT. Our DBA is experienced with Oracle on Unix but not NT and such has
> not
> >> found anything to tune. We have been doing traces and cost tracking
> through
> >> explain plan but this has not yeilded the information that we need. I
> tells
> >> us what we already know, that the query (build of the result set) works
> >> great. Explain plan does not tell us what is going on when reading the
> >> result set. Is there additional monitoring options that can be enabled
> >> within Oracle?
> >>
> >> thanks again
> >> -mike
> >>
> >> Sybrand Bakker wrote in message
> >> <925837438.21998.0.muttley.d4ee154e_at_news.demon.nl>...
> >> >Most likely this is a sql statement tuning issue.
> >> >We need to have your statement and details about the tables.
> >> >Then: are you using array fetching. This is possible in Pro*C and
> without
> >> >doubt it is possible in OCI. Fetching a record is the costly operation
> >> there
> >> >is. Fetch your records in batches of 100 or even 1000. Also makes sure
> >> >Oracle traces this session. This will provide useful detail from the
> server
> >> >side. Make sure sql_trace = true and timed_statistics = true in
> >> >init<sid>.ora, bounce the database and see what happens
> >> >
> >> >Hth,
> >> >
> >> >Sybrand Bakker, Oracle DBA
> >> >
> >> >
> >> >MScheuter wrote in message <7gn1s6$rho6_at_amber.alltel.com>...
> >> >>I have a table that contains customer name information (about 10
> fields).
> >> >>There are about 1.9 million rows in the table. The table is index by
> the
> >> >>last name. I am writing application code in both C++ using oracle oci
> and
> >> >>java using again the supplied oracle oci drivers (see oracle web site).
> >> In
> >> >>a test program I am timing the execution of the select statment and
> then
> >> >>timing how long it takes to 'read' (or drain) the rows of data from the
> >> >>database. The timing results are as follows:
> >> >>
> >> >>Rows in Result Set Query Time (ms) Read results time (ms)
> >> >>2824 187 11875
> >> >>211 156
> 969
> >> >>1330 187 4984
> >> >>1740 78 6062
> >> >>
> >> >>The test program was run on both a workstation using net8 and locally
> on
> >> >the
> >> >>server that is running the database using a local connection (no
> network
> >> >>involved) and the results were similiar.
> >> >>
> >> >>The server is a 2 way 200 mhz intell box with 512 meg ram 80 gig hd
> space.
> >> >>Can someone please explain some areas that I can look at in order to
> >> >improve
> >> >>the performance of reading the result set?
> >> >>
> >> >>Thanks
> >> >>-mike
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >--
> >Joseph R.P. Maloney, CCP,CSP,CDP
> >MPiR, Inc.
> >502-451-7404
> >some witty phrase goes here, I think.
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri May 07 1999 - 00:05:51 CDT
