KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > compiere > dbPort > JdbcTest


1 /******************************************************************************
2  * The contents of this file are subject to the Compiere License Version 1.1
3  * ("License"); You may not use this file except in compliance with the License
4  * You may obtain a copy of the License at http://www.compiere.org/license.html
5  * Software distributed under the License is distributed on an "AS IS" basis,
6  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7  * the specific language governing rights and limitations under the License.
8  * The Original Code is Compiere ERP & CRM Business Solution
9  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
10  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/

14 package org.compiere.dbPort;
15
16 import java.sql.*;
17 import javax.sql.*;
18
19 import oracle.jdbc.OracleConnection;
20 import oracle.jdbc.pool.OracleDataSource;
21 import oracle.jdbc.pool.OracleConnectionCacheImpl;
22
23 //import oracle.jdbc.rowset.*;
24

25 /**
26  * JDBC Performance Test - Oracle
27  *
28  * @author Jorg Janke
29  * @version $Id: JdbcTest.java,v 1.6 2002/09/27 04:44:16 jjanke Exp $
30  */

31 public class JdbcTest extends Thread JavaDoc
32 {
33 /*****************************************************************************
34
35 Multiple Connections Fetch=10 Conn=2032 Stmt=0 Query=47 Retrieve=2109 ClRs=0 ClStmt=0 ClConn=0 - Total=4188 Stmt=2156 Query=2156
36 Multiple Connections Fetch=10 Conn=141 Stmt=0 Query=31 Retrieve=1875 ClRs=0 ClStmt=0 ClConn=0 - Total=2047 Stmt=1906 Query=1906
37 Multiple Connections Fetch=10 Conn=141 Stmt=0 Query=31 Retrieve=1844 ClRs=0 ClStmt=0 ClConn=0 - Total=2016 Stmt=1875 Query=1875
38 Data Source Fetch=10 Conn=172 Stmt=0 Query=16 Retrieve=1875 ClRs=0 ClStmt=0 ClConn=0 - Total=2063 Stmt=1891 Query=1891
39 Data Source Fetch=10 Conn=672 Stmt=15 Query=16 Retrieve=1797 ClRs=0 ClStmt=0 ClConn=0 - Total=2500 Stmt=1828 Query=1813
40 Data Source Fetch=10 Conn=156 Stmt=0 Query=16 Retrieve=1766 ClRs=0 ClStmt=0 ClConn=15 - Total=1953 Stmt=1782 Query=1782
41 Connection Cache Fetch=10 Conn=141 Stmt=0 Query=125 Retrieve=1766 ClRs=0 ClStmt=0 ClConn=0 - Total=2032 Stmt=1891 Query=1891
42 Connection Cache Fetch=10 Conn=0 Stmt=0 Query=15 Retrieve=1766 ClRs=0 ClStmt=0 ClConn=0 - Total=1781 Stmt=1781 Query=1781
43 Connection Cache Fetch=10 Conn=0 Stmt=0 Query=16 Retrieve=1765 ClRs=0 ClStmt=0 ClConn=0 - Total=1781 Stmt=1781 Query=1781
44 Multiple Connections Fetch=20 Conn=4501 Stmt=0 Query=15 Retrieve=1313 ClRs=0 ClStmt=0 ClConn=0 - Total=5829 Stmt=1328 Query=1328
45 Multiple Connections Fetch=20 Conn=125 Stmt=0 Query=16 Retrieve=1312 ClRs=0 ClStmt=0 ClConn=0 - Total=1453 Stmt=1328 Query=1328
46 Multiple Connections Fetch=20 Conn=141 Stmt=0 Query=31 Retrieve=1406 ClRs=0 ClStmt=0 ClConn=0 - Total=1578 Stmt=1437 Query=1437
47 Data Source Fetch=20 Conn=126 Stmt=0 Query=31 Retrieve=1297 ClRs=0 ClStmt=0 ClConn=0 - Total=1454 Stmt=1328 Query=1328
48 Data Source Fetch=20 Conn=125 Stmt=0 Query=16 Retrieve=1328 ClRs=0 ClStmt=0 ClConn=0 - Total=1469 Stmt=1344 Query=1344
49 Data Source Fetch=20 Conn=140 Stmt=0 Query=16 Retrieve=1469 ClRs=0 ClStmt=0 ClConn=0 - Total=1625 Stmt=1485 Query=1485
50 Connection Cache Fetch=20 Conn=0 Stmt=0 Query=31 Retrieve=1344 ClRs=0 ClStmt=0 ClConn=0 - Total=1375 Stmt=1375 Query=1375
51 Connection Cache Fetch=20 Conn=0 Stmt=0 Query=16 Retrieve=1375 ClRs=0 ClStmt=0 ClConn=0 - Total=1391 Stmt=1391 Query=1391
52 Connection Cache Fetch=20 Conn=0 Stmt=0 Query=15 Retrieve=1375 ClRs=0 ClStmt=0 ClConn=0 - Total=1390 Stmt=1390 Query=1390
53 JDBC RowSet Fetch=10 Conn=16 Stmt=0 Query=3969 Retrieve=3047 ClRs=0 ClStmt=0 ClConn=0 - Total=7032 Stmt=7016 Query=7016
54 JDBC RowSet Fetch=10 Conn=0 Stmt=0 Query=172 Retrieve=2781 ClRs=0 ClStmt=0 ClConn=0 - Total=2953 Stmt=2953 Query=2953
55 JDBC RowSet Fetch=10 Conn=0 Stmt=0 Query=313 Retrieve=2609 ClRs=0 ClStmt=0 ClConn=0 - Total=2922 Stmt=2922 Query=2922
56 Cached RowSet Fetch=10 Conn=63 Stmt=0 Query=5406 Retrieve=16 ClRs=0 ClStmt=0 ClConn=0 - Total=5485 Stmt=5422 Query=5422
57 Cached RowSet Fetch=10 Conn=0 Stmt=0 Query=3907 Retrieve=0 ClRs=0 ClStmt=0 ClConn=0 - Total=3907 Stmt=3907 Query=3907
58 Cached RowSet Fetch=10 Conn=0 Stmt=0 Query=3890 Retrieve=0 ClRs=0 ClStmt=0 ClConn=0 - Total=3890 Stmt=3890 Query=3890
59 Shared Connection Threads=10 Yield=false ms= 18267 each= 1826
60 Shared Connection Threads=10 Yield=false ms= 18220 each= 1822
61 Shared Connection Threads=10 Yield=true ms= 18329 each= 1832
62 Shared Connection Threads=10 Yield=true ms= 18314 each= 1831
63 Multiple Connections Threads=10 Yield=false ms= 14610 each= 1461
64 Multiple Connections Threads=10 Yield=false ms= 14360 each= 1436
65 Multiple Connections Threads=10 Yield=true ms= 13986 each= 1398
66 Multiple Connections Threads=10 Yield=true ms= 14017 each= 1401
67 Multiple PreCreated Threads=10 Yield=false ms= 5376 each= 537
68 Multiple PreCreated Threads=10 Yield=false ms= 1828 each= 182
69 Multiple PreCreated Threads=10 Yield=true ms= 12017 each= 1201
70 Multiple PreCreated Threads=10 Yield=true ms= 12032 each= 1203
71 Data Source Threads=10 Yield=false ms= 13391 each= 1339
72 Data Source Threads=10 Yield=false ms= 13532 each= 1353
73 Data Source Threads=10 Yield=true ms= 13923 each= 1392
74 Data Source Threads=10 Yield=true ms= 13829 each= 1382
75 Connection Cache Threads=10 Yield=false ms= 12907 each= 1290 CacheSize=2, Active=0
76 Connection Cache Threads=10 Yield=false ms= 12907 each= 1290 CacheSize=2, Active=0
77 Connection Cache Threads=10 Yield=true ms= 12813 each= 1281 CacheSize=2, Active=0
78 Connection Cache Threads=10 Yield=true ms= 12813 each= 1281 CacheSize=2, Active=0
79
80 ******************************************************************************/

81
82     // Default no of threads to 10
83
private static final int NUM_OF_THREADS = 10;
84
85     private static final String JavaDoc DRIVER =
86     // "oci8";
87
"thin";
88
89     private static final String JavaDoc CONNECTION =
90     // "jdbc:oracle:oci8:@";
91
// "jdbc:oracle:oci8:@dev1";
92
"jdbc:oracle:thin:@dev:1521:dev1";
93
94
95     private static final String JavaDoc UID = "compiere";
96     private static final String JavaDoc PWD = "compiere";
97     private static final String JavaDoc STATEMENT = "SELECT * FROM AD_Column";
98     private static final boolean WITH_OUTPUT = false;
99
100     private static boolean s_do_yield = true;
101
102     private static Connection s_sconn = null;
103     private static Connection[] s_conn = null;
104     private static OracleDataSource s_ds = null;
105     private static OracleConnectionCacheImpl s_cc = null;
106
107     private static int s_fetchSize = 10;
108
109     // Connection
110
private static int s_cType = 0;
111     private static final String JavaDoc[] C_INFO = {
112         "Shared Connection ",
113         "Multiple Connections ",
114         "Multiple PreCreated ",
115         "Data Source ",
116         "Connection Cache "};
117     private static final int C_SHARED = 0;
118     private static final int C_MULTIPLE = 1;
119     private static final int C_PRECREATED = 2;
120     private static final int C_DATASOURCE = 3;
121     private static final int C_CACHE = 4;
122
123     // Data
124
private static int s_rType = 0;
125     private static final String JavaDoc[] R_INFO = {
126         "ResultSet ",
127         "Cached RowSet ",
128         "JDBC RowSet "};
129     private static final int R_RESULTSET = 0;
130     private static final int R_CACHED_ROWSET = 1;
131     private static final int R_JDBC_ROWSET = 2;
132
133
134
135     /**
136      * Main Test Start
137      * @param args
138      */

139     public static void main (String JavaDoc args [])
140     {
141         try
142         {
143             /* Load the JDBC driver */
144             DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
145
146             s_ds = new OracleDataSource();
147             s_ds.setDriverType(DRIVER);
148             s_ds.setServerName("dev");
149             s_ds.setNetworkProtocol("tcp");
150             s_ds.setDatabaseName("dev1");
151             s_ds.setPortNumber(1521);
152             s_ds.setUser("compiere");
153             s_ds.setPassword("compiere");
154             //
155
s_cc = new OracleConnectionCacheImpl();
156             s_cc.setDriverType(DRIVER);
157             s_cc.setServerName("dev");
158             s_cc.setNetworkProtocol("tcp");
159             s_cc.setDatabaseName("dev1");
160             s_cc.setPortNumber(1521);
161             s_cc.setUser("compiere");
162             s_cc.setPassword("compiere");
163             s_cc.setMaxLimit(NUM_OF_THREADS/4);
164             s_cc.setCacheScheme(OracleConnectionCacheImpl.FIXED_WAIT_SCHEME);
165         // s_cc.setCacheScheme(OracleConnectionCacheImpl.DYNAMIC_SCHEME);
166

167             s_fetchSize = 10;
168             s_cType = C_MULTIPLE;
169             statementTiming();
170             statementTiming();
171             statementTiming();
172             s_cType = C_DATASOURCE;
173             statementTiming();
174             statementTiming();
175             statementTiming();
176             s_cType = C_CACHE;
177             statementTiming();
178             statementTiming();
179             statementTiming();
180             s_fetchSize = 20;
181             s_cType = C_MULTIPLE;
182             statementTiming();
183             statementTiming();
184             statementTiming();
185             s_cType = C_DATASOURCE;
186             statementTiming();
187             statementTiming();
188             statementTiming();
189             s_cType = C_CACHE;
190             statementTiming();
191             statementTiming();
192             statementTiming();
193             //
194
s_fetchSize = 10; // standard value
195
/**
196             s_rType = R_JDBC_ROWSET;
197             rowSetTiming();
198             rowSetTiming();
199             rowSetTiming();
200             s_rType = R_CACHED_ROWSET;
201             rowSetTiming();
202             rowSetTiming();
203             rowSetTiming();
204 **/

205             //
206
s_cType = C_SHARED;
207             s_do_yield = false;
208             runTest();
209             runTest();
210             s_do_yield = true;
211             runTest();
212             runTest();
213             //
214
s_cType = C_MULTIPLE;
215             s_do_yield = false;
216             runTest();
217             runTest();
218             s_do_yield = true;
219             runTest();
220             runTest();
221             //
222
s_cType = C_PRECREATED;
223             s_do_yield = false;
224             runTest();
225             runTest();
226             s_do_yield = true;
227             runTest();
228             runTest();
229             //
230
s_cType = C_DATASOURCE;
231             s_do_yield = false;
232             runTest();
233             runTest();
234             s_do_yield = true;
235             runTest();
236             runTest();
237             //
238
s_cType = C_CACHE;
239             s_do_yield = false;
240             runTest();
241             runTest();
242             s_do_yield = true;
243             runTest();
244             runTest();
245             //
246

247         }
248         catch (Exception JavaDoc e)
249         {
250             e.printStackTrace();
251         }
252     } // main
253

254     /*************************************************************************/
255
256     /**
257      * Run the test
258      * @throws Exception
259      */

260     static void runTest() throws Exception JavaDoc
261     {
262         // Create the threads
263
Thread JavaDoc[] threadList = new Thread JavaDoc[NUM_OF_THREADS];
264         s_conn = new Connection[NUM_OF_THREADS];
265
266         if (s_cType == C_SHARED)
267             s_sconn = DriverManager.getConnection (CONNECTION, UID, PWD);
268         //
269
// spawn threads
270
for (int i = 0; i < NUM_OF_THREADS; i++)
271         {
272             if (s_cType == C_PRECREATED)
273                 s_conn[i] = DriverManager.getConnection (CONNECTION, UID, PWD);
274             //
275
threadList[i] = new JdbcTest(i);
276             threadList[i].start();
277         }
278         // Start everyone at the same time
279
long start = System.currentTimeMillis();
280         setGreenLight ();
281         // wait for all threads to end
282
for (int i = 0; i < NUM_OF_THREADS; i++)
283             threadList[i].join();
284         //
285
if (s_sconn != null)
286             s_sconn.close();
287         s_sconn = null;
288         for (int i = 0; i < NUM_OF_THREADS; i++)
289         {
290             if (s_conn[i] != null)
291                 s_conn[i].close();
292             s_conn[i] = null;
293         }
294         long result = System.currentTimeMillis() - start;
295         System.out.print (C_INFO[s_cType]
296             + "Threads=" + NUM_OF_THREADS
297             + " \tYield=" + s_do_yield
298             + " \tms= " + result
299             + " \teach= " + (result/NUM_OF_THREADS));
300         if (s_cType == C_CACHE)
301             System.out.print (" \tCacheSize=" + s_cc.getCacheSize() + ", Active=" + s_cc.getActiveSize());
302         System.out.println();
303     } // runTest
304

305
306     /**
307      * Statement Timing
308      */

309     private static void statementTiming()
310     {
311         try
312         {
313             long startConnection = System.currentTimeMillis();
314             Connection conn = null;
315             if (s_cType == C_MULTIPLE)
316                 conn = DriverManager.getConnection (CONNECTION, UID, PWD);
317             if (s_cType == C_DATASOURCE)
318                 conn = s_ds.getConnection();
319             if (s_cType == C_CACHE)
320                 conn = s_cc.getConnection();
321
322             long startStatement = System.currentTimeMillis();
323             Statement stmt = conn.createStatement ();
324             stmt.setFetchSize(s_fetchSize);
325
326             long startQuery = System.currentTimeMillis();
327             ResultSet rs = stmt.executeQuery (STATEMENT);
328
329             int i = 0;
330             long startRetrieve = System.currentTimeMillis();
331             while (rs.next())
332             {
333                 rs.getString(1);
334                 i++;
335             }
336             long endRetrieve = System.currentTimeMillis();
337         // System.out.println(i);
338

339             rs.close();
340             rs = null;
341             long endQuery = System.currentTimeMillis();
342
343             stmt.close();
344             stmt = null;
345             long endStatement = System.currentTimeMillis();
346
347             conn.close();
348             conn = null;
349             long endConnection = System.currentTimeMillis();
350
351             //
352
System.out.println(C_INFO[s_cType]
353                 + "Fetch=" + s_fetchSize
354                 + " \tConn=" + (startStatement - startConnection)
355                 + " \tStmt=" + (startQuery - startStatement)
356                 + " \tQuery=" + (startRetrieve - startQuery)
357                 + " \tRetrieve=" + (endRetrieve - startRetrieve)
358                 + " \tClRs=" + (endQuery - endRetrieve)
359                 + " \tClStmt=" + (endStatement - endQuery)
360                 + " \tClConn=" + (endConnection - endStatement)
361                 + " \t- Total=" + (endConnection - startConnection)
362                 + " \tStmt=" + (endStatement - startStatement)
363                 + " \tQuery=" + (endQuery - startQuery));
364         }
365         catch (SQLException e)
366         {
367             e.printStackTrace();
368         }
369     } // statementTiming
370

371     /**
372      * Row Set Timing
373      */

374     private static void rowSetTiming()
375     {
376         try
377         {
378             long startConnection = System.currentTimeMillis();
379             RowSet rowset = null;
380             /**
381             if (s_rType == R_JDBC_ROWSET)
382                 rowset = new OracleJDBCRowSet ();
383             else if (s_rType == R_CACHED_ROWSET)
384                 rowset = new OracleCachedRowSet();
385             **/

386             rowset.setUrl (CONNECTION);
387             rowset.setUsername (UID);
388             rowset.setPassword (PWD);
389             rowset.setFetchSize(s_fetchSize);
390
391             long startStatement = System.currentTimeMillis();
392             rowset.setCommand (STATEMENT);
393
394             long startQuery = System.currentTimeMillis();
395             rowset.execute ();
396
397             long startRetrieve = System.currentTimeMillis();
398             while (rowset.next ())
399             {
400             }
401             long endRetrieve = System.currentTimeMillis();
402             long endQuery = System.currentTimeMillis();
403
404             rowset.close();
405             long endStatement = System.currentTimeMillis();
406             long endConnection = System.currentTimeMillis();
407             //
408
System.out.println(R_INFO[s_rType]
409                 + "Fetch=" + s_fetchSize
410                 + " \tConn=" + (startStatement - startConnection)
411                 + " \tStmt=" + (startQuery - startStatement)
412                 + " \tQuery=" + (startRetrieve - startQuery)
413                 + " \tRetrieve=" + (endRetrieve - startRetrieve)
414                 + " \tClRs=" + (endQuery - endRetrieve)
415                 + " \tClStmt=" + (endStatement - endQuery)
416                 + " \tClConn=" + (endConnection - endStatement)
417                 + " \t- Total=" + (endConnection - startConnection)
418                 + " \tStmt=" + (endStatement - startStatement)
419                 + " \tQuery=" + (endQuery - startQuery));
420         }
421         catch (SQLException e)
422         {
423             e.printStackTrace();
424         }
425     } // rowSetTiming
426

427     /*************************************************************************/
428
429     /**
430      * JDBC Test
431      * @param id Thread ID
432      */

433     public JdbcTest(int id)
434     {
435         super();
436         m_myId = id;
437     } // JdbcTest
438

439     private int m_myId = 0;
440
441     /**
442      * Async Worker
443      */

444     public void run()
445     {
446         ResultSet rs = null;
447         Statement stmt = null;
448
449         try
450         {
451             while (!getGreenLight())
452                 yield();
453             if (WITH_OUTPUT)
454                 System.out.println("Thread " + m_myId + " started");
455
456             // Get the connection & statement
457
if (s_cType == C_SHARED)
458                 stmt = s_sconn.createStatement ();
459             else if (s_cType == C_MULTIPLE)
460             {
461                 s_conn[m_myId] = DriverManager.getConnection (CONNECTION, UID, PWD);
462                 stmt = s_conn[m_myId].createStatement ();
463             }
464             else if (s_cType == C_PRECREATED)
465             {
466                 stmt = s_conn[m_myId].createStatement ();
467             }
468             else if (s_cType == C_DATASOURCE)
469             {
470                 s_conn[m_myId] = s_ds.getConnection();
471                 stmt = s_conn[m_myId].createStatement ();
472             }
473             else if (s_cType == C_CACHE)
474             {
475                 s_conn[m_myId] = s_cc.getConnection();
476                 stmt = s_conn[m_myId].createStatement ();
477             }
478             stmt.setFetchSize(s_fetchSize);
479
480             // Execute the Query
481
rs = stmt.executeQuery (STATEMENT);
482
483             // Loop through the results
484
while (rs.next())
485             {
486                 if (s_do_yield)
487                     yield(); // Yield To other threads
488
}
489
490             // Close all the resources
491
rs.close();
492             rs = null;
493
494             // Close the statement
495
stmt.close();
496             stmt = null;
497
498             // Close the local connection
499
if (s_cType == C_SHARED || s_cType == C_PRECREATED)
500                 ;
501             else
502             {
503                 s_conn[m_myId].close();
504                 s_conn[m_myId] = null;
505             }
506         }
507         catch (Exception JavaDoc e)
508         {
509             System.out.println("Thread " + m_myId + " got Exception: " + e);
510             e.printStackTrace();
511             return;
512         }
513         if (WITH_OUTPUT)
514             System.out.println("Thread " + m_myId + " finished");
515     }
516
517     /*************************************************************************/
518
519     static boolean greenLight = false;
520     static synchronized void setGreenLight () { greenLight = true; }
521     synchronized boolean getGreenLight () { return greenLight; }
522
523 } // JdbcTest
524
Popular Tags