1 /* 2 * Copyright (C) 2007 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package android.database; 18 19 import android.content.ContentValues; 20 import android.database.sqlite.SQLiteDatabase; 21 import android.os.SystemProperties; 22 import android.test.PerformanceTestCase; 23 import android.util.ArrayMap; 24 import android.util.Log; 25 26 import junit.framework.TestCase; 27 28 import java.io.File; 29 import java.io.IOException; 30 import java.nio.file.Files; 31 import java.util.List; 32 import java.util.Map; 33 import java.util.Random; 34 35 /** 36 * Database Performance Tests. 37 * 38 * <p>Usage: 39 * <code>./frameworks/base/core/tests/coretests/src/android/database/run_newdb_perf_test.sh</code> 40 * <p>Test with WAL journaling enabled: 41 * <code>setprop debug.NewDatabasePerformanceTests.enable_wal 1</code> 42 */ 43 public class NewDatabasePerformanceTests { 44 private static final String TAG = "NewDatabasePerformanceTests"; 45 46 private static final boolean DEBUG_ENABLE_WAL = SystemProperties 47 .getBoolean("debug.NewDatabasePerformanceTests.enable_wal", false); 48 49 private static final int DATASET_SIZE = 100; // Size of dataset to use for testing 50 private static final int FAST_OP_MULTIPLIER = 25; 51 private static final int FAST_OP_COUNT = FAST_OP_MULTIPLIER * DATASET_SIZE; 52 53 private static Long sInitialWriteBytes; 54 55 static { 56 sInitialWriteBytes = getIoStats().get("write_bytes"); 57 if (DEBUG_ENABLE_WAL) { 58 Log.i(TAG, "Testing with WAL enabled"); 59 } 60 } 61 62 public static class PerformanceBase extends TestCase 63 implements PerformanceTestCase { 64 protected static final int CURRENT_DATABASE_VERSION = 42; 65 protected SQLiteDatabase mDatabase; 66 protected File mDatabaseFile; 67 private long mSetupFinishedTime; 68 private Long mSetupWriteBytes; 69 70 public void setUp() { 71 long setupStarted = System.currentTimeMillis(); 72 mDatabaseFile = new File("/sdcard", "perf_database_test.db"); 73 if (mDatabaseFile.exists()) { 74 SQLiteDatabase.deleteDatabase(mDatabaseFile); 75 } 76 SQLiteDatabase.OpenParams.Builder params = new SQLiteDatabase.OpenParams.Builder(); 77 params.addOpenFlags(SQLiteDatabase.CREATE_IF_NECESSARY); 78 if (DEBUG_ENABLE_WAL) { 79 params.addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING); 80 } 81 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params.build()); 82 if (DEBUG_ENABLE_WAL) { 83 assertTrue("Cannot enable WAL", mDatabase.isWriteAheadLoggingEnabled()); 84 } 85 mDatabase.setVersion(CURRENT_DATABASE_VERSION); 86 mDatabase.beginTransactionNonExclusive(); 87 prepareForTest(); 88 mDatabase.setTransactionSuccessful(); 89 mDatabase.endTransaction(); 90 mSetupFinishedTime = System.currentTimeMillis(); 91 Log.i(TAG, "Setup for " + getClass().getSimpleName() + " took " 92 + (mSetupFinishedTime - setupStarted) + " ms"); 93 mSetupWriteBytes = getIoStats().get("write_bytes"); 94 } 95 96 protected void prepareForTest() { 97 } 98 99 public void tearDown() { 100 long duration = System.currentTimeMillis() - mSetupFinishedTime; 101 Log.i(TAG, "Test " + getClass().getSimpleName() + " took " + duration + " ms"); 102 mDatabase.close(); 103 SQLiteDatabase.deleteDatabase(mDatabaseFile); 104 Long writeBytes = getIoStats().get("write_bytes"); 105 if (writeBytes != null && sInitialWriteBytes != null) { 106 long testWriteBytes = writeBytes - mSetupWriteBytes; 107 long totalWriteBytes = (writeBytes - sInitialWriteBytes); 108 Log.i(TAG, "Test " + getClass().getSimpleName() + " write_bytes=" + testWriteBytes 109 + ". Since tests started - totalWriteBytes=" + totalWriteBytes); 110 } 111 } 112 113 public boolean isPerformanceOnly() { 114 return true; 115 } 116 117 // These tests can only be run once. 118 public int startPerformance(Intermediates intermediates) { 119 return 0; 120 } 121 122 String numberName(int number) { 123 String result = ""; 124 125 if (number >= 1000) { 126 result += numberName((number / 1000)) + " thousand"; 127 number = (number % 1000); 128 129 if (number > 0) result += " "; 130 } 131 132 if (number >= 100) { 133 result += ONES[(number / 100)] + " hundred"; 134 number = (number % 100); 135 136 if (number > 0) result += " "; 137 } 138 139 if (number >= 20) { 140 result += TENS[(number / 10)]; 141 number = (number % 10); 142 143 if (number > 0) result += " "; 144 } 145 146 if (number > 0) { 147 result += ONES[number]; 148 } 149 150 return result; 151 } 152 153 void checkCursor(Cursor c) { 154 c.getColumnCount(); 155 c.close(); 156 } 157 } 158 159 /** 160 * Test CREATE SIZE tables with 1 row. 161 */ 162 public static class CreateTable100 extends PerformanceBase { 163 public void testRun() { 164 for (int i = 0; i < DATASET_SIZE; i++) { 165 String t = "t" + i; 166 mDatabase.execSQL("CREATE TABLE " + t + "(a INTEGER, b INTEGER, c VARCHAR(100))"); 167 mDatabase.execSQL("INSERT INTO " + t + " VALUES(" + i + "," + i + ",'" 168 + numberName(i) + "')"); 169 } 170 } 171 } 172 173 /** 174 * Test 100 inserts. 175 */ 176 public static class Insert100 extends PerformanceBase { 177 private String[] mStatements = new String[DATASET_SIZE]; 178 179 @Override 180 protected void prepareForTest() { 181 Random random = new Random(42); 182 183 for (int i = 0; i < DATASET_SIZE; i++) { 184 int r = random.nextInt(100000); 185 mStatements[i] = 186 "INSERT INTO t1 VALUES(" + i + "," + r + ",'" 187 + numberName(r) + "')"; 188 } 189 190 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 191 } 192 193 public void testRun() { 194 for (int i = 0; i < DATASET_SIZE; i++) { 195 mDatabase.execSQL(mStatements[i]); 196 } 197 } 198 } 199 200 /** 201 * Test 100 inserts into an indexed table. 202 */ 203 204 public static class InsertIndexed100 extends PerformanceBase { 205 private String[] mStatements = new String[DATASET_SIZE]; 206 207 @Override 208 protected void prepareForTest() { 209 Random random = new Random(42); 210 211 for (int i = 0; i < DATASET_SIZE; i++) { 212 int r = random.nextInt(100000); 213 mStatements[i] = "INSERT INTO t1 VALUES(" + i + "," + r + ",'" 214 + numberName(r) + "')"; 215 } 216 217 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 218 mDatabase.execSQL("CREATE INDEX i1c ON t1(c)"); 219 } 220 221 public void testRun() { 222 for (int i = 0; i < DATASET_SIZE; i++) { 223 mDatabase.execSQL(mStatements[i]); 224 } 225 } 226 } 227 228 /** 229 * 100 SELECTs without an index 230 */ 231 public static class Select100 extends PerformanceBase { 232 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 233 234 private String[] mWhere = new String[DATASET_SIZE]; 235 236 @Override 237 protected void prepareForTest() { 238 Random random = new Random(42); 239 240 mDatabase 241 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 242 243 for (int i = 0; i < DATASET_SIZE; i++) { 244 int r = random.nextInt(100000); 245 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 246 + numberName(r) + "')"); 247 } 248 249 for (int i = 0; i < DATASET_SIZE; i++) { 250 int lower = i * 100; 251 int upper = (i + 10) * 100; 252 mWhere[i] = "b >= " + lower + " AND b < " + upper; 253 } 254 } 255 256 public void testRun() { 257 for (int i = 0; i < FAST_OP_COUNT; i++) { 258 checkCursor(mDatabase 259 .query("t1", COLUMNS, mWhere[i % DATASET_SIZE], null, null, null, null)); 260 } 261 } 262 } 263 264 /** 265 * 100 SELECTs on a string comparison 266 */ 267 public static class SelectStringComparison100 extends PerformanceBase { 268 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 269 270 private String[] mWhere = new String[DATASET_SIZE]; 271 272 @Override 273 protected void prepareForTest() { 274 Random random = new Random(42); 275 276 mDatabase 277 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 278 279 for (int i = 0; i < DATASET_SIZE; i++) { 280 int r = random.nextInt(100000); 281 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 282 + numberName(r) + "')"); 283 } 284 285 for (int i = 0; i < DATASET_SIZE; i++) { 286 mWhere[i] = "c LIKE '" + numberName(i) + "'"; 287 } 288 } 289 290 public void testRun() { 291 for (int i = 0; i < FAST_OP_COUNT; i++) { 292 checkCursor(mDatabase 293 .query("t1", COLUMNS, mWhere[i % DATASET_SIZE], null, null, null, null)); 294 } 295 } 296 } 297 298 /** 299 * 100 SELECTs with an index 300 */ 301 public static class SelectIndex100 extends PerformanceBase { 302 private static final int TABLE_SIZE = 100; 303 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 304 305 private String[] mWhere = new String[TABLE_SIZE]; 306 307 @Override 308 protected void prepareForTest() { 309 Random random = new Random(42); 310 311 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 312 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 313 314 for (int i = 0; i < TABLE_SIZE; i++) { 315 int r = random.nextInt(100000); 316 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 317 + numberName(r) + "')"); 318 } 319 320 for (int i = 0; i < TABLE_SIZE; i++) { 321 int lower = i * 100; 322 int upper = (i + 10) * 100; 323 mWhere[i] = "b >= " + lower + " AND b < " + upper; 324 } 325 } 326 327 public void testRun() { 328 for (int i = 0; i < FAST_OP_COUNT; i++) { 329 checkCursor(mDatabase 330 .query("t1", COLUMNS, mWhere[i % TABLE_SIZE], null, null, null, null)); 331 } 332 } 333 } 334 335 /** 336 * INNER JOIN without an index 337 */ 338 public static class InnerJoin100 extends PerformanceBase { 339 private static final String[] COLUMNS = {"t1.a"}; 340 341 @Override 342 protected void prepareForTest() { 343 Random random = new Random(42); 344 345 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 346 mDatabase.execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 347 348 for (int i = 0; i < DATASET_SIZE; i++) { 349 int r = random.nextInt(100000); 350 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 351 + numberName(r) + "')"); 352 } 353 354 for (int i = 0; i < DATASET_SIZE; i++) { 355 int r = random.nextInt(100000); 356 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 357 + numberName(r) + "')"); 358 } 359 } 360 361 public void testRun() { 362 for (int i = 0; i < FAST_OP_COUNT; i++) { 363 checkCursor(mDatabase.query("t1 INNER JOIN t2 ON t1.b = t2.b", COLUMNS, null, 364 null, null, null, null)); 365 } 366 } 367 } 368 369 /** 370 * INNER JOIN without an index on one side 371 */ 372 373 public static class InnerJoinOneSide100 extends PerformanceBase { 374 private static final String[] COLUMNS = {"t1.a"}; 375 376 @Override 377 protected void prepareForTest() { 378 Random random = new Random(42); 379 380 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 381 mDatabase.execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 382 383 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 384 385 for (int i = 0; i < DATASET_SIZE; i++) { 386 int r = random.nextInt(100000); 387 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 388 + numberName(r) + "')"); 389 } 390 391 for (int i = 0; i < DATASET_SIZE; i++) { 392 int r = random.nextInt(100000); 393 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 394 + numberName(r) + "')"); 395 } 396 } 397 398 public void testRun() { 399 for (int i = 0; i < FAST_OP_COUNT; i++) { 400 checkCursor(mDatabase.query("t1 INNER JOIN t2 ON t1.b = t2.b", COLUMNS, null, 401 null, null, null, null)); 402 } 403 } 404 } 405 406 /** 407 * INNER JOIN without an index on one side 408 */ 409 public static class InnerJoinNoIndex100 extends PerformanceBase { 410 private static final String[] COLUMNS = {"t1.a"}; 411 412 @Override 413 protected void prepareForTest() { 414 Random random = new Random(42); 415 416 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 417 mDatabase.execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 418 419 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 420 421 for (int i = 0; i < DATASET_SIZE; i++) { 422 int r = random.nextInt(100000); 423 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 424 + numberName(r) + "')"); 425 } 426 427 for (int i = 0; i < DATASET_SIZE; i++) { 428 int r = random.nextInt(100000); 429 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 430 + numberName(r) + "')"); 431 } 432 } 433 434 public void testRun() { 435 for (int i = 0; i < FAST_OP_COUNT; i++) { 436 checkCursor(mDatabase 437 .query("t1 INNER JOIN t2 ON t1.c = t2.c", COLUMNS, null, null, null, null, 438 null)); 439 } 440 } 441 } 442 443 /** 444 * 100 SELECTs with subqueries. Subquery is using an index 445 */ 446 public static class SelectSubQIndex100 extends PerformanceBase { 447 private static final String[] COLUMNS = {"t1.a"}; 448 449 private String[] mWhere = new String[DATASET_SIZE]; 450 451 @Override 452 protected void prepareForTest() { 453 Random random = new Random(42); 454 455 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 456 mDatabase.execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 457 458 mDatabase.execSQL("CREATE INDEX i2b ON t2(b)"); 459 460 for (int i = 0; i < DATASET_SIZE; i++) { 461 int r = random.nextInt(100000); 462 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 463 + numberName(r) + "')"); 464 } 465 466 for (int i = 0; i < DATASET_SIZE; i++) { 467 int r = random.nextInt(100000); 468 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 469 + numberName(r) + "')"); 470 } 471 472 for (int i = 0; i < DATASET_SIZE; i++) { 473 int lower = i * 100; 474 int upper = (i + 10) * 100; 475 mWhere[i] = 476 "t1.b IN (SELECT t2.b FROM t2 WHERE t2.b >= " + lower 477 + " AND t2.b < " + upper + ")"; 478 } 479 } 480 481 public void testRun() { 482 for (int i = 0; i < FAST_OP_COUNT; i++) { 483 checkCursor(mDatabase 484 .query("t1", COLUMNS, mWhere[i % DATASET_SIZE], null, null, null, null)); 485 } 486 } 487 } 488 489 /** 490 * 100 SELECTs on string comparison with Index 491 */ 492 public static class SelectIndexStringComparison100 extends PerformanceBase { 493 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 494 495 private String[] mWhere = new String[DATASET_SIZE]; 496 497 @Override 498 protected void prepareForTest() { 499 Random random = new Random(42); 500 501 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 502 mDatabase.execSQL("CREATE INDEX i3c ON t1(c)"); 503 504 for (int i = 0; i < DATASET_SIZE; i++) { 505 int r = random.nextInt(100000); 506 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 507 + numberName(r) + "')"); 508 } 509 510 for (int i = 0; i < DATASET_SIZE; i++) { 511 mWhere[i] = "c LIKE '" + numberName(i) + "'"; 512 } 513 } 514 515 public void testRun() { 516 for (int i = 0; i < FAST_OP_COUNT; i++) { 517 checkCursor(mDatabase 518 .query("t1", COLUMNS, mWhere[i % DATASET_SIZE], null, null, null, null)); 519 } 520 } 521 } 522 523 /** 524 * 100 SELECTs on integer 525 */ 526 public static class SelectInteger100 extends PerformanceBase { 527 private static final String[] COLUMNS = {"b"}; 528 529 @Override 530 protected void prepareForTest() { 531 Random random = new Random(42); 532 533 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 534 535 for (int i = 0; i < DATASET_SIZE; i++) { 536 int r = random.nextInt(100000); 537 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 538 + numberName(r) + "')"); 539 } 540 541 } 542 543 public void testRun() { 544 for (int i = 0; i < FAST_OP_COUNT; i++) { 545 checkCursor(mDatabase.query("t1", COLUMNS, null, null, null, null, null)); 546 } 547 } 548 } 549 550 /** 551 * 100 SELECTs on String 552 */ 553 public static class SelectString100 extends PerformanceBase { 554 private static final String[] COLUMNS = {"c"}; 555 556 @Override 557 protected void prepareForTest() { 558 Random random = new Random(42); 559 560 mDatabase 561 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 562 563 for (int i = 0; i < DATASET_SIZE; i++) { 564 int r = random.nextInt(100000); 565 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 566 + numberName(r) + "')"); 567 } 568 } 569 570 public void testRun() { 571 for (int i = 0; i < FAST_OP_COUNT; i++) { 572 mDatabase.query("t1", COLUMNS, null, null, null, null, null); 573 } 574 } 575 } 576 577 /** 578 * 100 SELECTs on integer with index 579 */ 580 public static class SelectIntegerIndex100 extends PerformanceBase { 581 private static final String[] COLUMNS = {"b"}; 582 583 @Override 584 protected void prepareForTest() { 585 Random random = new Random(42); 586 587 mDatabase 588 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 589 mDatabase.execSQL("CREATE INDEX i1b on t1(b)"); 590 591 for (int i = 0; i < DATASET_SIZE; i++) { 592 int r = random.nextInt(100000); 593 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 594 + numberName(r) + "')"); 595 } 596 597 } 598 599 public void testRun() { 600 for (int i = 0; i < FAST_OP_COUNT; i++) { 601 mDatabase.query("t1", COLUMNS, null, null, null, null, null); 602 } 603 } 604 } 605 606 /** 607 * 100 SELECTs on String with index 608 */ 609 public static class SelectIndexString100 extends PerformanceBase { 610 private static final String[] COLUMNS = {"c"}; 611 612 @Override 613 protected void prepareForTest() { 614 Random random = new Random(42); 615 616 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 617 mDatabase.execSQL("CREATE INDEX i1c ON t1(c)"); 618 619 for (int i = 0; i < DATASET_SIZE; i++) { 620 int r = random.nextInt(100000); 621 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 622 + numberName(r) + "')"); 623 } 624 } 625 626 public void testRun() { 627 for (int i = 0; i < FAST_OP_COUNT; i++) { 628 checkCursor(mDatabase.query("t1", COLUMNS, null, null, null, null, null)); 629 } 630 } 631 632 } 633 634 /** 635 * 100 SELECTs on String with starts with 636 */ 637 public static class SelectStringStartsWith100 extends PerformanceBase { 638 private static final String[] COLUMNS = {"c"}; 639 private String[] mWhere = new String[DATASET_SIZE]; 640 641 @Override 642 protected void prepareForTest() { 643 Random random = new Random(42); 644 645 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 646 mDatabase.execSQL("CREATE INDEX i1c ON t1(c)"); 647 648 for (int i = 0; i < DATASET_SIZE; i++) { 649 int r = random.nextInt(100000); 650 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 651 + numberName(r) + "')"); 652 } 653 654 for (int i = 0; i < DATASET_SIZE; i++) { 655 int r = random.nextInt(100000); 656 mWhere[i] = "c LIKE '" + numberName(r).substring(0, 1) + "*'"; 657 658 } 659 660 } 661 662 public void testRun() { 663 for (int i = 0; i < FAST_OP_COUNT; i++) { 664 mDatabase.query("t1", COLUMNS, mWhere[i % DATASET_SIZE], null, null, null, null); 665 } 666 } 667 } 668 669 /** 670 * 100 Deletes on an indexed table 671 */ 672 public static class DeleteIndexed100 extends PerformanceBase { 673 674 @Override 675 protected void prepareForTest() { 676 Random random = new Random(42); 677 678 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 679 mDatabase.execSQL("CREATE INDEX i3c ON t1(c)"); 680 681 for (int i = 0; i < DATASET_SIZE; i++) { 682 int r = random.nextInt(100000); 683 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 684 + numberName(r) + "')"); 685 } 686 687 } 688 689 public void testRun() { 690 for (int i = 0; i < DATASET_SIZE; i++) { 691 mDatabase.delete("t1", null, null); 692 } 693 } 694 } 695 696 /** 697 * 100 Deletes 698 */ 699 public static class Delete100 extends PerformanceBase { 700 @Override 701 protected void prepareForTest() { 702 Random random = new Random(42); 703 704 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 705 706 for (int i = 0; i < DATASET_SIZE; i++) { 707 int r = random.nextInt(100000); 708 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 709 + numberName(r) + "')"); 710 } 711 712 } 713 714 public void testRun() { 715 for (int i = 0; i < DATASET_SIZE; i++) { 716 mDatabase.delete("t1", null, null); 717 } 718 } 719 } 720 721 /** 722 * 100 DELETE's without an index with where clause 723 */ 724 public static class DeleteWhere100 extends PerformanceBase { 725 private String[] mWhere = new String[DATASET_SIZE]; 726 727 @Override 728 protected void prepareForTest() { 729 Random random = new Random(42); 730 731 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 732 733 for (int i = 0; i < DATASET_SIZE; i++) { 734 int r = random.nextInt(100000); 735 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 736 + numberName(r) + "')"); 737 } 738 739 for (int i = 0; i < DATASET_SIZE; i++) { 740 int lower = i * 100; 741 int upper = (i + 10) * 100; 742 mWhere[i] = "b >= " + lower + " AND b < " + upper; 743 } 744 } 745 746 public void testRun() { 747 for (int i = 0; i < DATASET_SIZE; i++) { 748 mDatabase.delete("t1", mWhere[i], null); 749 } 750 } 751 } 752 753 /** 754 * 100 DELETE's with an index with where clause 755 */ 756 public static class DeleteIndexWhere100 extends PerformanceBase { 757 private String[] mWhere = new String[DATASET_SIZE]; 758 759 @Override 760 protected void prepareForTest() { 761 Random random = new Random(42); 762 763 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 764 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 765 766 for (int i = 0; i < DATASET_SIZE; i++) { 767 int r = random.nextInt(100000); 768 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 769 + numberName(r) + "')"); 770 } 771 772 for (int i = 0; i < DATASET_SIZE; i++) { 773 int lower = i * 100; 774 int upper = (i + 10) * 100; 775 mWhere[i] = "b >= " + lower + " AND b < " + upper; 776 } 777 } 778 779 public void testRun() { 780 for (int i = 0; i < DATASET_SIZE; i++) { 781 mDatabase.delete("t1", mWhere[i], null); 782 } 783 } 784 } 785 786 /** 787 * 100 update's with an index with where clause 788 */ 789 public static class UpdateIndexWhere100 extends PerformanceBase { 790 private String[] mWhere = new String[DATASET_SIZE]; 791 ContentValues[] mValues = new ContentValues[DATASET_SIZE]; 792 793 @Override 794 protected void prepareForTest() { 795 Random random = new Random(42); 796 797 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 798 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 799 800 for (int i = 0; i < DATASET_SIZE; i++) { 801 int r = random.nextInt(100000); 802 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 803 + numberName(r) + "')"); 804 } 805 806 for (int i = 0; i < DATASET_SIZE; i++) { 807 int lower = i * 100; 808 int upper = (i + 10) * 100; 809 mWhere[i] = "b >= " + lower + " AND b < " + upper; 810 ContentValues b = new ContentValues(1); 811 b.put("b", upper); 812 mValues[i] = b; 813 814 } 815 } 816 817 public void testRun() { 818 for (int i = 0; i < DATASET_SIZE; i++) { 819 mDatabase.update("t1", mValues[i], mWhere[i], null); 820 } 821 } 822 } 823 824 /** 825 * 100 update's without an index with where clause 826 */ 827 public static class UpdateWhere100 extends PerformanceBase { 828 private String[] mWhere = new String[DATASET_SIZE]; 829 ContentValues[] mValues = new ContentValues[DATASET_SIZE]; 830 831 @Override 832 protected void prepareForTest() { 833 Random random = new Random(42); 834 835 mDatabase.execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 836 837 for (int i = 0; i < DATASET_SIZE; i++) { 838 int r = random.nextInt(100000); 839 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 840 + numberName(r) + "')"); 841 } 842 843 for (int i = 0; i < DATASET_SIZE; i++) { 844 845 int lower = i * 100; 846 int upper = (i + 10) * 100; 847 mWhere[i] = "b >= " + lower + " AND b < " + upper; 848 ContentValues b = new ContentValues(1); 849 b.put("b", upper); 850 mValues[i] = b; 851 } 852 } 853 854 public void testRun() { 855 for (int i = 0; i < DATASET_SIZE; i++) { 856 mDatabase.update("t1", mValues[i], mWhere[i], null); 857 } 858 } 859 } 860 861 /** 862 * 100 selects for a String - contains 'e' 863 */ 864 public static class SelectStringContains100 extends PerformanceBase { 865 private static final String[] COLUMNS = {"t3.a"}; 866 private String[] mWhere = new String[DATASET_SIZE]; 867 868 @Override 869 protected void prepareForTest() { 870 Random random = new Random(42); 871 872 mDatabase.execSQL("CREATE TABLE t3(a VARCHAR(100))"); 873 874 for (int i = 0; i < DATASET_SIZE; i++) { 875 int r = random.nextInt(100000); 876 mDatabase.execSQL("INSERT INTO t3 VALUES('" 877 + numberName(r) + "')"); 878 } 879 880 for (int i = 0; i < DATASET_SIZE; i++) { 881 mWhere[i] = "a LIKE '*e*'"; 882 883 } 884 } 885 886 public void testRun() { 887 for (int i = 0; i < FAST_OP_COUNT; i++) { 888 checkCursor(mDatabase 889 .query("t3", COLUMNS, mWhere[i % DATASET_SIZE], null, null, null, null)); 890 } 891 } 892 } 893 894 /** 895 * 100 selects for a String - contains 'e'-indexed table 896 */ 897 public static class SelectStringIndexedContains100 extends PerformanceBase { 898 private static final String[] COLUMNS = {"t3.a"}; 899 private String[] mWhere = new String[DATASET_SIZE]; 900 901 @Override 902 protected void prepareForTest() { 903 Random random = new Random(42); 904 905 mDatabase.execSQL("CREATE TABLE t3(a VARCHAR(100))"); 906 mDatabase.execSQL("CREATE INDEX i3a ON t3(a)"); 907 908 for (int i = 0; i < DATASET_SIZE; i++) { 909 int r = random.nextInt(100000); 910 mDatabase.execSQL("INSERT INTO t3 VALUES('" 911 + numberName(r) + "')"); 912 } 913 914 for (int i = 0; i < DATASET_SIZE; i++) { 915 mWhere[i] = "a LIKE '*e*'"; 916 917 } 918 } 919 920 public void testRun() { 921 for (int i = 0; i < FAST_OP_COUNT; i++) { 922 checkCursor(mDatabase 923 .query("t3", COLUMNS, mWhere[i % DATASET_SIZE], null, null, null, null)); 924 } 925 } 926 } 927 928 static final String[] ONES = 929 {"zero", "one", "two", "three", "four", "five", "six", "seven", 930 "eight", "nine", "ten", "eleven", "twelve", "thirteen", 931 "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", 932 "nineteen"}; 933 934 static final String[] TENS = 935 {"", "ten", "twenty", "thirty", "forty", "fifty", "sixty", 936 "seventy", "eighty", "ninety"}; 937 938 static Map<String, Long> getIoStats() { 939 String ioStat = "/proc/self/io"; 940 Map<String, Long> results = new ArrayMap<>(); 941 try { 942 List<String> lines = Files.readAllLines(new File(ioStat).toPath()); 943 for (String line : lines) { 944 line = line.trim(); 945 String[] split = line.split(":"); 946 if (split.length == 2) { 947 try { 948 String key = split[0].trim(); 949 Long value = Long.valueOf(split[1].trim()); 950 results.put(key, value); 951 } catch (NumberFormatException e) { 952 Log.e(TAG, "Cannot parse number from " + line); 953 } 954 } else if (line.isEmpty()) { 955 Log.e(TAG, "Cannot parse line " + line); 956 } 957 } 958 } catch (IOException e) { 959 Log.e(TAG, "Can't read: " + ioStat, e); 960 } 961 return results; 962 } 963 964 } 965