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.test.PerformanceTestCase; 22 23 import junit.framework.TestCase; 24 25 import java.io.File; 26 import java.util.Random; 27 28 /** 29 * Database Performance Tests 30 * 31 */ 32 33 public class NewDatabasePerformanceTests { 34 35 // Edit this to change the test run times. The original is 100. 36 final static int kMultiplier = 1; 37 38 public static class PerformanceBase extends TestCase 39 implements PerformanceTestCase { 40 protected static final int CURRENT_DATABASE_VERSION = 42; 41 protected SQLiteDatabase mDatabase; 42 protected File mDatabaseFile; 43 44 public void setUp() { 45 mDatabaseFile = new File("/sdcard", "perf_database_test.db"); 46 if (mDatabaseFile.exists()) { 47 mDatabaseFile.delete(); 48 } 49 mDatabase = 50 SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), 51 null); 52 assertTrue(mDatabase != null); 53 mDatabase.setVersion(CURRENT_DATABASE_VERSION); 54 } 55 56 public void tearDown() { 57 mDatabase.close(); 58 mDatabaseFile.delete(); 59 } 60 61 public boolean isPerformanceOnly() { 62 return true; 63 } 64 65 // These tests can only be run once. 66 public int startPerformance(Intermediates intermediates) { 67 return 0; 68 } 69 70 public String numberName(int number) { 71 String result = ""; 72 73 if (number >= 1000) { 74 result += numberName((number / 1000)) + " thousand"; 75 number = (number % 1000); 76 77 if (number > 0) result += " "; 78 } 79 80 if (number >= 100) { 81 result += ONES[(number / 100)] + " hundred"; 82 number = (number % 100); 83 84 if (number > 0) result += " "; 85 } 86 87 if (number >= 20) { 88 result += TENS[(number / 10)]; 89 number = (number % 10); 90 91 if (number > 0) result += " "; 92 } 93 94 if (number > 0) { 95 result += ONES[number]; 96 } 97 98 return result; 99 } 100 } 101 102 /** 103 * Test 1000 inserts. 104 */ 105 106 public static class Insert1000 extends PerformanceBase { 107 private static final int SIZE = 10 * kMultiplier; 108 109 private String[] statements = new String[SIZE]; 110 111 @Override 112 public void setUp() { 113 super.setUp(); 114 Random random = new Random(42); 115 116 for (int i = 0; i < SIZE; i++) { 117 int r = random.nextInt(100000); 118 statements[i] = 119 "INSERT INTO t1 VALUES(" + i + "," + r + ",'" 120 + numberName(r) + "')"; 121 } 122 123 mDatabase 124 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 125 } 126 127 public void testRun() { 128 for (int i = 0; i < SIZE; i++) { 129 mDatabase.execSQL(statements[i]); 130 } 131 } 132 } 133 134 /** 135 * Test 1000 inserts into an indexed table. 136 */ 137 138 public static class InsertIndexed1000 extends PerformanceBase { 139 private static final int SIZE = 10 * kMultiplier; 140 141 private String[] statements = new String[SIZE]; 142 143 @Override 144 public void setUp() { 145 super.setUp(); 146 Random random = new Random(42); 147 148 for (int i = 0; i < SIZE; i++) { 149 int r = random.nextInt(100000); 150 statements[i] = 151 "INSERT INTO t1 VALUES(" + i + "," + r + ",'" 152 + numberName(r) + "')"; 153 } 154 155 mDatabase 156 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 157 mDatabase.execSQL("CREATE INDEX i1c ON t1(c)"); 158 } 159 160 public void testRun() { 161 for (int i = 0; i < SIZE; i++) { 162 mDatabase.execSQL(statements[i]); 163 } 164 } 165 } 166 167 /** 168 * 100 SELECTs without an index 169 */ 170 171 public static class Select100 extends PerformanceBase { 172 private static final int SIZE = 1 * kMultiplier; 173 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 174 175 private String[] where = new String[SIZE]; 176 177 @Override 178 public void setUp() { 179 super.setUp(); 180 Random random = new Random(42); 181 182 mDatabase 183 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 184 185 for (int i = 0; i < SIZE; i++) { 186 int r = random.nextInt(100000); 187 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 188 + numberName(r) + "')"); 189 } 190 191 for (int i = 0; i < SIZE; i++) { 192 int lower = i * 100; 193 int upper = (i + 10) * 100; 194 where[i] = "b >= " + lower + " AND b < " + upper; 195 } 196 } 197 198 public void testRun() { 199 for (int i = 0; i < SIZE; i++) { 200 mDatabase 201 .query("t1", COLUMNS, where[i], null, null, null, null); 202 } 203 } 204 } 205 206 /** 207 * 100 SELECTs on a string comparison 208 */ 209 210 public static class SelectStringComparison100 extends PerformanceBase { 211 private static final int SIZE = 1 * kMultiplier; 212 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 213 214 private String[] where = new String[SIZE]; 215 216 @Override 217 public void setUp() { 218 super.setUp(); 219 Random random = new Random(42); 220 221 mDatabase 222 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 223 224 for (int i = 0; i < SIZE; i++) { 225 int r = random.nextInt(100000); 226 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 227 + numberName(r) + "')"); 228 } 229 230 for (int i = 0; i < SIZE; i++) { 231 where[i] = "c LIKE '" + numberName(i) + "'"; 232 } 233 } 234 235 public void testRun() { 236 for (int i = 0; i < SIZE; i++) { 237 mDatabase 238 .query("t1", COLUMNS, where[i], null, null, null, null); 239 } 240 } 241 } 242 243 /** 244 * 100 SELECTs with an index 245 */ 246 247 public static class SelectIndex100 extends PerformanceBase { 248 private static final int SIZE = 1 * kMultiplier; 249 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 250 251 private String[] where = new String[SIZE]; 252 253 @Override 254 public void setUp() { 255 super.setUp(); 256 Random random = new Random(42); 257 258 mDatabase 259 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 260 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 261 262 for (int i = 0; i < SIZE; i++) { 263 int r = random.nextInt(100000); 264 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 265 + numberName(r) + "')"); 266 } 267 268 for (int i = 0; i < SIZE; i++) { 269 int lower = i * 100; 270 int upper = (i + 10) * 100; 271 where[i] = "b >= " + lower + " AND b < " + upper; 272 } 273 } 274 275 public void testRun() { 276 for (int i = 0; i < SIZE; i++) { 277 mDatabase 278 .query("t1", COLUMNS, where[i], null, null, null, null); 279 } 280 } 281 } 282 283 /** 284 * INNER JOIN without an index 285 */ 286 287 public static class InnerJoin100 extends PerformanceBase { 288 private static final int SIZE = 1 * kMultiplier; 289 private static final String[] COLUMNS = {"t1.a"}; 290 291 @Override 292 public void setUp() { 293 super.setUp(); 294 Random random = new Random(42); 295 296 mDatabase 297 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 298 mDatabase 299 .execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 300 301 for (int i = 0; i < SIZE; i++) { 302 int r = random.nextInt(100000); 303 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 304 + numberName(r) + "')"); 305 } 306 307 for (int i = 0; i < SIZE; i++) { 308 int r = random.nextInt(100000); 309 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 310 + numberName(r) + "')"); 311 } 312 } 313 314 public void testRun() { 315 mDatabase.query("t1 INNER JOIN t2 ON t1.b = t2.b", COLUMNS, null, 316 null, null, null, null); 317 } 318 } 319 320 /** 321 * INNER JOIN without an index on one side 322 */ 323 324 public static class InnerJoinOneSide100 extends PerformanceBase { 325 private static final int SIZE = 1 * kMultiplier; 326 private static final String[] COLUMNS = {"t1.a"}; 327 328 @Override 329 public void setUp() { 330 super.setUp(); 331 Random random = new Random(42); 332 333 mDatabase 334 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 335 mDatabase 336 .execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 337 338 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 339 340 for (int i = 0; i < SIZE; i++) { 341 int r = random.nextInt(100000); 342 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 343 + numberName(r) + "')"); 344 } 345 346 for (int i = 0; i < SIZE; i++) { 347 int r = random.nextInt(100000); 348 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 349 + numberName(r) + "')"); 350 } 351 } 352 353 public void testRun() { 354 mDatabase.query("t1 INNER JOIN t2 ON t1.b = t2.b", COLUMNS, null, 355 null, null, null, null); 356 } 357 } 358 359 /** 360 * INNER JOIN without an index on one side 361 */ 362 363 public static class InnerJoinNoIndex100 extends PerformanceBase { 364 private static final int SIZE = 1 * kMultiplier; 365 private static final String[] COLUMNS = {"t1.a"}; 366 367 @Override 368 public void setUp() { 369 super.setUp(); 370 Random random = new Random(42); 371 372 mDatabase 373 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 374 mDatabase 375 .execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 376 377 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 378 379 for (int i = 0; i < SIZE; i++) { 380 int r = random.nextInt(100000); 381 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 382 + numberName(r) + "')"); 383 } 384 385 for (int i = 0; i < SIZE; i++) { 386 int r = random.nextInt(100000); 387 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 388 + numberName(r) + "')"); 389 } 390 } 391 392 public void testRun() { 393 mDatabase.query("t1 INNER JOIN t2 ON t1.c = t2.c", COLUMNS, null, 394 null, null, null, null); 395 } 396 } 397 398 /** 399 * 100 SELECTs with subqueries. Subquery is using an index 400 */ 401 402 public static class SelectSubQIndex100 extends PerformanceBase { 403 private static final int SIZE = 1 * kMultiplier; 404 private static final String[] COLUMNS = {"t1.a"}; 405 406 private String[] where = new String[SIZE]; 407 408 @Override 409 public void setUp() { 410 super.setUp(); 411 Random random = new Random(42); 412 413 mDatabase 414 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 415 mDatabase 416 .execSQL("CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100))"); 417 418 mDatabase.execSQL("CREATE INDEX i2b ON t2(b)"); 419 420 for (int i = 0; i < SIZE; i++) { 421 int r = random.nextInt(100000); 422 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 423 + numberName(r) + "')"); 424 } 425 426 for (int i = 0; i < SIZE; i++) { 427 int r = random.nextInt(100000); 428 mDatabase.execSQL("INSERT INTO t2 VALUES(" + i + "," + r + ",'" 429 + numberName(r) + "')"); 430 } 431 432 for (int i = 0; i < SIZE; i++) { 433 int lower = i * 100; 434 int upper = (i + 10) * 100; 435 where[i] = 436 "t1.b IN (SELECT t2.b FROM t2 WHERE t2.b >= " + lower 437 + " AND t2.b < " + upper + ")"; 438 } 439 } 440 441 public void testRun() { 442 for (int i = 0; i < SIZE; i++) { 443 mDatabase 444 .query("t1", COLUMNS, where[i], null, null, null, null); 445 } 446 } 447 } 448 449 /** 450 * 100 SELECTs on string comparison with Index 451 */ 452 453 public static class SelectIndexStringComparison100 extends PerformanceBase { 454 private static final int SIZE = 1 * kMultiplier; 455 private static final String[] COLUMNS = {"count(*)", "avg(b)"}; 456 457 private String[] where = new String[SIZE]; 458 459 @Override 460 public void setUp() { 461 super.setUp(); 462 Random random = new Random(42); 463 464 mDatabase 465 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 466 mDatabase.execSQL("CREATE INDEX i3c ON t1(c)"); 467 468 for (int i = 0; i < SIZE; i++) { 469 int r = random.nextInt(100000); 470 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 471 + numberName(r) + "')"); 472 } 473 474 for (int i = 0; i < SIZE; i++) { 475 where[i] = "c LIKE '" + numberName(i) + "'"; 476 } 477 } 478 479 public void testRun() { 480 for (int i = 0; i < SIZE; i++) { 481 mDatabase 482 .query("t1", COLUMNS, where[i], null, null, null, null); 483 } 484 } 485 } 486 487 /** 488 * 100 SELECTs on integer 489 */ 490 491 public static class SelectInteger100 extends PerformanceBase { 492 private static final int SIZE = 1 * kMultiplier; 493 private static final String[] COLUMNS = {"b"}; 494 495 @Override 496 public void setUp() { 497 super.setUp(); 498 Random random = new Random(42); 499 500 mDatabase 501 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 502 503 for (int i = 0; i < SIZE; i++) { 504 int r = random.nextInt(100000); 505 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 506 + numberName(r) + "')"); 507 } 508 509 } 510 511 public void testRun() { 512 for (int i = 0; i < SIZE; i++) { 513 mDatabase.query("t1", COLUMNS, null, null, null, null, null); 514 } 515 } 516 } 517 518 /** 519 * 100 SELECTs on String 520 */ 521 522 public static class SelectString100 extends PerformanceBase { 523 private static final int SIZE = 1 * kMultiplier; 524 private static final String[] COLUMNS = {"c"}; 525 526 @Override 527 public void setUp() { 528 super.setUp(); 529 Random random = new Random(42); 530 531 mDatabase 532 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 533 534 for (int i = 0; i < SIZE; i++) { 535 int r = random.nextInt(100000); 536 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 537 + numberName(r) + "')"); 538 } 539 540 } 541 542 public void testRun() { 543 for (int i = 0; i < SIZE; i++) { 544 mDatabase.query("t1", COLUMNS, null, null, null, null, null); 545 } 546 } 547 } 548 549 /** 550 * 100 SELECTs on integer with index 551 */ 552 553 public static class SelectIntegerIndex100 extends PerformanceBase { 554 private static final int SIZE = 1 * kMultiplier; 555 private static final String[] COLUMNS = {"b"}; 556 557 @Override 558 public void setUp() { 559 super.setUp(); 560 Random random = new Random(42); 561 562 mDatabase 563 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 564 mDatabase.execSQL("CREATE INDEX i1b on t1(b)"); 565 566 for (int i = 0; i < SIZE; i++) { 567 int r = random.nextInt(100000); 568 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 569 + numberName(r) + "')"); 570 } 571 572 } 573 574 public void testRun() { 575 for (int i = 0; i < SIZE; i++) { 576 mDatabase.query("t1", COLUMNS, null, null, null, null, null); 577 } 578 } 579 } 580 581 /** 582 * 100 SELECTs on String with index 583 */ 584 585 public static class SelectIndexString100 extends PerformanceBase { 586 private static final int SIZE = 1 * kMultiplier; 587 private static final String[] COLUMNS = {"c"}; 588 589 @Override 590 public void setUp() { 591 super.setUp(); 592 Random random = new Random(42); 593 594 mDatabase 595 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 596 mDatabase.execSQL("CREATE INDEX i1c ON t1(c)"); 597 598 for (int i = 0; i < SIZE; i++) { 599 int r = random.nextInt(100000); 600 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 601 + numberName(r) + "')"); 602 } 603 604 } 605 606 public void testRun() { 607 for (int i = 0; i < SIZE; i++) { 608 mDatabase.query("t1", COLUMNS, null, null, null, null, null); 609 } 610 } 611 } 612 613 /** 614 * 100 SELECTs on String with starts with 615 */ 616 617 public static class SelectStringStartsWith100 extends PerformanceBase { 618 private static final int SIZE = 1 * kMultiplier; 619 private static final String[] COLUMNS = {"c"}; 620 private String[] where = new String[SIZE]; 621 622 @Override 623 public void setUp() { 624 super.setUp(); 625 Random random = new Random(42); 626 627 mDatabase 628 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 629 mDatabase.execSQL("CREATE INDEX i1c ON t1(c)"); 630 631 for (int i = 0; i < SIZE; i++) { 632 int r = random.nextInt(100000); 633 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 634 + numberName(r) + "')"); 635 } 636 637 for (int i = 0; i < SIZE; i++) { 638 int r = random.nextInt(100000); 639 where[i] = "c LIKE '" + numberName(r).substring(0, 1) + "*'"; 640 641 } 642 643 } 644 645 public void testRun() { 646 for (int i = 0; i < SIZE; i++) { 647 mDatabase 648 .query("t1", COLUMNS, where[i], null, null, null, null); 649 } 650 } 651 } 652 653 /** 654 * 1000 Deletes on an indexed table 655 */ 656 657 public static class DeleteIndexed1000 extends PerformanceBase { 658 private static final int SIZE = 10 * kMultiplier; 659 660 @Override 661 public void setUp() { 662 super.setUp(); 663 Random random = new Random(42); 664 665 mDatabase 666 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 667 mDatabase.execSQL("CREATE INDEX i3c ON t1(c)"); 668 669 for (int i = 0; i < SIZE; i++) { 670 int r = random.nextInt(100000); 671 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 672 + numberName(r) + "')"); 673 } 674 675 } 676 677 public void testRun() { 678 for (int i = 0; i < SIZE; i++) { 679 mDatabase.delete("t1", null, null); 680 } 681 } 682 } 683 684 /** 685 * 1000 Deletes 686 */ 687 688 public static class Delete1000 extends PerformanceBase { 689 private static final int SIZE = 10 * kMultiplier; 690 691 @Override 692 public void setUp() { 693 super.setUp(); 694 Random random = new Random(42); 695 696 mDatabase 697 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 698 699 for (int i = 0; i < SIZE; i++) { 700 int r = random.nextInt(100000); 701 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 702 + numberName(r) + "')"); 703 } 704 705 } 706 707 public void testRun() { 708 for (int i = 0; i < SIZE; i++) { 709 mDatabase.delete("t1", null, null); 710 } 711 } 712 } 713 714 /** 715 * 1000 DELETE's without an index with where clause 716 */ 717 718 public static class DeleteWhere1000 extends PerformanceBase { 719 private static final int SIZE = 10 * kMultiplier; 720 private String[] where = new String[SIZE]; 721 722 @Override 723 public void setUp() { 724 super.setUp(); 725 Random random = new Random(42); 726 727 mDatabase 728 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 729 730 for (int i = 0; i < SIZE; i++) { 731 int r = random.nextInt(100000); 732 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 733 + numberName(r) + "')"); 734 } 735 736 for (int i = 0; i < SIZE; i++) { 737 int lower = i * 100; 738 int upper = (i + 10) * 100; 739 where[i] = "b >= " + lower + " AND b < " + upper; 740 } 741 } 742 743 public void testRun() { 744 for (int i = 0; i < SIZE; i++) { 745 mDatabase.delete("t1", where[i], null); 746 } 747 } 748 } 749 750 /** 751 * 1000 DELETE's with an index with where clause 752 */ 753 754 public static class DeleteIndexWhere1000 extends PerformanceBase { 755 private static final int SIZE = 10 * kMultiplier; 756 private String[] where = new String[SIZE]; 757 758 @Override 759 public void setUp() { 760 super.setUp(); 761 Random random = new Random(42); 762 763 mDatabase 764 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 765 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 766 767 for (int i = 0; i < SIZE; i++) { 768 int r = random.nextInt(100000); 769 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 770 + numberName(r) + "')"); 771 } 772 773 for (int i = 0; i < SIZE; i++) { 774 int lower = i * 100; 775 int upper = (i + 10) * 100; 776 where[i] = "b >= " + lower + " AND b < " + upper; 777 } 778 } 779 780 public void testRun() { 781 for (int i = 0; i < SIZE; i++) { 782 mDatabase.delete("t1", where[i], null); 783 } 784 } 785 } 786 787 /** 788 * 1000 update's with an index with where clause 789 */ 790 791 public static class UpdateIndexWhere1000 extends PerformanceBase { 792 private static final int SIZE = 10 * kMultiplier; 793 private String[] where = new String[SIZE]; 794 ContentValues[] mValues = new ContentValues[SIZE]; 795 796 @Override 797 public void setUp() { 798 super.setUp(); 799 Random random = new Random(42); 800 801 mDatabase 802 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 803 mDatabase.execSQL("CREATE INDEX i1b ON t1(b)"); 804 805 for (int i = 0; i < SIZE; i++) { 806 int r = random.nextInt(100000); 807 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 808 + numberName(r) + "')"); 809 } 810 811 for (int i = 0; i < SIZE; i++) { 812 813 int lower = i * 100; 814 int upper = (i + 10) * 100; 815 where[i] = "b >= " + lower + " AND b < " + upper; 816 ContentValues b = new ContentValues(1); 817 b.put("b", upper); 818 mValues[i] = b; 819 820 } 821 } 822 823 public void testRun() { 824 for (int i = 0; i < SIZE; i++) { 825 mDatabase.update("t1", mValues[i], where[i], null); 826 } 827 } 828 } 829 830 /** 831 * 1000 update's without an index with where clause 832 */ 833 834 public static class UpdateWhere1000 extends PerformanceBase { 835 private static final int SIZE = 10 * kMultiplier; 836 private String[] where = new String[SIZE]; 837 ContentValues[] mValues = new ContentValues[SIZE]; 838 839 @Override 840 public void setUp() { 841 super.setUp(); 842 Random random = new Random(42); 843 844 mDatabase 845 .execSQL("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))"); 846 847 for (int i = 0; i < SIZE; i++) { 848 int r = random.nextInt(100000); 849 mDatabase.execSQL("INSERT INTO t1 VALUES(" + i + "," + r + ",'" 850 + numberName(r) + "')"); 851 } 852 853 for (int i = 0; i < SIZE; i++) { 854 855 int lower = i * 100; 856 int upper = (i + 10) * 100; 857 where[i] = "b >= " + lower + " AND b < " + upper; 858 ContentValues b = new ContentValues(1); 859 b.put("b", upper); 860 mValues[i] = b; 861 } 862 } 863 864 public void testRun() { 865 for (int i = 0; i < SIZE; i++) { 866 mDatabase.update("t1", mValues[i], where[i], null); 867 } 868 } 869 } 870 871 /** 872 * 10000 inserts for an integer 873 */ 874 875 public static class InsertInteger10000 extends PerformanceBase { 876 private static final int SIZE = 100 * kMultiplier; 877 ContentValues[] mValues = new ContentValues[SIZE]; 878 879 @Override 880 public void setUp() { 881 super.setUp(); 882 Random random = new Random(42); 883 884 mDatabase 885 .execSQL("CREATE TABLE t1(a INTEGER)"); 886 887 for (int i = 0; i < SIZE; i++) { 888 int r = random.nextInt(100000); 889 ContentValues b = new ContentValues(1); 890 b.put("a", r); 891 mValues[i] = b; 892 } 893 } 894 895 public void testRun() { 896 for (int i = 0; i < SIZE; i++) { 897 mDatabase.insert("t1", null, mValues[i]); 898 } 899 } 900 } 901 902 /** 903 * 10000 inserts for an integer -indexed table 904 */ 905 906 public static class InsertIntegerIndex10000 extends PerformanceBase { 907 private static final int SIZE = 100 * kMultiplier; 908 ContentValues[] mValues = new ContentValues[SIZE]; 909 910 @Override 911 public void setUp() { 912 super.setUp(); 913 Random random = new Random(42); 914 915 mDatabase 916 .execSQL("CREATE TABLE t1(a INTEGER)"); 917 mDatabase.execSQL("CREATE INDEX i1a ON t1(a)"); 918 919 for (int i = 0; i < SIZE; i++) { 920 int r = random.nextInt(100000); 921 ContentValues b = new ContentValues(1); 922 b.put("a", r); 923 mValues[i] = b; 924 } 925 } 926 927 public void testRun() { 928 for (int i = 0; i < SIZE; i++) { 929 mDatabase.insert("t1", null, mValues[i]); 930 } 931 } 932 } 933 934 /** 935 * 10000 inserts for a String 936 */ 937 938 public static class InsertString10000 extends PerformanceBase { 939 private static final int SIZE = 100 * kMultiplier; 940 ContentValues[] mValues = new ContentValues[SIZE]; 941 942 @Override 943 public void setUp() { 944 super.setUp(); 945 Random random = new Random(42); 946 947 mDatabase 948 .execSQL("CREATE TABLE t1(a VARCHAR(100))"); 949 950 for (int i = 0; i < SIZE; i++) { 951 int r = random.nextInt(100000); 952 ContentValues b = new ContentValues(1); 953 b.put("a", numberName(r)); 954 mValues[i] = b; 955 } 956 } 957 958 public void testRun() { 959 for (int i = 0; i < SIZE; i++) { 960 mDatabase.insert("t1", null, mValues[i]); 961 } 962 } 963 } 964 965 /** 966 * 10000 inserts for a String - indexed table 967 */ 968 969 public static class InsertStringIndexed10000 extends PerformanceBase { 970 private static final int SIZE = 100 * kMultiplier; 971 ContentValues[] mValues = new ContentValues[SIZE]; 972 973 @Override 974 public void setUp() { 975 super.setUp(); 976 Random random = new Random(42); 977 978 mDatabase 979 .execSQL("CREATE TABLE t1(a VARCHAR(100))"); 980 mDatabase.execSQL("CREATE INDEX i1a ON t1(a)"); 981 982 for (int i = 0; i < SIZE; i++) { 983 int r = random.nextInt(100000); 984 ContentValues b = new ContentValues(1); 985 b.put("a", numberName(r)); 986 mValues[i] = b; 987 } 988 } 989 990 public void testRun() { 991 for (int i = 0; i < SIZE; i++) { 992 mDatabase.insert("t1", null, mValues[i]); 993 } 994 } 995 } 996 997 998 /** 999 * 10000 selects for a String -starts with 1000 */ 1001 1002 public static class SelectStringStartsWith10000 extends PerformanceBase { 1003 private static final int SIZE = 100 * kMultiplier; 1004 private static final String[] COLUMNS = {"t3.a"}; 1005 private String[] where = new String[SIZE]; 1006 1007 @Override 1008 public void setUp() { 1009 super.setUp(); 1010 Random random = new Random(42); 1011 1012 mDatabase 1013 .execSQL("CREATE TABLE t3(a VARCHAR(100))"); 1014 1015 for (int i = 0; i < SIZE; i++) { 1016 int r = random.nextInt(100000); 1017 mDatabase.execSQL("INSERT INTO t3 VALUES('" 1018 + numberName(r) + "')"); 1019 } 1020 1021 for (int i = 0; i < SIZE; i++) { 1022 int r = random.nextInt(100000); 1023 where[i] = "a LIKE '" + numberName(r).substring(0, 1) + "*'"; 1024 1025 } 1026 } 1027 1028 public void testRun() { 1029 for (int i = 0; i < SIZE; i++) { 1030 mDatabase.query("t3", COLUMNS, where[i], null, null, null, null); 1031 } 1032 } 1033 } 1034 1035 /** 1036 * 10000 selects for a String - indexed table -starts with 1037 */ 1038 1039 public static class SelectStringIndexedStartsWith10000 extends 1040 PerformanceBase { 1041 private static final int SIZE = 100 * kMultiplier; 1042 private static final String[] COLUMNS = {"t3.a"}; 1043 private String[] where = new String[SIZE]; 1044 1045 @Override 1046 public void setUp() { 1047 super.setUp(); 1048 Random random = new Random(42); 1049 1050 mDatabase 1051 .execSQL("CREATE TABLE t3(a VARCHAR(100))"); 1052 mDatabase.execSQL("CREATE INDEX i3a ON t3(a)"); 1053 1054 for (int i = 0; i < SIZE; i++) { 1055 int r = random.nextInt(100000); 1056 mDatabase.execSQL("INSERT INTO t3 VALUES('" 1057 + numberName(r) + "')"); 1058 } 1059 1060 for (int i = 0; i < SIZE; i++) { 1061 int r = random.nextInt(100000); 1062 where[i] = "a LIKE '" + numberName(r).substring(0, 1) + "*'"; 1063 1064 } 1065 } 1066 1067 public void testRun() { 1068 for (int i = 0; i < SIZE; i++) { 1069 mDatabase.query("t3", COLUMNS, where[i], null, null, null, null); 1070 } 1071 } 1072 } 1073 1074 /** 1075 * 10000 selects for an integer - 1076 */ 1077 1078 public static class SelectInteger10000 extends PerformanceBase { 1079 private static final int SIZE = 100 * kMultiplier; 1080 private static final String[] COLUMNS = {"t4.a"}; 1081 private String[] where = new String[SIZE]; 1082 1083 @Override 1084 public void setUp() { 1085 super.setUp(); 1086 Random random = new Random(42); 1087 1088 mDatabase 1089 .execSQL("CREATE TABLE t4(a INTEGER)"); 1090 1091 for (int i = 0; i < SIZE; i++) { 1092 int r = random.nextInt(100000); 1093 mDatabase.execSQL("INSERT INTO t4 VALUES(" + r + ")"); 1094 int lower = i * 100; 1095 int upper = (i + 10) * 100; 1096 where[i] = "a >= " + lower + " AND a < " + upper; 1097 } 1098 } 1099 1100 public void testRun() { 1101 for (int i = 0; i < SIZE; i++) { 1102 mDatabase.query("t4", COLUMNS, where[i], null, null, null, null); 1103 } 1104 } 1105 } 1106 1107 /** 1108 * 10000 selects for an integer -indexed table 1109 */ 1110 1111 public static class SelectIntegerIndexed10000 extends PerformanceBase { 1112 private static final int SIZE = 100 * kMultiplier; 1113 private static final String[] COLUMNS = {"t4.a"}; 1114 private String[] where = new String[SIZE]; 1115 1116 @Override 1117 public void setUp() { 1118 super.setUp(); 1119 Random random = new Random(42); 1120 1121 mDatabase 1122 .execSQL("CREATE TABLE t4(a INTEGER)"); 1123 mDatabase.execSQL("CREATE INDEX i4a ON t4(a)"); 1124 1125 for (int i = 0; i < SIZE; i++) { 1126 int r = random.nextInt(100000); 1127 mDatabase.execSQL("INSERT INTO t4 VALUES(" + r + ")"); 1128 1129 int lower = i * 100; 1130 int upper = (i + 10) * 100; 1131 where[i] = "a >= " + lower + " AND a < " + upper; 1132 } 1133 1134 } 1135 1136 public void testRun() { 1137 for (int i = 0; i < SIZE; i++) { 1138 mDatabase.query("t4", COLUMNS, where[i], null, null, null, null); 1139 } 1140 } 1141 } 1142 1143 1144 /** 1145 * 10000 selects for a String - contains 'e' 1146 */ 1147 1148 public static class SelectStringContains10000 extends PerformanceBase { 1149 private static final int SIZE = 100 * kMultiplier; 1150 private static final String[] COLUMNS = {"t3.a"}; 1151 private String[] where = new String[SIZE]; 1152 1153 @Override 1154 public void setUp() { 1155 super.setUp(); 1156 Random random = new Random(42); 1157 1158 mDatabase 1159 .execSQL("CREATE TABLE t3(a VARCHAR(100))"); 1160 1161 for (int i = 0; i < SIZE; i++) { 1162 int r = random.nextInt(100000); 1163 mDatabase.execSQL("INSERT INTO t3 VALUES('" 1164 + numberName(r) + "')"); 1165 } 1166 1167 for (int i = 0; i < SIZE; i++) { 1168 where[i] = "a LIKE '*e*'"; 1169 1170 } 1171 } 1172 1173 public void testRun() { 1174 for (int i = 0; i < SIZE; i++) { 1175 mDatabase.query("t3", COLUMNS, where[i], null, null, null, null); 1176 } 1177 } 1178 } 1179 1180 /** 1181 * 10000 selects for a String - contains 'e'-indexed table 1182 */ 1183 1184 public static class SelectStringIndexedContains10000 extends 1185 PerformanceBase { 1186 private static final int SIZE = 100 * kMultiplier; 1187 private static final String[] COLUMNS = {"t3.a"}; 1188 private String[] where = new String[SIZE]; 1189 1190 @Override 1191 public void setUp() { 1192 super.setUp(); 1193 Random random = new Random(42); 1194 1195 mDatabase 1196 .execSQL("CREATE TABLE t3(a VARCHAR(100))"); 1197 mDatabase.execSQL("CREATE INDEX i3a ON t3(a)"); 1198 1199 for (int i = 0; i < SIZE; i++) { 1200 int r = random.nextInt(100000); 1201 mDatabase.execSQL("INSERT INTO t3 VALUES('" 1202 + numberName(r) + "')"); 1203 } 1204 1205 for (int i = 0; i < SIZE; i++) { 1206 where[i] = "a LIKE '*e*'"; 1207 1208 } 1209 } 1210 1211 public void testRun() { 1212 for (int i = 0; i < SIZE; i++) { 1213 mDatabase.query("t3", COLUMNS, where[i], null, null, null, null); 1214 } 1215 } 1216 } 1217 1218 public static final String[] ONES = 1219 {"zero", "one", "two", "three", "four", "five", "six", "seven", 1220 "eight", "nine", "ten", "eleven", "twelve", "thirteen", 1221 "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", 1222 "nineteen"}; 1223 1224 public static final String[] TENS = 1225 {"", "ten", "twenty", "thirty", "forty", "fifty", "sixty", 1226 "seventy", "eighty", "ninety"}; 1227 } 1228
