|
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
12 import org.h2.test.TestBase;
13 import org.h2.util.MemoryUtils;
14
15 public class TestBigDb extends TestBase {
16
17 public void test() throws Exception {
18 if(config.memory) {
19 return;
20 }
21 if(config.networked && config.big) {
22 return;
23 }
24 testLargeTable();
25 testInsert();
26 testLeftSummary();
27 }
28
29 private void testLargeTable() throws Exception {
30 deleteDb("bigDb");
31 Connection conn = getConnection("bigDb");
32 Statement stat = conn.createStatement();
33 stat.execute(
34 "CREATE CACHED TABLE PAB_ARTLEV("+
35 "MUTATIECODE CHAR(1) DEFAULT CAST(RAND()*9 AS INT),"+
36 "PRDCODE CHAR(20) DEFAULT SECURE_RAND(10),"+
37 "ORGCODESUPPLIER CHAR(13) DEFAULT SECURE_RAND(6),"+
38 "PRDCODEGTIN CHAR(14) DEFAULT SECURE_RAND(7),"+
39 "PRDCODEMF CHAR(20) DEFAULT SECURE_RAND(10),"+
40 "ORGCODEMF CHAR(13) DEFAULT SECURE_RAND(6),"+
41 "SUBSTITUTEDBY CHAR(20) DEFAULT SECURE_RAND(10),"+
42 "SUBSTITUTEDBYGTIN CHAR(14) DEFAULT SECURE_RAND(7),"+
43 "SUBSTITUTIONFOR CHAR(20) DEFAULT SECURE_RAND(10),"+
44 "SUBSTITUTIONFORGTIN CHAR(14) DEFAULT SECURE_RAND(7),"+
45 "VERWERKBAAR CHAR(2) DEFAULT SECURE_RAND(1),"+
46 "BESTELBAAR CHAR(2) DEFAULT SECURE_RAND(1),"+
47 "AANTALGEBRUIKSEENHEDEN DECIMAL(7,2) DEFAULT RAND(),"+
48 "PRIMARYUNITCODE CHAR(3) DEFAULT SECURE_RAND(1),"+
49 "RATEPRICEORDERUNIT DECIMAL(9,3) DEFAULT RAND(),"+
50 "ORDERUNITCODE CHAR(3) DEFAULT SECURE_RAND(1),"+
51 "ORDERQTYMIN DECIMAL(6,1) DEFAULT RAND(),"+
52 "ORDERQTYLOTSIZE DECIMAL(6,1) DEFAULT RAND(),"+
53 "ORDERUNITCODE2 CHAR(3) DEFAULT SECURE_RAND(1),"+
54 "PRICEGROUP CHAR(20) DEFAULT SECURE_RAND(10),"+
55 "LEADTIME INTEGER DEFAULT RAND(),"+
56 "LEADTIMEUNITCODE CHAR(3) DEFAULT SECURE_RAND(1),"+
57 "PRDGROUP CHAR(10) DEFAULT SECURE_RAND(5),"+
58 "WEIGHTGROSS DECIMAL(7,3) DEFAULT RAND(),"+
59 "WEIGHTUNITCODE CHAR(3) DEFAULT SECURE_RAND(1),"+
60 "PACKUNITCODE CHAR(3) DEFAULT SECURE_RAND(1),"+
61 "PACKLENGTH DECIMAL(7,3) DEFAULT RAND(),"+
62 "PACKWIDTH DECIMAL(7,3) DEFAULT RAND(),"+
63 "PACKHEIGHT DECIMAL(7,3) DEFAULT RAND(),"+
64 "SIZEUNITCODE CHAR(3) DEFAULT SECURE_RAND(1),"+
65 "STATUCCODE CHAR(3) DEFAULT SECURE_RAND(1),"+
66 "INTRASTATCODE CHAR(12) DEFAULT SECURE_RAND(6),"+
67 "PRDTITLE CHAR(50) DEFAULT SECURE_RAND(25),"+
68 "VALIDFROM DATE DEFAULT NOW(),"+
69 "MUTATIEDATUM DATE DEFAULT NOW())");
70 int len = getSize(10, 50000);
71 try {
72 PreparedStatement prep = conn.prepareStatement("INSERT INTO PAB_ARTLEV(PRDCODE) VALUES('abc' || ?)");
73 long time = System.currentTimeMillis();
74 for(int i=0; i<len; i++) {
75 if((i % 1000) == 0) {
76 long t = System.currentTimeMillis();
77 if(t-time > 1000) {
78 time = t;
79 int free = MemoryUtils.getMemoryFree();
80 System.out.println("i: " + i + " free: " + free + " used: " + MemoryUtils.getMemoryUsed());
81 }
82 }
83 prep.setInt(1, i);
84 prep.execute();
85 }
86 stat.execute("CREATE INDEX IDX_PAB_ARTLEV_PRDCODE ON PAB_ARTLEV(PRDCODE)");
87 ResultSet rs = stat.executeQuery("SELECT * FROM PAB_ARTLEV");
88 int columns = rs.getMetaData().getColumnCount();
89 while(rs.next()) {
90 for(int i=0; i<columns; i++) {
91 rs.getString(i+1);
92 }
93 }
94 } catch(OutOfMemoryError e) {
95 TestBase.logError("memory", e);
96 conn.close();
97 throw e;
98 }
99 conn.close();
100 }
101
102 private void testLeftSummary() throws Exception {
103 deleteDb("bigDb");
104 Connection conn = getConnection("bigDb");
105 Statement stat = conn.createStatement();
106 stat.execute("CREATE TABLE TEST(ID INT, NEG INT AS -ID, NAME VARCHAR, PRIMARY KEY(ID, NAME))");
107 stat.execute("CREATE INDEX IDXNEG ON TEST(NEG, NAME)");
108 PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST(ID, NAME) VALUES(?, '1234567890')");
109 int len = getSize(10, 1000);
110 int block = getSize(3, 10);
111 int left, x = 0;
112 for(int i=0; i<len; i++) {
113 left = x+block/2;
114 for(int j=0; j<block; j++) {
115 prep.setInt(1, x++);
116 prep.execute();
117 }
118 stat.execute("DELETE FROM TEST WHERE ID>" + left);
119 ResultSet rs = stat.executeQuery("SELECT COUNT(*) FROM TEST");
120 rs.next();
121 int count = rs.getInt(1);
122 trace("count: " + count);
123 }
124 conn.close();
125 }
126
127 private void testInsert() throws Exception {
128 deleteDb("bigDb");
129 Connection conn = getConnection("bigDb");
130 Statement stat = conn.createStatement();
131 stat.execute("CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR)");
132 PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST(NAME) VALUES('Hello World')");
133 int len = getSize(1000, 10000);
134 long time = System.currentTimeMillis();
135 for(int i=0; i<len; i++) {
136 if(i % 1000 == 0) {
137 long t = System.currentTimeMillis();
138 time = t;
139 trace("rows:" + i + " time:" + (t-time));
140 Thread.yield();
141 }
142 prep.execute();
143 }
144 conn.close();
145 }
146
147
148 }
149 |
Popular Tags |
Java API By Example, From Geeks To Geeks.