001: /*
002: Copyright (C) 2002-2004 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.regression;
026:
027: import java.sql.Date;
028: import java.sql.PreparedStatement;
029: import java.sql.Time;
030: import java.sql.Timestamp;
031: import java.util.HashMap;
032: import java.util.Locale;
033: import java.util.Map;
034:
035: import testsuite.BaseTestCase;
036:
037: /**
038: * Microperformance benchmarks to track increase/decrease in performance of core
039: * methods in the driver over time.
040: *
041: * @author Mark Matthews
042: *
043: * @version $Id: MicroPerformanceRegressionTest.java,v 1.1.2.1 2005/05/13
044: * 18:58:38 mmatthews Exp $
045: */
046: public class MicroPerformanceRegressionTest extends BaseTestCase {
047:
048: private double scaleFactor = 1.0;
049:
050: private final static int ORIGINAL_LOOP_TIME_MS = 2300;
051:
052: private final static double LEEWAY = 3.0;
053:
054: private final static Map BASELINE_TIMES = new HashMap();
055:
056: static {
057: BASELINE_TIMES.put("ResultSet.getInt()", new Double(0.00661));
058: BASELINE_TIMES
059: .put("ResultSet.getDouble()", new Double(0.00671));
060: BASELINE_TIMES.put("ResultSet.getTime()", new Double(0.02033));
061: BASELINE_TIMES.put("ResultSet.getTimestamp()", new Double(
062: 0.02363));
063: BASELINE_TIMES.put("ResultSet.getDate()", new Double(0.02223));
064: BASELINE_TIMES
065: .put("ResultSet.getString()", new Double(0.00982));
066: BASELINE_TIMES.put("ResultSet.getObject() on a string",
067: new Double(0.00861));
068: BASELINE_TIMES.put("Connection.prepareStatement()", new Double(
069: 0.18547));
070: BASELINE_TIMES.put("PreparedStatement.setInt()", new Double(
071: 0.0011));
072: BASELINE_TIMES.put("PreparedStatement.setDouble()", new Double(
073: 0.00671));
074: BASELINE_TIMES.put("PreparedStatement.setTime()", new Double(
075: 0.0642));
076: BASELINE_TIMES.put("PreparedStatement.setTimestamp()",
077: new Double(0.03184));
078: BASELINE_TIMES.put("PreparedStatement.setDate()", new Double(
079: 0.12248));
080: BASELINE_TIMES.put("PreparedStatement.setString()", new Double(
081: 0.01512));
082: BASELINE_TIMES.put("PreparedStatement.setObject() on a string",
083: new Double(0.01923));
084: BASELINE_TIMES.put("single selects", new Double(46));
085: BASELINE_TIMES.put("5 standalone queries", new Double(146));
086: BASELINE_TIMES.put("total time all queries", new Double(190));
087: }
088:
089: public MicroPerformanceRegressionTest(String name) {
090: super (name);
091: }
092:
093: /**
094: * Runs all test cases in this test suite
095: *
096: * @param args
097: */
098: public static void main(String[] args) {
099: junit.textui.TestRunner
100: .run(MicroPerformanceRegressionTest.class);
101: }
102:
103: /**
104: * Tests result set accessors performance.
105: *
106: * @throws Exception
107: * if the performance of these methods does not meet
108: * expectations.
109: */
110: public void testResultSetAccessors() throws Exception {
111: try {
112: this .stmt.executeUpdate("DROP TABLE IF EXISTS marktest");
113: this .stmt
114: .executeUpdate("CREATE TABLE marktest(intField INT, floatField DOUBLE, timeField TIME, datetimeField DATETIME, stringField VARCHAR(64))");
115: this .stmt
116: .executeUpdate("INSERT INTO marktest VALUES (123456789, 12345.6789, NOW(), NOW(), 'abcdefghijklmnopqrstuvABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@')");
117:
118: this .rs = this .stmt
119: .executeQuery("SELECT intField, floatField, timeField, datetimeField, stringField FROM marktest");
120:
121: this .rs.next();
122:
123: int numLoops = 100000;
124:
125: long start = System.currentTimeMillis();
126:
127: for (int i = 0; i < numLoops; i++) {
128: this .rs.getInt(1);
129: }
130:
131: double getIntAvgMs = (double) (System.currentTimeMillis() - start)
132: / numLoops;
133:
134: checkTime("ResultSet.getInt()", getIntAvgMs);
135:
136: start = System.currentTimeMillis();
137:
138: for (int i = 0; i < numLoops; i++) {
139: this .rs.getDouble(2);
140: }
141:
142: double getDoubleAvgMs = (double) (System
143: .currentTimeMillis() - start)
144: / numLoops;
145:
146: checkTime("ResultSet.getDouble()", getDoubleAvgMs);
147:
148: start = System.currentTimeMillis();
149:
150: for (int i = 0; i < numLoops; i++) {
151: this .rs.getTime(3);
152: }
153:
154: double getTimeAvgMs = (double) (System.currentTimeMillis() - start)
155: / numLoops;
156:
157: checkTime("ResultSet.getTime()", getTimeAvgMs);
158:
159: start = System.currentTimeMillis();
160:
161: for (int i = 0; i < numLoops; i++) {
162: this .rs.getTimestamp(4);
163: }
164:
165: double getTimestampAvgMs = (double) (System
166: .currentTimeMillis() - start)
167: / numLoops;
168:
169: checkTime("ResultSet.getTimestamp()", getTimestampAvgMs);
170:
171: start = System.currentTimeMillis();
172:
173: for (int i = 0; i < numLoops; i++) {
174: this .rs.getDate(4);
175: }
176:
177: double getDateAvgMs = (double) (System.currentTimeMillis() - start)
178: / numLoops;
179:
180: checkTime("ResultSet.getDate()", getDateAvgMs);
181:
182: start = System.currentTimeMillis();
183:
184: for (int i = 0; i < numLoops; i++) {
185: this .rs.getString(5);
186: }
187:
188: double getStringAvgMs = (double) (System
189: .currentTimeMillis() - start)
190: / numLoops;
191:
192: checkTime("ResultSet.getString()", getStringAvgMs);
193:
194: start = System.currentTimeMillis();
195:
196: for (int i = 0; i < numLoops; i++) {
197: this .rs.getObject(5);
198: }
199:
200: double getStringObjAvgMs = (double) (System
201: .currentTimeMillis() - start)
202: / numLoops;
203:
204: checkTime("ResultSet.getObject() on a string",
205: getStringObjAvgMs);
206: } finally {
207: this .stmt.executeUpdate("DROP TABLE IF EXISTS marktest");
208: }
209: }
210:
211: public void testPreparedStatementTimes() throws Exception {
212: try {
213: this .stmt.executeUpdate("DROP TABLE IF EXISTS marktest");
214: this .stmt
215: .executeUpdate("CREATE TABLE marktest(intField INT, floatField DOUBLE, timeField TIME, datetimeField DATETIME, stringField VARCHAR(64))");
216: this .stmt
217: .executeUpdate("INSERT INTO marktest VALUES (123456789, 12345.6789, NOW(), NOW(), 'abcdefghijklmnopqrstuvABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@')");
218:
219: long start = System.currentTimeMillis();
220:
221: long blockStart = System.currentTimeMillis();
222: long lastBlock = 0;
223:
224: int numLoops = 100000;
225:
226: int numPrepares = 100000;
227:
228: if (versionMeetsMinimum(4, 1)) {
229: numPrepares = 10000; // we don't need to do so many for
230: // server-side prep statements...
231: }
232:
233: for (int i = 0; i < numPrepares; i++) {
234: if (i % 1000 == 0) {
235:
236: long blockEnd = System.currentTimeMillis();
237:
238: long totalTime = blockEnd - blockStart;
239:
240: blockStart = blockEnd;
241:
242: StringBuffer messageBuf = new StringBuffer();
243:
244: messageBuf.append(i
245: + " prepares, the last 1000 prepares took "
246: + totalTime + " ms");
247:
248: if (lastBlock == 0) {
249: lastBlock = totalTime;
250: messageBuf.append(".");
251: } else {
252: double diff = (double) totalTime
253: / (double) lastBlock;
254:
255: messageBuf.append(", difference is " + diff
256: + " x");
257:
258: lastBlock = totalTime;
259: }
260:
261: System.out.println(messageBuf.toString());
262:
263: }
264:
265: PreparedStatement pStmt = this .conn
266: .prepareStatement("INSERT INTO test.marktest VALUES (?, ?, ?, ?, ?)");
267: pStmt.close();
268: }
269:
270: double getPrepareStmtAvgMs = (double) (System
271: .currentTimeMillis() - start)
272: / numPrepares;
273:
274: // checkTime("Connection.prepareStatement()", getPrepareStmtAvgMs);
275:
276: PreparedStatement pStmt = this .conn
277: .prepareStatement("INSERT INTO marktest VALUES (?, ?, ?, ?, ?)");
278:
279: System.out.println(pStmt.toString());
280:
281: start = System.currentTimeMillis();
282:
283: for (int i = 0; i < numLoops; i++) {
284: pStmt.setInt(1, 1);
285: }
286:
287: System.out.println(pStmt.toString());
288:
289: double setIntAvgMs = (double) (System.currentTimeMillis() - start)
290: / numLoops;
291:
292: checkTime("PreparedStatement.setInt()", setIntAvgMs);
293:
294: start = System.currentTimeMillis();
295:
296: for (int i = 0; i < numLoops; i++) {
297: pStmt.setDouble(2, 1234567890.1234);
298: }
299:
300: double setDoubleAvgMs = (double) (System
301: .currentTimeMillis() - start)
302: / numLoops;
303:
304: checkTime("PreparedStatement.setDouble()", setDoubleAvgMs);
305:
306: start = System.currentTimeMillis();
307:
308: Time tm = new Time(start);
309:
310: for (int i = 0; i < numLoops; i++) {
311: pStmt.setTime(3, tm);
312: }
313:
314: double setTimeAvgMs = (double) (System.currentTimeMillis() - start)
315: / numLoops;
316:
317: checkTime("PreparedStatement.setTime()", setTimeAvgMs);
318:
319: start = System.currentTimeMillis();
320:
321: Timestamp ts = new Timestamp(start);
322:
323: for (int i = 0; i < numLoops; i++) {
324: pStmt.setTimestamp(4, ts);
325: }
326:
327: double setTimestampAvgMs = (double) (System
328: .currentTimeMillis() - start)
329: / numLoops;
330:
331: checkTime("PreparedStatement.setTimestamp()",
332: setTimestampAvgMs);
333:
334: start = System.currentTimeMillis();
335:
336: Date dt = new Date(start);
337:
338: for (int i = 0; i < numLoops; i++) {
339: pStmt.setDate(4, dt);
340: }
341:
342: double setDateAvgMs = (double) (System.currentTimeMillis() - start)
343: / numLoops;
344:
345: checkTime("PreparedStatement.setDate()", setDateAvgMs);
346:
347: start = System.currentTimeMillis();
348:
349: for (int i = 0; i < numLoops; i++) {
350: pStmt
351: .setString(5,
352: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@");
353: }
354:
355: double setStringAvgMs = (double) (System
356: .currentTimeMillis() - start)
357: / numLoops;
358:
359: checkTime("PreparedStatement.setString()", setStringAvgMs);
360:
361: start = System.currentTimeMillis();
362:
363: for (int i = 0; i < numLoops; i++) {
364: pStmt
365: .setObject(5,
366: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@");
367: }
368:
369: double setStringObjAvgMs = (double) (System
370: .currentTimeMillis() - start)
371: / numLoops;
372:
373: checkTime("PreparedStatement.setObject() on a string",
374: setStringObjAvgMs);
375:
376: start = System.currentTimeMillis();
377:
378: } finally {
379: this .stmt.executeUpdate("DROP TABLE IF EXISTS marktest");
380: }
381: }
382:
383: /*
384: * (non-Javadoc)
385: *
386: * @see junit.framework.TestCase#setUp()
387: */
388: public void setUp() throws Exception {
389: super .setUp();
390:
391: System.out.println("Calculating performance scaling factor...");
392: // Run this simple test to get some sort of performance scaling factor,
393: // compared to
394: // the development environment. This should help reduce false-positives
395: // on this test.
396: int numLoops = 10000;
397:
398: long start = System.currentTimeMillis();
399:
400: for (int j = 0; j < 2000; j++) {
401: StringBuffer buf = new StringBuffer(numLoops);
402:
403: for (int i = 0; i < numLoops; i++) {
404: buf.append('a');
405: }
406: }
407:
408: long elapsedTime = System.currentTimeMillis() - start;
409:
410: System.out.println("Elapsed time for factor: " + elapsedTime);
411:
412: this .scaleFactor = (double) elapsedTime
413: / (double) ORIGINAL_LOOP_TIME_MS;
414:
415: System.out.println("Performance scaling factor is: "
416: + this .scaleFactor);
417: }
418:
419: private void checkTime(String testType, double avgExecTimeMs)
420: throws Exception {
421:
422: double adjustForVendor = 1.0D;
423:
424: if (isRunningOnJRockit()) {
425: adjustForVendor = 4.0D;
426: }
427:
428: Double baselineExecTimeMs = (Double) BASELINE_TIMES
429: .get(testType);
430:
431: if (baselineExecTimeMs == null) {
432: throw new Exception("No baseline time recorded for test '"
433: + testType + "'");
434: }
435:
436: double acceptableTime = LEEWAY
437: * baselineExecTimeMs.doubleValue() * this .scaleFactor
438: * adjustForVendor;
439:
440: assertTrue("Average execution time of " + avgExecTimeMs
441: + " ms. exceeded baseline * leeway of "
442: + acceptableTime + " ms.",
443: (avgExecTimeMs <= acceptableTime));
444: }
445:
446: public void testBug6359() throws Exception {
447: if (runLongTests()) {
448: int numRows = 550000;
449: int numSelects = 100000;
450:
451: try {
452: this .stmt
453: .executeUpdate("DROP TABLE IF EXISTS testBug6359");
454: this .stmt
455: .executeUpdate("CREATE TABLE testBug6359 (pk_field INT PRIMARY KEY NOT NULL AUTO_INCREMENT, field1 INT, field2 INT, field3 INT, field4 INT, field5 INT, field6 INT, field7 INT, field8 INT, field9 INT, INDEX (field1))");
456:
457: PreparedStatement pStmt = this .conn
458: .prepareStatement("INSERT INTO testBug6359 (field1, field2, field3, field4, field5, field6, field7, field8, field9) VALUES (?, 1, 2, 3, 4, 5, 6, 7, 8)");
459:
460: logDebug("Loading " + numRows + " rows...");
461:
462: for (int i = 0; i < numRows; i++) {
463: pStmt.setInt(1, i);
464: pStmt.executeUpdate();
465:
466: if ((i % 10000) == 0) {
467: logDebug(i + " rows loaded so far");
468: }
469: }
470:
471: logDebug("Finished loading rows");
472:
473: long begin = System.currentTimeMillis();
474:
475: long beginSingleQuery = System.currentTimeMillis();
476:
477: for (int i = 0; i < numSelects; i++) {
478: this .rs = this .stmt
479: .executeQuery("SELECT pk_field FROM testBug6359 WHERE field1 BETWEEN 1 AND 5");
480: }
481:
482: long endSingleQuery = System.currentTimeMillis();
483:
484: double secondsSingleQuery = ((double) endSingleQuery - (double) beginSingleQuery) / 1000;
485:
486: logDebug("time to execute " + numSelects
487: + " single queries: " + secondsSingleQuery
488: + " seconds");
489:
490: checkTime("single selects", secondsSingleQuery);
491:
492: PreparedStatement pStmt2 = this .conn
493: .prepareStatement("SELECT field2, field3, field4, field5 FROM testBug6359 WHERE pk_field=?");
494:
495: long beginFiveQueries = System.currentTimeMillis();
496:
497: for (int i = 0; i < numSelects; i++) {
498:
499: for (int j = 0; j < 5; j++) {
500: pStmt2.setInt(1, j);
501: pStmt2.executeQuery();
502: }
503: }
504:
505: long endFiveQueries = System.currentTimeMillis();
506:
507: double secondsFiveQueries = ((double) endFiveQueries - (double) beginFiveQueries) / 1000;
508:
509: logDebug("time to execute " + numSelects
510: + " 5 standalone queries: "
511: + secondsFiveQueries + " seconds");
512:
513: checkTime("5 standalone queries", secondsFiveQueries);
514:
515: long end = System.currentTimeMillis();
516:
517: double seconds = ((double) end - (double) begin) / 1000;
518:
519: logDebug("time to execute " + numSelects + " selects: "
520: + seconds + " seconds");
521:
522: checkTime("total time all queries", seconds);
523: } finally {
524: this .stmt
525: .executeUpdate("DROP TABLE IF EXISTS testBug6359");
526: }
527: }
528: }
529:
530: }
|