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