|
1
25 package testsuite.regression;
26
27 import java.sql.Date  ;
28 import java.sql.PreparedStatement ;
29 import java.sql.Time ;
30 import java.sql.Timestamp ;
31 import java.util.HashMap ;
32 import java.util.Map ;
33
34 import testsuite.BaseTestCase;
35
36
45 public class MicroPerformanceRegressionTest extends BaseTestCase {
46
47 private double scaleFactor = 1.0;
48
49 private final static int ORIGINAL_LOOP_TIME_MS = 2300;
50
51 private final static double LEEWAY = 3.0;
52
53 private final static Map BASELINE_TIMES = new HashMap ();
54
55 static {
56 BASELINE_TIMES.put("ResultSet.getInt()", new Double (0.00661));
57 BASELINE_TIMES.put("ResultSet.getDouble()", new Double (0.00671));
58 BASELINE_TIMES.put("ResultSet.getTime()", new Double (0.02033));
59 BASELINE_TIMES.put("ResultSet.getTimestamp()", new Double (0.02363));
60 BASELINE_TIMES.put("ResultSet.getDate()", new Double (0.02223));
61 BASELINE_TIMES.put("ResultSet.getString()", new Double (0.00982));
62 BASELINE_TIMES.put("ResultSet.getObject() on a string", new Double (
63 0.00861));
64 BASELINE_TIMES
65 .put("Connection.prepareStatement()", new Double (0.18547));
66 BASELINE_TIMES.put("PreparedStatement.setInt()", new Double (0.0011));
67 BASELINE_TIMES
68 .put("PreparedStatement.setDouble()", new Double (0.00671));
69 BASELINE_TIMES.put("PreparedStatement.setTime()", new Double (0.0642));
70 BASELINE_TIMES.put("PreparedStatement.setTimestamp()", new Double (
71 0.03184));
72 BASELINE_TIMES.put("PreparedStatement.setDate()", new Double (0.12248));
73 BASELINE_TIMES
74 .put("PreparedStatement.setString()", new Double (0.01512));
75 BASELINE_TIMES.put("PreparedStatement.setObject() on a string",
76 new Double (0.01923));
77 BASELINE_TIMES.put("single selects", new Double (46));
78 BASELINE_TIMES.put("5 standalone queries", new Double (146));
79 BASELINE_TIMES.put("total time all queries", new Double (190));
80 }
81
82 public MicroPerformanceRegressionTest(String name) {
83 super(name);
84 }
85
86
91 public static void main(String [] args) {
92 junit.textui.TestRunner.run(MicroPerformanceRegressionTest.class);
93 }
94
95
102 public void testResultSetAccessors() throws Exception {
103 try {
104 this.stmt.executeUpdate("DROP TABLE IF EXISTS marktest");
105 this.stmt
106 .executeUpdate("CREATE TABLE marktest(intField INT, floatField DOUBLE, timeField TIME, datetimeField DATETIME, stringField VARCHAR(64))");
107 this.stmt
108 .executeUpdate("INSERT INTO marktest VALUES (123456789, 12345.6789, NOW(), NOW(), 'abcdefghijklmnopqrstuvABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@')");
109
110 this.rs = this.stmt
111 .executeQuery("SELECT intField, floatField, timeField, datetimeField, stringField FROM marktest");
112
113 this.rs.next();
114
115 int numLoops = 100000;
116
117 long start = System.currentTimeMillis();
118
119 for (int i = 0; i < numLoops; i++) {
120 this.rs.getInt(1);
121 }
122
123 double getIntAvgMs = (double) (System.currentTimeMillis() - start)
124 / numLoops;
125
126 checkTime("ResultSet.getInt()", getIntAvgMs);
127
128 start = System.currentTimeMillis();
129
130 for (int i = 0; i < numLoops; i++) {
131 this.rs.getDouble(2);
132 }
133
134 double getDoubleAvgMs = (double) (System.currentTimeMillis() - start)
135 / numLoops;
136
137 checkTime("ResultSet.getDouble()", getDoubleAvgMs);
138
139 start = System.currentTimeMillis();
140
141 for (int i = 0; i < numLoops; i++) {
142 this.rs.getTime(3);
143 }
144
145 double getTimeAvgMs = (double) (System.currentTimeMillis() - start)
146 / numLoops;
147
148 checkTime("ResultSet.getTime()", getTimeAvgMs);
149
150 start = System.currentTimeMillis();
151
152 for (int i = 0; i < numLoops; i++) {
153 this.rs.getTimestamp(4);
154 }
155
156 double getTimestampAvgMs = (double) (System.currentTimeMillis() - start)
157 / numLoops;
158
159 checkTime("ResultSet.getTimestamp()", getTimestampAvgMs);
160
161 start = System.currentTimeMillis();
162
163 for (int i = 0; i < numLoops; i++) {
164 this.rs.getDate(4);
165 }
166
167 double getDateAvgMs = (double) (System.currentTimeMillis() - start)
168 / numLoops;
169
170 checkTime("ResultSet.getDate()", getDateAvgMs);
171
172 start = System.currentTimeMillis();
173
174 for (int i = 0; i < numLoops; i++) {
175 this.rs.getString(5);
176 }
177
178 double getStringAvgMs = (double) (System.currentTimeMillis() - start)
179 / numLoops;
180
181 checkTime("ResultSet.getString()", getStringAvgMs);
182
183 start = System.currentTimeMillis();
184
185 for (int i = 0; i < numLoops; i++) {
186 this.rs.getObject(5);
187 }
188
189 double getStringObjAvgMs = (double) (System.currentTimeMillis() - start)
190 / numLoops;
191
192 checkTime("ResultSet.getObject() on a string", getStringObjAvgMs);
193
194 start = System.currentTimeMillis();
195
196 long blockStart = System.currentTimeMillis();
197 long lastBlock = 0;
198
199 int numPrepares = 100000;
200
201 if (versionMeetsMinimum(4, 1)) {
202 numPrepares = 100000; }
205
206 for (int i = 0; i < numPrepares; i++) {
207 if (i % 1000 == 0) {
208
209 long blockEnd = System.currentTimeMillis();
210
211 long totalTime = blockEnd - blockStart;
212
213 blockStart = blockEnd;
214
215 StringBuffer messageBuf = new StringBuffer ();
216
217 messageBuf.append(i
218 + " prepares, the last 1000 prepares took "
219 + totalTime + " ms");
220
221 if (lastBlock == 0) {
222 lastBlock = totalTime;
223 messageBuf.append(".");
224 } else {
225 double diff = (double) totalTime / (double) lastBlock;
226
227 messageBuf.append(", difference is " + diff + " x");
228
229 lastBlock = totalTime;
230 }
231
232 System.out.println(messageBuf.toString());
233
234 }
235
236 PreparedStatement pStmt = this.conn
237 .prepareStatement("INSERT INTO test.marktest VALUES (?, ?, ?, ?, ?)");
238 pStmt.close();
239 }
240
241 double getPrepareStmtAvgMs = (double) (System.currentTimeMillis() - start)
242 / numPrepares;
243
244
246 PreparedStatement pStmt = this.conn
247 .prepareStatement("INSERT INTO marktest VALUES (?, ?, ?, ?, ?)");
248
249 System.out.println(pStmt.toString());
250
251 start = System.currentTimeMillis();
252
253 for (int i = 0; i < numLoops; i++) {
254 pStmt.setInt(1, 1);
255 }
256
257 System.out.println(pStmt.toString());
258
259 double setIntAvgMs = (double) (System.currentTimeMillis() - start)
260 / numLoops;
261
262 checkTime("PreparedStatement.setInt()", setIntAvgMs);
263
264 start = System.currentTimeMillis();
265
266 for (int i = 0; i < numLoops; i++) {
267 pStmt.setDouble(2, 1234567890.1234);
268 }
269
270 double setDoubleAvgMs = (double) (System.currentTimeMillis() - start)
271 / numLoops;
272
273 checkTime("PreparedStatement.setDouble()", getDoubleAvgMs);
274
275 start = System.currentTimeMillis();
276
277 Time tm = new Time (start);
278
279 for (int i = 0; i < numLoops; i++) {
280 pStmt.setTime(3, tm);
281 }
282
283 double setTimeAvgMs = (double) (System.currentTimeMillis() - start)
284 / numLoops;
285
286 checkTime("PreparedStatement.setTime()", setTimeAvgMs);
287
288 start = System.currentTimeMillis();
289
290 Timestamp ts = new Timestamp (start);
291
292 for (int i = 0; i < numLoops; i++) {
293 pStmt.setTimestamp(4, ts);
294 }
295
296 double setTimestampAvgMs = (double) (System.currentTimeMillis() - start)
297 / numLoops;
298
299 checkTime("PreparedStatement.setTimestamp()", setTimestampAvgMs);
300
301 start = System.currentTimeMillis();
302
303 Date dt = new Date (start);
304
305 for (int i = 0; i < numLoops; i++) {
306 pStmt.setDate(4, dt);
307 }
308
309 double setDateAvgMs = (double) (System.currentTimeMillis() - start)
310 / numLoops;
311
312 checkTime("PreparedStatement.setDate()", setDateAvgMs);
313
314 start = System.currentTimeMillis();
315
316 for (int i = 0; i < numLoops; i++) {
317 pStmt
318 .setString(5,
319 "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@");
320 }
321
322 double setStringAvgMs = (double) (System.currentTimeMillis() - start)
323 / numLoops;
324
325 checkTime("PreparedStatement.setString()", setStringAvgMs);
326
327 start = System.currentTimeMillis();
328
329 for (int i = 0; i < numLoops; i++) {
330 pStmt
331 .setObject(5,
332 "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@");
333 }
334
335 double setStringObjAvgMs = (double) (System.currentTimeMillis() - start)
336 / numLoops;
337
338 checkTime("PreparedStatement.setObject() on a string",
339 setStringObjAvgMs);
340
341 start = System.currentTimeMillis();
342
343 } finally {
344 this.stmt.executeUpdate("DROP TABLE IF EXISTS marktest");
345 }
346 }
347
348
353 public void setUp() throws Exception {
354 super.setUp();
355
356 System.out.println("Calculating performance scaling factor...");
357 int numLoops = 10000;
362
363 long start = System.currentTimeMillis();
364
365 for (int j = 0; j < 2000; j++) {
366 StringBuffer buf = new StringBuffer (numLoops);
367
368 for (int i = 0; i < numLoops; i++) {
369 buf.append('a');
370 }
371 }
372
373 long elapsedTime = System.currentTimeMillis() - start;
374
375 System.out.println("Elapsed time for factor: " + elapsedTime);
376
377 this.scaleFactor = (double) elapsedTime
378 / (double) ORIGINAL_LOOP_TIME_MS;
379
380 System.out
381 .println("Performance scaling factor is: " + this.scaleFactor);
382 }
383
384 private void checkTime(String testType, double avgExecTimeMs)
385 throws Exception {
386 System.out.println("Execution time for " + testType + ": "
387 + avgExecTimeMs);
388
389 Double baselineExecTimeMs = (Double ) BASELINE_TIMES.get(testType);
390
391 if (baselineExecTimeMs == null) {
392 throw new Exception ("No baseline time recorded for test '"
393 + testType + "'");
394 }
395
396 double acceptableTime = LEEWAY * baselineExecTimeMs.doubleValue()
397 * this.scaleFactor;
398
399 assertTrue("Average execution time of " + avgExecTimeMs
400 + " ms. exceeded baseline * leeway of " + acceptableTime
401 + " ms.", (avgExecTimeMs <= acceptableTime));
402 }
403
404 public void testBug6359() throws Exception {
405 if (runLongTests()) {
406 int numRows = 550000;
407 int numSelects = 100000;
408
409 try {
410 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6359");
411 this.stmt
412 .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))");
413
414 PreparedStatement pStmt = this.conn
415 .prepareStatement("INSERT INTO testBug6359 (field1, field2, field3, field4, field5, field6, field7, field8, field9) VALUES (?, 1, 2, 3, 4, 5, 6, 7, 8)");
416
417 logDebug("Loading " + numRows + " rows...");
418
419 for (int i = 0; i < numRows; i++) {
420 pStmt.setInt(1, i);
421 pStmt.executeUpdate();
422
423 if ((i % 10000) == 0) {
424 logDebug(i + " rows loaded so far");
425 }
426 }
427
428 logDebug("Finished loading rows");
429
430 long begin = System.currentTimeMillis();
431
432 long beginSingleQuery = System.currentTimeMillis();
433
434 for (int i = 0; i < numSelects; i++) {
435 this.rs = this.stmt
436 .executeQuery("SELECT pk_field FROM testBug6359 WHERE field1 BETWEEN 1 AND 5");
437 }
438
439 long endSingleQuery = System.currentTimeMillis();
440
441 double secondsSingleQuery = ((double) endSingleQuery - (double) beginSingleQuery) / 1000;
442
443 logDebug("time to execute " + numSelects + " single queries: "
444 + secondsSingleQuery + " seconds");
445
446 checkTime("single selects", secondsSingleQuery);
447
448 PreparedStatement pStmt2 = this.conn
449 .prepareStatement("SELECT field2, field3, field4, field5 FROM testBug6359 WHERE pk_field=?");
450
451 long beginFiveQueries = System.currentTimeMillis();
452
453 for (int i = 0; i < numSelects; i++) {
454
455 for (int j = 0; j < 5; j++) {
456 pStmt2.setInt(1, j);
457 pStmt2.executeQuery();
458 }
459 }
460
461 long endFiveQueries = System.currentTimeMillis();
462
463 double secondsFiveQueries = ((double) endFiveQueries - (double) beginFiveQueries) / 1000;
464
465 logDebug("time to execute " + numSelects
466 + " 5 standalone queries: " + secondsFiveQueries
467 + " seconds");
468
469 checkTime("5 standalone queries", secondsFiveQueries);
470
471 long end = System.currentTimeMillis();
472
473 double seconds = ((double) end - (double) begin) / 1000;
474
475 logDebug("time to execute " + numSelects + " selects: "
476 + seconds + " seconds");
477
478 checkTime("total time all queries", seconds);
479 } finally {
480 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6359");
481 }
482 }
483 }
484
485 }
486 |
Popular Tags |
Java API By Example, From Geeks To Geeks.