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