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