SQLite

Check-in [ad71c72be5]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add some timing tests to the amatch test script.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ad71c72be56786d8dcf6917efd10a8d3c2d50419
User & Date: drh 2013-09-30 19:05:03.027
Context
2013-09-30
19:33
Fix a minor typo in a comment in where.c. (check-in: 9c9fa151e9 user: drh tags: trunk)
19:05
Add some timing tests to the amatch test script. (check-in: ad71c72be5 user: drh tags: trunk)
18:14
Fix a performance problem in the FTS4 auxiliary functions triggered by an OR clause in the full-text query. (check-in: fa0f2f0e3e user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/amatch1.test.
︙︙
31
32
33
34
35
36
37

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61


62
63
64
65
66













67
68










69




70
















71
72


do_test amatch1-1.0 {
  db eval {
    CREATE VIRTUAL TABLE t1 USING fts4(words); --, tokenize porter);
  }
  fts_kjv_genesis
  db eval {

    CREATE VIRTUAL TABLE temp.t1aux USING fts4aux(main, t1);
    SELECT term FROM t1aux WHERE col=0 ORDER BY 1 LIMIT 5
  }
} {a abated abel abelmizraim abidah}
do_test amatch1-1.1 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 ORDER BY 1 LIMIT 5
  }
} {baalhanan babel back backward bad}
do_test amatch1-1.2 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 LIMIT 5
  }
} {baalhanan babel back backward bad}

# Load the amatch extension
load_static_extension db amatch

do_test amatch1-2.0 {
  db eval {
    CREATE TABLE costs(iLang, cFrom, cTo, Cost);
    INSERT INTO costs VALUES(0, '', '?', 100);
    INSERT INTO costs VALUES(0, '?', '', 100);
    INSERT INTO costs VALUES(0, '?', '?', 150);


    CREATE VIRTUAL TABLE t2 USING approximate_match(
      vocabulary_table=t1aux,
      vocabulary_word=term,
      edit_distances=costs
    );













    SELECT word, distance FROM t2
     WHERE word MATCH 'josxph' AND distance<300;










  }




} {joseph 150}

















finish_test








>


















|
<




>
>





>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117


do_test amatch1-1.0 {
  db eval {
    CREATE VIRTUAL TABLE t1 USING fts4(words); --, tokenize porter);
  }
  fts_kjv_genesis
  db eval {
    INSERT INTO t1(t1) VALUES('optimize');
    CREATE VIRTUAL TABLE temp.t1aux USING fts4aux(main, t1);
    SELECT term FROM t1aux WHERE col=0 ORDER BY 1 LIMIT 5
  }
} {a abated abel abelmizraim abidah}
do_test amatch1-1.1 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 ORDER BY 1 LIMIT 5
  }
} {baalhanan babel back backward bad}
do_test amatch1-1.2 {
  db eval {
    SELECT term FROM t1aux WHERE term>'b' AND col=0 LIMIT 5
  }
} {baalhanan babel back backward bad}

# Load the amatch extension
load_static_extension db amatch

do_execsql_test amatch1-2.0 {

    CREATE TABLE costs(iLang, cFrom, cTo, Cost);
    INSERT INTO costs VALUES(0, '', '?', 100);
    INSERT INTO costs VALUES(0, '?', '', 100);
    INSERT INTO costs VALUES(0, '?', '?', 150);
    CREATE TABLE vocab(w TEXT UNIQUE);
    INSERT OR IGNORE INTO vocab SELECT term FROM t1aux;
    CREATE VIRTUAL TABLE t2 USING approximate_match(
      vocabulary_table=t1aux,
      vocabulary_word=term,
      edit_distances=costs
    );
    CREATE VIRTUAL TABLE t3 USING approximate_match(
      vocabulary_table=vocab,
      vocabulary_word=w,
      edit_distances=costs
    );
    CREATE VIRTUAL TABLE t4 USING approximate_match(
        vocabulary_table=vtemp,
        vocabulary_word=w,
        edit_distances=costs
      );
} {}
puts "Query against fts4aux: [time {
  do_execsql_test amatch1-2.1 {
      SELECT word, distance FROM t2
       WHERE word MATCH 'josxph' AND distance<300;
  } {joseph 150}} 1]"
puts "Query against ordinary table: [time {
  do_execsql_test amatch1-2.2 {
      SELECT word, distance FROM t3
       WHERE word MATCH 'josxph' AND distance<300;
  } {joseph 150}} 1]"
puts "Temp table initialized from fts4aux: [time {
  do_execsql_test amatch1-2.3a {
      CREATE TEMP TABLE vtemp(w TEXT UNIQUE);
      INSERT OR IGNORE INTO vtemp SELECT term FROM t1aux;
  } {}} 1]"
puts "Query against temp table: [time {
  do_execsql_test amatch1-2.3b {
      SELECT word, distance FROM t4
       WHERE word MATCH 'josxph' AND distance<300;
  } {joseph 150}} 1]"
do_execsql_test amatch1-2.11 {
    SELECT word, distance FROM t2
     WHERE word MATCH 'joxxph' AND distance<=300;
} {joseph 300}
do_execsql_test amatch1-2.12 {
    SELECT word, distance FROM t3
     WHERE word MATCH 'joxxph' AND distance<=300;
} {joseph 300}
do_execsql_test amatch1-2.21 {
    SELECT word, distance FROM t2
     WHERE word MATCH 'joxxph' AND distance<300;
} {}
do_execsql_test amatch1-2.22 {
    SELECT word, distance FROM t3
     WHERE word MATCH 'joxxph' AND distance<300;
} {}

finish_test