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 junit.framework.Assert;
20
21import android.content.ContentValues;
22import android.content.Context;
23import android.database.Cursor;
24import android.database.sqlite.SQLiteDatabase;
25import android.provider.Contacts;
26import android.provider.Contacts.People;
27import android.test.PerformanceTestCase;
28import android.test.TestCase;
29
30import java.io.File;
31import java.util.Random;
32
33/**
34 * Database Performance Tests
35 *
36 */
37
38public 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