|
1
5 package org.h2.test.db;
6
7 import java.sql.Connection  ;
8 import java.sql.PreparedStatement ;
9 import java.sql.ResultSet ;
10 import java.sql.Statement ;
11 import java.util.ArrayList ;
12 import java.util.HashMap ;
13 import java.util.Random ;
14 import java.util.TreeSet ;
15
16 import org.h2.test.TestBase;
17
18 public class TestOptimizations extends TestBase {
19
20 public void test() throws Exception {
21 if(config.networked) {
22 return;
23 }
24 testQueryCacheTimestamp();
25 testQueryCacheSpeed();
26 testQueryCache(true);
27 testQueryCache(false);
28 testIn();
29 testMinMaxCountOptimization(true);
30 testMinMaxCountOptimization(false);
31 }
32
33 private void testQueryCacheTimestamp() throws Exception {
34 deleteDb("optimizations");
35 Connection conn=getConnection("optimizations");
36 PreparedStatement prep = conn.prepareStatement("SELECT CURRENT_TIMESTAMP()");
37 ResultSet rs = prep.executeQuery();
38 rs.next();
39 String a = rs.getString(1);
40 Thread.sleep(50);
41 rs = prep.executeQuery();
42 rs.next();
43 String b = rs.getString(1);
44 checkFalse(a.equals(b));
45 conn.close();
46 }
47
48 private void testQueryCacheSpeed() throws Exception {
49 deleteDb("optimizations");
50 Connection conn=getConnection("optimizations");
51 Statement stat = conn.createStatement();
52 testQuerySpeed(stat, "select sum(x) from system_range(1, 10000) a where a.x in (select b.x from system_range(1, 30) b)");
53 testQuerySpeed(stat, "select sum(a.n), sum(b.x) from system_range(1, 100) b, (select sum(x) n from system_range(1, 4000)) a");
54 conn.close();
55 }
56
57 private void testQuerySpeed(Statement stat, String sql) throws Exception {
58 stat.execute("set OPTIMIZE_REUSE_RESULTS 0");
59 stat.execute(sql);
60 long time = System.currentTimeMillis();
61 stat.execute(sql);
62 time = System.currentTimeMillis() - time;
63 stat.execute("set OPTIMIZE_REUSE_RESULTS 1");
64 stat.execute(sql);
65 long time2 = System.currentTimeMillis();
66 stat.execute(sql);
67 time2 = System.currentTimeMillis() - time2;
68 if(time2 > time) {
69 error("not optimized: " + time + " optimized: " + time2+ " sql:"+sql);
70 }
71 }
72
73 private void testQueryCache(boolean optimize) throws Exception {
74 deleteDb("optimizations");
75 Connection conn=getConnection("optimizations");
76 Statement stat = conn.createStatement();
77 if(optimize) {
78 stat.execute("set OPTIMIZE_REUSE_RESULTS 1");
79 } else {
80 stat.execute("set OPTIMIZE_REUSE_RESULTS 0");
81 }
82 stat.execute("create table test(id int)");
83 stat.execute("create table test2(id int)");
84 stat.execute("insert into test values(1), (1), (2)");
85 stat.execute("insert into test2 values(1)");
86 PreparedStatement prep = conn.prepareStatement("select * from test where id = (select id from test2)");
87 ResultSet rs1 = prep.executeQuery();
88 rs1.next();
89 check(rs1.getInt(1), 1);
90 rs1.next();
91 check(rs1.getInt(1), 1);
92 checkFalse(rs1.next());
93
94 stat.execute("update test2 set id = 2");
95 ResultSet rs2 = prep.executeQuery();
96 rs2.next();
97 check(rs2.getInt(1), 2);
98
99 conn.close();
100 }
101
102 private void testMinMaxCountOptimization(boolean memory) throws Exception {
103 deleteDb("optimizations");
104 Connection conn=getConnection("optimizations");
105 Statement stat = conn.createStatement();
106 stat.execute("create "+(memory?"memory":"") +" table test(id int primary key, value int)");
107 stat.execute("create index idxvalueid on test(value, id);");
108 int len = getSize(1000, 10000);
109 HashMap map = new HashMap ();
110 TreeSet set = new TreeSet ();
111 Random random = new Random (1);
112 for(int i=0; i<len ; i++) {
113 if(i==len / 2) {
114 if(!config.memory) {
115 conn.close();
116 conn=getConnection("optimizations");
117 stat = conn.createStatement();
118 }
119 }
120 switch(random.nextInt(10)) {
121 case 0:
122 case 1:
123 case 2:
124 case 3:
125 case 4:
126 case 5:
127 if(random.nextInt(1000)==1) {
128 stat.execute("insert into test values("+i+", null)");
129 map.put(new Integer (i), null);
130 } else {
131 int value = random.nextInt();
132 stat.execute("insert into test values("+i+", "+value+")");
133 map.put(new Integer (i), new Integer (value));
134 set.add(new Integer (value));
135 }
136 break;
137 case 6:
138 case 7:
139 case 8: {
140 if(map.size()>0) {
141 for(int j=random.nextInt(i), k=0; k<10; k++, j++) {
142 if(map.containsKey(new Integer (j))) {
143 Integer x = (Integer ) map.remove(new Integer (j));
144 if(x != null) {
145 set.remove(x);
146 }
147 stat.execute("delete from test where id="+j);
148 }
149 }
150 }
151 break;
152 }
153 case 9: {
154 ArrayList list = new ArrayList (map.values());
155 int count = list.size();
156 Integer min = null, max = null;
157 if(count > 0) {
158 min = (Integer ) set.first();
159 max = (Integer ) set.last();
160 }
161 ResultSet rs = stat.executeQuery("select min(value), max(value), count(*) from test");
162 rs.next();
163 Integer minDb = (Integer )rs.getObject(1);
164 Integer maxDb = (Integer )rs.getObject(2);
165 int countDb = rs.getInt(3);
166 check(minDb, min);
167 check(maxDb, max);
168 check(countDb, count);
169 }
170 }
171 }
172 conn.close();
173 }
174
175 private void testIn() throws Exception {
176 deleteDb("optimizations");
177 Connection conn=getConnection("optimizations");
178 Statement stat = conn.createStatement();
179 stat.execute("create table test(id int primary key, name varchar)");
180 stat.execute("insert into test values(1, 'Hello')");
181 stat.execute("insert into test values(2, 'World')");
182 PreparedStatement prep;
183 ResultSet rs;
184
185 prep = conn.prepareStatement("select * from test t1 where t1.id in(?)");
186 prep.setInt(1, 1);
187 rs = prep.executeQuery();
188 rs.next();
189 check(rs.getInt(1), 1);
190 check(rs.getString(2), "Hello");
191 checkFalse(rs.next());
192
193 prep = conn.prepareStatement("select * from test t1 where t1.id in(?, ?) order by id");
194 prep.setInt(1, 1);
195 prep.setInt(2, 2);
196 rs = prep.executeQuery();
197 rs.next();
198 check(rs.getInt(1), 1);
199 check(rs.getString(2), "Hello");
200 rs.next();
201 check(rs.getInt(1), 2);
202 check(rs.getString(2), "World");
203 checkFalse(rs.next());
204
205 prep = conn.prepareStatement("select * from test t1 where t1.id in(select t2.id from test t2 where t2.id=?)");
206 prep.setInt(1, 2);
207 rs = prep.executeQuery();
208 rs.next();
209 check(rs.getInt(1), 2);
210 check(rs.getString(2), "World");
211 checkFalse(rs.next());
212
213 prep = conn.prepareStatement("select * from test t1 where t1.id in(select t2.id from test t2 where t2.id=? and t1.id<>t2.id)");
214 prep.setInt(1, 2);
215 rs = prep.executeQuery();
216 checkFalse(rs.next());
217
218 prep = conn.prepareStatement("select * from test t1 where t1.id in(select t2.id from test t2 where t2.id in(cast(?+10 as varchar)))");
219 prep.setInt(1, 2);
220 rs = prep.executeQuery();
221 checkFalse(rs.next());
222
223 conn.close();
224 }
225
226
227 }
228 |
Popular Tags |
Java API By Example, From Geeks To Geeks.