· 9 years ago · Jul 17, 2017, 03:46 PM
1import static java.lang.System.out;
2
3import java.math.BigInteger;
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.PreparedStatement;
7import java.sql.ResultSet;
8import java.sql.Statement;
9import java.util.ArrayList;
10import java.util.Random;
11
12public class driver3 {
13
14 // create database database1
15 // CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
16 // GRANT ALL ON *.* TO 'user1'@'localhost';
17
18 public static String tablename = "table4";
19
20 public static String log(String s) {
21 try {
22 return s;
23 } finally {
24 // out.println(s);
25 }
26 }
27
28 // byte[] key byte ordering is BigInteger compatible
29 public static void test(int keySpaceSize, final boolean index, final int numberOfBytes, final boolean asIndividualINTs, final int selectCount,
30 final int insertCount) throws Throwable {
31 if (numberOfBytes < 1) {
32 throw new UnsupportedOperationException();
33 }
34 if (numberOfBytes % 4 != 0 && numberOfBytes > 0) {
35 throw new UnsupportedOperationException();
36 }
37 Class.forName("com.mysql.jdbc.Driver").newInstance();
38 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/database1", "user1", "pass1");
39
40 Statement stat = connection.createStatement();
41 stat.execute(log("DROP TABLE IF EXISTS " + tablename));
42 int numberOfIntCols = numberOfBytes / 4;
43 StringBuilder b = new StringBuilder();
44 StringBuilder b2 = new StringBuilder();
45 StringBuilder b3 = new StringBuilder();
46 for (int i = 0; i < numberOfIntCols; i++) {
47 b.append("key" + i + " INTEGER, ");
48 b2.append("key" + i + ", ");
49 b3.append("?, ");
50 }
51 String s2 = b2.substring(0, b2.length() - ", ".length());
52 String s3 = b3 + "?";
53 if (asIndividualINTs) {
54 stat.execute(log("CREATE TABLE IF NOT EXISTS " + tablename + " (" + b + "string1 VARCHAR(254)" + (index ? ", INDEX index1 (" + s2 + ")" : "") + ")"));
55 } else {
56 stat.execute(log("CREATE TABLE IF NOT EXISTS " + tablename + " (key1 BINARY(" + numberOfBytes + "), string1 VARCHAR(254)"
57 + (index ? ", INDEX index1 (key1)" : "") + ")"));
58 }
59 connection.setAutoCommit(false);
60 Random gen = new Random();
61 ArrayList<byte[]> keys = new ArrayList<byte[]>();
62 // Prevent out of memory;
63int batchSize = 10000;
64 int batches = keySpaceSize / batchSize;
65 keySpaceSize = keySpaceSize * batchSize;
66 for (int ba = 0; ba < batches; ba++) {
67 PreparedStatement preparedStatement;
68 if (asIndividualINTs) {
69 preparedStatement = connection.prepareStatement(log("INSERT INTO " + tablename + "(" + s2 + ", string1) values (" + s3 + ")"));
70 for (int i = 0; i < batchSize; i++) {
71 byte[] key = new byte[numberOfBytes];
72
73 for (int temp, byteindex = 0, j = numberOfIntCols; j != 0; j--) {
74 preparedStatement.setInt(j, temp = gen.nextInt());
75
76 for (int k = 24; k != -8; k -= 8) {
77 key[byteindex++] = (byte) ((temp >>> k) & 0xFF);
78 }
79 }
80 preparedStatement.setString(numberOfIntCols + 1, "someemail@gmail.com");
81 preparedStatement.addBatch();
82 if (ba == 0) {
83 // last batch gather keys
84 keys.add(key);
85 }
86 }
87 } else {
88 preparedStatement = connection.prepareStatement(log("INSERT INTO " + tablename + "(key1, string1) values (?, ?)"));
89 for (int i = 0; i < batchSize; i++) {
90 byte[] key = new byte[numberOfBytes];
91
92 for (int temp, byteindex = 0, j = numberOfIntCols; j != 0; j--) {
93 temp = gen.nextInt();
94 for (int k = 24; k != -8; k -= 8) {
95 key[byteindex++] = (byte) ((temp >>> k) & 0xFF);
96 }
97 }
98 preparedStatement.setBytes(1, key);
99 preparedStatement.setString(2, "someemail@gmail.com");
100 preparedStatement.addBatch();
101 if (ba == 0) {
102 // last batch gather keys
103 keys.add(key);
104 }
105 }
106 }
107 preparedStatement.executeBatch();
108 connection.commit();
109 preparedStatement = null;
110 System.gc();
111 }
112 int[] indexesToSelect = new int[selectCount];
113 for (int i = 0; i < indexesToSelect.length; i++) {
114 indexesToSelect[i] = gen.nextInt(keys.size());
115 }
116 long start = System.nanoTime();
117 if (asIndividualINTs) {
118 String query = "SELECT * FROM " + tablename + " where ";
119 for (int i = 0; i < numberOfIntCols; i++) {
120 query += "key" + i + " = ? and ";
121 }
122 query = query.substring(0, query.length() - (" and ".length()));
123 final int max = numberOfIntCols * 4 - 1;
124 for (int i = 0; i < selectCount; i++) {
125 byte[] key = keys.get(indexesToSelect[i]);
126 PreparedStatement preparedStatement2 = connection.prepareStatement(query);
127 for (int j = 1; j <= numberOfIntCols; j++) {
128 int temp2 = (j - 1) * 4;
129 preparedStatement2.setInt(j, ((((int) key[max - (temp2 + 3)]) & 0xFF) << 24) | (((int) (key[max - (temp2 + 2)]) & 0xFF) << 16)
130 | ((((int) (key[max - (temp2 + 1)]) & 0xFF) << 8)) | ((((int) key[max - (temp2)]) & 0xFF)));
131 }
132 ResultSet result = preparedStatement2.executeQuery();
133 if (!result.next()) {
134 out.println("Missing " + new BigInteger(key).toString(Character.MAX_RADIX));
135 break;
136 }
137
138 }
139
140 } else {
141 String query = log("SELECT * FROM " + tablename + " where key1 = ?");
142 for (int i = 0; i < selectCount; i++) {
143 byte[] key = keys.get(indexesToSelect[i]);
144 PreparedStatement preparedStatement2 = connection.prepareStatement(query);
145 preparedStatement2.setBytes(1, key);
146 ResultSet result = preparedStatement2.executeQuery();
147 if (!result.next()) {
148 out.println("Missing " + new BigInteger(key).toString(Character.MAX_RADIX));
149 break;
150 }
151 }
152 }
153 out.println((System.nanoTime() - start) + " ns " + ((System.nanoTime() - start) / 1000000) + " ms " + "to select " + selectCount + " in a keyspace of "
154 + keySpaceSize + (index ? "" : " INDEXED") + (asIndividualINTs ? (numberOfIntCols + " INTS") : "BYTE(" + numberOfBytes + ")"));
155 keys = null;
156 {
157
158 ArrayList<byte[]> keys2 = new ArrayList<byte[]>();
159 for (int i = 0; i < insertCount; i++) {
160 byte[] key = new byte[numberOfBytes];
161 for (int temp, byteindex = 0, j = numberOfIntCols; j != 0; j--) {
162 temp = gen.nextInt();
163 for (int k = 24; k != -8; k -= 8) {
164 key[byteindex++] = (byte) ((temp >>> k) & 0xFF);
165 }
166 }
167 keys2.add(key);
168 }
169 long start2 = System.nanoTime();
170 if (asIndividualINTs) {
171 final int max = numberOfIntCols * 4 - 1;
172 for (byte[] key : keys2) {
173 PreparedStatement preparedStatement2 = connection.prepareStatement("INSERT INTO " + tablename + "(" + s2 + ", string1) values (" + s3 + ")");
174
175 for (int j = 1; j <= numberOfIntCols; j++) {
176 int temp2 = (j - 1) * 4;
177 preparedStatement2.setInt(j, ((((int) key[max - (temp2 + 3)]) & 0xFF) << 24) | (((int) (key[max - (temp2 + 2)]) & 0xFF) << 16)
178 | ((((int) (key[max - (temp2 + 1)]) & 0xFF) << 8)) | ((((int) key[max - (temp2)]) & 0xFF)));
179 }
180 preparedStatement2.setString(numberOfIntCols + 1, "someemail@gmail.com");
181 preparedStatement2.execute();
182
183 }
184 } else {
185 for (byte[] key : keys2) {
186 PreparedStatement preparedStatement2 = connection.prepareStatement("INSERT INTO " + tablename + "(key1, string1) values (?, ?)");
187 preparedStatement2.setBytes(1, key);
188 preparedStatement2.setString(2, "");
189 preparedStatement2.execute();
190 }
191 }
192 out.println((System.nanoTime() - start) + " ns " + ((System.nanoTime() - start) / 1000000) + " ms " + "to Insert " + insertCount
193 + " in a keyspace of " + keySpaceSize + (index ? "" : " INDEXED")
194 + (asIndividualINTs ? (numberOfIntCols + " INTS") : "BYTE(" + numberOfBytes + ")"));
195 }
196
197 }
198
199 public static void main(String[] args) {
200 try {
201 test(400000, true, 4, true, 1000, 1000);
202 test(400000, true, 4, false, 1000, 1000);
203 test(400000, false, 4, true, 1000, 1000);
204 test(400000, false, 4, false, 1000, 1000);
205 test(400000, true, 20, true, 1000, 1000);
206 test(400000, true, 20, false, 1000, 1000);
207 test(400000, false, 20, true, 1000, 1000);
208 test(400000, false, 20, false, 1000, 1000);
209 test(400000, true, 40, true, 1000, 1000);
210 test(400000, true, 40, false, 1000, 1000);
211 test(400000, false, 40, true, 1000, 1000);
212 test(400000, false, 40, false, 1000, 1000);
213
214// test(40000000, true, 40, false, 1000, 1000);
215// test(40000000, true, 40, true, 1000, 1000);
216
217 } catch (Throwable e1) {
218
219 e1.printStackTrace();
220 }
221 }
222}