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