INList.java
text/plain
import java.sql.* ;
/*
Query huge inlists, test for buffer overflow and performance using in-lists vs.
parameterized statements, and perhaps batched statements (not yet)
Results:
MySQL blows PostgreSQL away here, and that's AFTER doing a VACUUMDB in PostgreSQL
PostgreSQL does ok with VACUUMDB to 700 entries, the prepared statements are better.
MySQL: IN lists are always superior to any practical limit (tested to 100,000 elements)
Local-to-local on AMD1600Mhz, MySQL 3.23.56 with JDBC driver 3.0.8
[MYSQL was much better here...]
Table update time: 109927 ms
For 10,000 -> 100,000 by 10,000
Elapsed time for 90000 IN list elements: 1449 ms
62 elements/ms
Elapsed time for 90000 parameterized elements: 9283 ms
9 elements/ms
For 100 -> 10,000 by 100
Last received via IN list: 99
Elapsed time for 100 IN list elements: 6 ms
16 elements/ms
Elapsed time for 100 parameterized elements: 20 ms
5 elements/ms
Last received via parameterized elements: 99
Last received via IN list: 199
Elapsed time for 200 IN list elements: 7 ms
28 elements/ms
Elapsed time for 200 parameterized elements: 45 ms
4 elements/ms
Last received via parameterized elements: 199
Last received via IN list: 299
Elapsed time for 300 IN list elements: 22 ms
13 elements/ms
Elapsed time for 300 parameterized elements: 64 ms
4 elements/ms
Last received via parameterized elements: 299
Last received via IN list: 399
Elapsed time for 400 IN list elements: 39 ms
10 elements/ms
Elapsed time for 400 parameterized elements: 63 ms
6 elements/ms
Last received via parameterized elements: 399
Last received via IN list: 499
Elapsed time for 500 IN list elements: 12 ms
41 elements/ms
Elapsed time for 500 parameterized elements: 116 ms
4 elements/ms
Last received via parameterized elements: 499
Last received via IN list: 599
Elapsed time for 600 IN list elements: 8 ms
75 elements/ms
Elapsed time for 600 parameterized elements: 65 ms
9 elements/ms
Last received via parameterized elements: 599
Last received via IN list: 699
Elapsed time for 700 IN list elements: 19 ms
36 elements/ms
Elapsed time for 700 parameterized elements: 73 ms
9 elements/ms
Last received via parameterized elements: 699
Last received via IN list: 799
Elapsed time for 800 IN list elements: 12 ms
66 elements/ms
Elapsed time for 800 parameterized elements: 84 ms
9 elements/ms
Last received via parameterized elements: 799
Last received via IN list: 899
Elapsed time for 900 IN list elements: 12 ms
75 elements/ms
Elapsed time for 900 parameterized elements: 97 ms
9 elements/ms
Last received via parameterized elements: 899
Last received via IN list: 999
Elapsed time for 1000 IN list elements: 15 ms
66 elements/ms
Elapsed time for 1000 parameterized elements: 109 ms
9 elements/ms
Last received via parameterized elements: 999
Last received via IN list: 1099
Elapsed time for 1100 IN list elements: 16 ms
68 elements/ms
Elapsed time for 1100 parameterized elements: 115 ms
9 elements/ms
Last received via parameterized elements: 1099
Last received via IN list: 1199
Elapsed time for 1200 IN list elements: 16 ms
75 elements/ms
Elapsed time for 1200 parameterized elements: 138 ms
8 elements/ms
Last received via parameterized elements: 1199
Last received via IN list: 1299
Elapsed time for 1300 IN list elements: 19 ms
68 elements/ms
Elapsed time for 1300 parameterized elements: 136 ms
9 elements/ms
Last received via parameterized elements: 1299
Last received via IN list: 1399
Elapsed time for 1400 IN list elements: 21 ms
66 elements/ms
Elapsed time for 1400 parameterized elements: 150 ms
9 elements/ms
Last received via parameterized elements: 1399
Last received via IN list: 1499
Elapsed time for 1500 IN list elements: 21 ms
71 elements/ms
Elapsed time for 1500 parameterized elements: 158 ms
9 elements/ms
Last received via parameterized elements: 1499
Last received via IN list: 1599
Elapsed time for 1600 IN list elements: 22 ms
72 elements/ms
Elapsed time for 1600 parameterized elements: 178 ms
8 elements/ms
Last received via parameterized elements: 1599
Last received via IN list: 1699
Elapsed time for 1700 IN list elements: 22 ms
77 elements/ms
Elapsed time for 1700 parameterized elements: 180 ms
9 elements/ms
Last received via parameterized elements: 1699
Last received via IN list: 1799
Elapsed time for 1800 IN list elements: 26 ms
69 elements/ms
Elapsed time for 1800 parameterized elements: 205 ms
8 elements/ms
Last received via parameterized elements: 1799
Last received via IN list: 1899
Elapsed time for 1900 IN list elements: 57 ms
33 elements/ms
Elapsed time for 1900 parameterized elements: 198 ms
9 elements/ms
Last received via parameterized elements: 1899
Last received via IN list: 1999
Elapsed time for 2000 IN list elements: 29 ms
68 elements/ms
Elapsed time for 2000 parameterized elements: 215 ms
9 elements/ms
Last received via parameterized elements: 1999
Last received via IN list: 2099
Elapsed time for 2100 IN list elements: 30 ms
70 elements/ms
Elapsed time for 2100 parameterized elements: 222 ms
9 elements/ms
Last received via parameterized elements: 2099
Last received via IN list: 2199
Elapsed time for 2200 IN list elements: 32 ms
68 elements/ms
Elapsed time for 2200 parameterized elements: 243 ms
9 elements/ms
Last received via parameterized elements: 2199
Last received via IN list: 2299
Elapsed time for 2300 IN list elements: 32 ms
71 elements/ms
Elapsed time for 2300 parameterized elements: 241 ms
9 elements/ms
Last received via parameterized elements: 2299
Last received via IN list: 2399
Elapsed time for 2400 IN list elements: 33 ms
72 elements/ms
Elapsed time for 2400 parameterized elements: 260 ms
9 elements/ms
Last received via parameterized elements: 2399
Last received via IN list: 2499
Elapsed time for 2500 IN list elements: 64 ms
39 elements/ms
Elapsed time for 2500 parameterized elements: 275 ms
9 elements/ms
Last received via parameterized elements: 2499
Last received via IN list: 2599
Elapsed time for 2600 IN list elements: 35 ms
74 elements/ms
Elapsed time for 2600 parameterized elements: 275 ms
9 elements/ms
Last received via parameterized elements: 2599
Last received via IN list: 2699
Elapsed time for 2700 IN list elements: 36 ms
75 elements/ms
Elapsed time for 2700 parameterized elements: 292 ms
9 elements/ms
Last received via parameterized elements: 2699
Last received via IN list: 2799
Elapsed time for 2800 IN list elements: 41 ms
68 elements/ms
Elapsed time for 2800 parameterized elements: 308 ms
9 elements/ms
Last received via parameterized elements: 2799
Last received via IN list: 2899
Elapsed time for 2900 IN list elements: 40 ms
72 elements/ms
Elapsed time for 2900 parameterized elements: 341 ms
8 elements/ms
Last received via parameterized elements: 2899
Last received via IN list: 2999
Elapsed time for 3000 IN list elements: 42 ms
71 elements/ms
Elapsed time for 3000 parameterized elements: 356 ms
8 elements/ms
Last received via parameterized elements: 2999
Last received via IN list: 3099
Elapsed time for 3100 IN list elements: 42 ms
73 elements/ms
Elapsed time for 3100 parameterized elements: 342 ms
9 elements/ms
Last received via parameterized elements: 3099
Last received via IN list: 3199
Elapsed time for 3200 IN list elements: 43 ms
74 elements/ms
Elapsed time for 3200 parameterized elements: 345 ms
9 elements/ms
Last received via parameterized elements: 3199
Last received via IN list: 3299
Elapsed time for 3300 IN list elements: 43 ms
76 elements/ms
Elapsed time for 3300 parameterized elements: 359 ms
9 elements/ms
Last received via parameterized elements: 3299
Last received via IN list: 3399
Elapsed time for 3400 IN list elements: 77 ms
44 elements/ms
Elapsed time for 3400 parameterized elements: 356 ms
9 elements/ms
Last received via parameterized elements: 3399
Last received via IN list: 3499
Elapsed time for 3500 IN list elements: 50 ms
70 elements/ms
Elapsed time for 3500 parameterized elements: 370 ms
9 elements/ms
Last received via parameterized elements: 3499
Last received via IN list: 3599
Elapsed time for 3600 IN list elements: 84 ms
42 elements/ms
Elapsed time for 3600 parameterized elements: 387 ms
9 elements/ms
Last received via parameterized elements: 3599
Last received via IN list: 3699
Elapsed time for 3700 IN list elements: 52 ms
71 elements/ms
Elapsed time for 3700 parameterized elements: 390 ms
9 elements/ms
Last received via parameterized elements: 3699
Last received via IN list: 3799
Elapsed time for 3800 IN list elements: 54 ms
70 elements/ms
Elapsed time for 3800 parameterized elements: 407 ms
9 elements/ms
Last received via parameterized elements: 3799
Last received via IN list: 3899
Elapsed time for 3900 IN list elements: 84 ms
46 elements/ms
Elapsed time for 3900 parameterized elements: 422 ms
9 elements/ms
Last received via parameterized elements: 3899
Last received via IN list: 3999
Elapsed time for 4000 IN list elements: 58 ms
68 elements/ms
Elapsed time for 4000 parameterized elements: 434 ms
9 elements/ms
Last received via parameterized elements: 3999
Last received via IN list: 4099
Elapsed time for 4100 IN list elements: 58 ms
70 elements/ms
Elapsed time for 4100 parameterized elements: 446 ms
9 elements/ms
Last received via parameterized elements: 4099
Last received via IN list: 4199
Elapsed time for 4200 IN list elements: 61 ms
68 elements/ms
Elapsed time for 4200 parameterized elements: 453 ms
9 elements/ms
Last received via parameterized elements: 4199
Last received via IN list: 4299
Elapsed time for 4300 IN list elements: 63 ms
68 elements/ms
Elapsed time for 4300 parameterized elements: 457 ms
9 elements/ms
Last received via parameterized elements: 4299
Last received via IN list: 4399
Elapsed time for 4400 IN list elements: 94 ms
46 elements/ms
Elapsed time for 4400 parameterized elements: 473 ms
9 elements/ms
Last received via parameterized elements: 4399
Last received via IN list: 4499
Elapsed time for 4500 IN list elements: 64 ms
70 elements/ms
Elapsed time for 4500 parameterized elements: 506 ms
8 elements/ms
Last received via parameterized elements: 4499
Last received via IN list: 4599
Elapsed time for 4600 IN list elements: 72 ms
63 elements/ms
Elapsed time for 4600 parameterized elements: 527 ms
8 elements/ms
Last received via parameterized elements: 4599
Last received via IN list: 4699
Elapsed time for 4700 IN list elements: 68 ms
69 elements/ms
Elapsed time for 4700 parameterized elements: 499 ms
9 elements/ms
Last received via parameterized elements: 4699
Last received via IN list: 4799
Elapsed time for 4800 IN list elements: 70 ms
68 elements/ms
Elapsed time for 4800 parameterized elements: 539 ms
8 elements/ms
Last received via parameterized elements: 4799
Last received via IN list: 4899
Elapsed time for 4900 IN list elements: 100 ms
49 elements/ms
Elapsed time for 4900 parameterized elements: 590 ms
8 elements/ms
Last received via parameterized elements: 4899
Last received via IN list: 4999
Elapsed time for 5000 IN list elements: 111 ms
45 elements/ms
Elapsed time for 5000 parameterized elements: 531 ms
9 elements/ms
Last received via parameterized elements: 4999
Last received via IN list: 5099
Elapsed time for 5100 IN list elements: 79 ms
64 elements/ms
Elapsed time for 5100 parameterized elements: 542 ms
9 elements/ms
Last received via parameterized elements: 5099
Last received via IN list: 5199
Elapsed time for 5200 IN list elements: 108 ms
48 elements/ms
Elapsed time for 5200 parameterized elements: 560 ms
9 elements/ms
Last received via parameterized elements: 5199
Last received via IN list: 5299
Elapsed time for 5300 IN list elements: 78 ms
67 elements/ms
Elapsed time for 5300 parameterized elements: 560 ms
9 elements/ms
Last received via parameterized elements: 5299
Last received via IN list: 5399
Elapsed time for 5400 IN list elements: 81 ms
66 elements/ms
Elapsed time for 5400 parameterized elements: 572 ms
9 elements/ms
Last received via parameterized elements: 5399
Last received via IN list: 5499
Elapsed time for 5500 IN list elements: 80 ms
68 elements/ms
Elapsed time for 5500 parameterized elements: 627 ms
8 elements/ms
Last received via parameterized elements: 5499
Last received via IN list: 5599
Elapsed time for 5600 IN list elements: 81 ms
69 elements/ms
Elapsed time for 5600 parameterized elements: 590 ms
9 elements/ms
Last received via parameterized elements: 5599
Last received via IN list: 5699
Elapsed time for 5700 IN list elements: 86 ms
66 elements/ms
Elapsed time for 5700 parameterized elements: 606 ms
9 elements/ms
Last received via parameterized elements: 5699
Last received via IN list: 5799
Elapsed time for 5800 IN list elements: 85 ms
68 elements/ms
Elapsed time for 5800 parameterized elements: 663 ms
8 elements/ms
Last received via parameterized elements: 5799
Last received via IN list: 5899
Elapsed time for 5900 IN list elements: 83 ms
71 elements/ms
Elapsed time for 5900 parameterized elements: 629 ms
9 elements/ms
Last received via parameterized elements: 5899
Last received via IN list: 5999
Elapsed time for 6000 IN list elements: 91 ms
65 elements/ms
Elapsed time for 6000 parameterized elements: 658 ms
9 elements/ms
Last received via parameterized elements: 5999
Last received via IN list: 6099
Elapsed time for 6100 IN list elements: 87 ms
70 elements/ms
Elapsed time for 6100 parameterized elements: 702 ms
8 elements/ms
Last received via parameterized elements: 6099
Last received via IN list: 6199
Elapsed time for 6200 IN list elements: 88 ms
70 elements/ms
Elapsed time for 6200 parameterized elements: 667 ms
9 elements/ms
Last received via parameterized elements: 6199
Last received via IN list: 6299
Elapsed time for 6300 IN list elements: 91 ms
69 elements/ms
Elapsed time for 6300 parameterized elements: 670 ms
9 elements/ms
Last received via parameterized elements: 6299
Last received via IN list: 6399
Elapsed time for 6400 IN list elements: 91 ms
70 elements/ms
Elapsed time for 6400 parameterized elements: 708 ms
9 elements/ms
Last received via parameterized elements: 6399
Last received via IN list: 6499
Elapsed time for 6500 IN list elements: 96 ms
67 elements/ms
Elapsed time for 6500 parameterized elements: 687 ms
9 elements/ms
Last received via parameterized elements: 6499
Last received via IN list: 6599
Elapsed time for 6600 IN list elements: 95 ms
69 elements/ms
Elapsed time for 6600 parameterized elements: 701 ms
9 elements/ms
Last received via parameterized elements: 6599
Last received via IN list: 6699
Elapsed time for 6700 IN list elements: 134 ms
50 elements/ms
Elapsed time for 6700 parameterized elements: 711 ms
9 elements/ms
Last received via parameterized elements: 6699
Last received via IN list: 6799
Elapsed time for 6800 IN list elements: 98 ms
69 elements/ms
Elapsed time for 6800 parameterized elements: 723 ms
9 elements/ms
Last received via parameterized elements: 6799
Last received via IN list: 6899
Elapsed time for 6900 IN list elements: 99 ms
69 elements/ms
Elapsed time for 6900 parameterized elements: 728 ms
9 elements/ms
Last received via parameterized elements: 6899
Last received via IN list: 6999
Elapsed time for 7000 IN list elements: 141 ms
49 elements/ms
Elapsed time for 7000 parameterized elements: 733 ms
9 elements/ms
Last received via parameterized elements: 6999
Last received via IN list: 7099
Elapsed time for 7100 IN list elements: 108 ms
65 elements/ms
Elapsed time for 7100 parameterized elements: 752 ms
9 elements/ms
Last received via parameterized elements: 7099
Last received via IN list: 7199
Elapsed time for 7200 IN list elements: 104 ms
69 elements/ms
Elapsed time for 7200 parameterized elements: 769 ms
9 elements/ms
Last received via parameterized elements: 7199
Last received via IN list: 7299
Elapsed time for 7300 IN list elements: 142 ms
51 elements/ms
Elapsed time for 7300 parameterized elements: 778 ms
9 elements/ms
Last received via parameterized elements: 7299
Last received via IN list: 7399
Elapsed time for 7400 IN list elements: 127 ms
58 elements/ms
Elapsed time for 7400 parameterized elements: 796 ms
9 elements/ms
Last received via parameterized elements: 7399
Last received via IN list: 7499
Elapsed time for 7500 IN list elements: 110 ms
68 elements/ms
Elapsed time for 7500 parameterized elements: 796 ms
9 elements/ms
Last received via parameterized elements: 7499
Last received via IN list: 7599
Elapsed time for 7600 IN list elements: 144 ms
52 elements/ms
Elapsed time for 7600 parameterized elements: 994 ms
7 elements/ms
Last received via parameterized elements: 7599
Last received via IN list: 7699
Elapsed time for 7700 IN list elements: 122 ms
63 elements/ms
Elapsed time for 7700 parameterized elements: 819 ms
9 elements/ms
Last received via parameterized elements: 7699
Last received via IN list: 7799
Elapsed time for 7800 IN list elements: 114 ms
68 elements/ms
Elapsed time for 7800 parameterized elements: 893 ms
8 elements/ms
Last received via parameterized elements: 7799
Last received via IN list: 7899
Elapsed time for 7900 IN list elements: 152 ms
51 elements/ms
Elapsed time for 7900 parameterized elements: 841 ms
9 elements/ms
Last received via parameterized elements: 7899
Last received via IN list: 7999
Elapsed time for 8000 IN list elements: 119 ms
67 elements/ms
Elapsed time for 8000 parameterized elements: 853 ms
9 elements/ms
Last received via parameterized elements: 7999
Last received via IN list: 8099
Elapsed time for 8100 IN list elements: 121 ms
66 elements/ms
Elapsed time for 8100 parameterized elements: 858 ms
9 elements/ms
Last received via parameterized elements: 8099
Last received via IN list: 8199
Elapsed time for 8200 IN list elements: 157 ms
52 elements/ms
Elapsed time for 8200 parameterized elements: 871 ms
9 elements/ms
Last received via parameterized elements: 8199
Last received via IN list: 8299
Elapsed time for 8300 IN list elements: 131 ms
63 elements/ms
Elapsed time for 8300 parameterized elements: 879 ms
9 elements/ms
Last received via parameterized elements: 8299
Last received via IN list: 8399
Elapsed time for 8400 IN list elements: 124 ms
67 elements/ms
Elapsed time for 8400 parameterized elements: 890 ms
9 elements/ms
Last received via parameterized elements: 8399
Last received via IN list: 8499
Elapsed time for 8500 IN list elements: 163 ms
52 elements/ms
Elapsed time for 8500 parameterized elements: 904 ms
9 elements/ms
Last received via parameterized elements: 8499
Last received via IN list: 8599
Elapsed time for 8600 IN list elements: 131 ms
65 elements/ms
Elapsed time for 8600 parameterized elements: 907 ms
9 elements/ms
Last received via parameterized elements: 8599
Last received via IN list: 8699
Elapsed time for 8700 IN list elements: 127 ms
68 elements/ms
Elapsed time for 8700 parameterized elements: 947 ms
9 elements/ms
Last received via parameterized elements: 8699
Last received via IN list: 8799
Elapsed time for 8800 IN list elements: 166 ms
53 elements/ms
Elapsed time for 8800 parameterized elements: 933 ms
9 elements/ms
Last received via parameterized elements: 8799
Last received via IN list: 8899
Elapsed time for 8900 IN list elements: 133 ms
66 elements/ms
Elapsed time for 8900 parameterized elements: 947 ms
9 elements/ms
Last received via parameterized elements: 8899
Last received via IN list: 8999
Elapsed time for 9000 IN list elements: 142 ms
63 elements/ms
Elapsed time for 9000 parameterized elements: 951 ms
9 elements/ms
Last received via parameterized elements: 8999
Last received via IN list: 9099
Elapsed time for 9100 IN list elements: 173 ms
52 elements/ms
Elapsed time for 9100 parameterized elements: 961 ms
9 elements/ms
Last received via parameterized elements: 9099
Last received via IN list: 9199
Elapsed time for 9200 IN list elements: 138 ms
66 elements/ms
Elapsed time for 9200 parameterized elements: 979 ms
9 elements/ms
Last received via parameterized elements: 9199
Last received via IN list: 9299
Elapsed time for 9300 IN list elements: 135 ms
68 elements/ms
Elapsed time for 9300 parameterized elements: 985 ms
9 elements/ms
Last received via parameterized elements: 9299
Last received via IN list: 9399
Elapsed time for 9400 IN list elements: 175 ms
53 elements/ms
Elapsed time for 9400 parameterized elements: 1000 ms
9 elements/ms
Last received via parameterized elements: 9399
Last received via IN list: 9499
Elapsed time for 9500 IN list elements: 136 ms
69 elements/ms
Elapsed time for 9500 parameterized elements: 1010 ms
9 elements/ms
Last received via parameterized elements: 9499
Last received via IN list: 9599
Elapsed time for 9600 IN list elements: 144 ms
66 elements/ms
Elapsed time for 9600 parameterized elements: 1017 ms
9 elements/ms
Last received via parameterized elements: 9599
Last received via IN list: 9699
Elapsed time for 9700 IN list elements: 186 ms
52 elements/ms
Elapsed time for 9700 parameterized elements: 1021 ms
9 elements/ms
Last received via parameterized elements: 9699
Last received via IN list: 9799
Elapsed time for 9800 IN list elements: 142 ms
69 elements/ms
Elapsed time for 9800 parameterized elements: 1048 ms
9 elements/ms
Last received via parameterized elements: 9799
Last received via IN list: 9899
Elapsed time for 9900 IN list elements: 145 ms
68 elements/ms
Elapsed time for 9900 parameterized elements: 1054 ms
9 elements/ms
Last received via parameterized elements: 9899
Local-to-local on AMD 1600Mhz, PGSQL7.3.2, RH9.0
Table update time: 169xxx ms
*Without* VACUUMDB, IN lists suck beyond measure:
For just 10 elements in the IN list:
Elapsed time for 10 IN list elements: 2638 ms
0 elements/ms
Elapsed time for 10 parameterized elements: 9 ms
1 elements/ms
*With* VACUUMDB: IN lists recover a bit:
Elapsed time for 10 IN list elements: 10 ms
1 elements/ms
Elapsed time for 10 parameterized elements: 24 ms
0 elements/ms
[...]
Elapsed time for 99 IN list elements: 16 ms
6 elements/ms
Elapsed time for 99 parameterized elements: 36 ms
2 elements/ms
Last received via parameterized elements: 98
Testing for some larger numbers of elements, however, reveals another story.
Elapsed time for 1000 IN list elements: 676 ms
1 elements/ms
Elapsed time for 1000 parameterized elements: 351 ms
2 elements/ms
Elapsed time for 2000 IN list elements: 3197 ms
0 elements/ms
Elapsed time for 2000 parameterized elements: 713 ms
2 elements/ms
Elapsed time for 3000 IN list elements: 7266 ms
0 elements/ms
Elapsed time for 3000 parameterized elements: 1080 ms
2 elements/ms
Elapsed time for 4000 IN list elements: 13341 ms
0 elements/ms
Elapsed time for 4000 parameterized elements: 1449 ms
2 elements/ms
Based on the 'by-the-1000's approach, IN lists are on a seriously bad curve,
while parameterized statements are linear.
The cutoff on my AMD 1600 Mhz machine is at about 700 elements:
Elapsed time for 600 IN list elements: 157 ms
3 elements/ms
Elapsed time for 600 parameterized elements: 246 ms
2 elements/ms
Elapsed time for 700 IN list elements: 269 ms
2 elements/ms
Elapsed time for 700 parameterized elements: 263 ms
2 elements/ms
Local(Intel PIII-M 1Ghz)-to-REMOTE(AMD1600Mhz), PostgreSQL 7.3.2
100 -> 1000 by 100's.
IN List Query string length is 637
Last received via IN list: 99
Elapsed time for 100 IN list elements: 22 ms
4 elements/ms
Elapsed time for 100 parameterized elements: 117 ms
0 elements/ms
Last received via parameterized elements: 99
IN List Query string length is 1237
Last received via IN list: 199
Elapsed time for 200 IN list elements: 39 ms
5 elements/ms
Elapsed time for 200 parameterized elements: 169 ms
1 elements/ms
Last received via parameterized elements: 199
IN List Query string length is 1837
Last received via IN list: 299
Elapsed time for 300 IN list elements: 81 ms
3 elements/ms
Elapsed time for 300 parameterized elements: 136 ms
2 elements/ms
Last received via parameterized elements: 299
IN List Query string length is 2437
Last received via IN list: 399
Elapsed time for 400 IN list elements: 108 ms
3 elements/ms
Elapsed time for 400 parameterized elements: 157 ms
2 elements/ms
Last received via parameterized elements: 399
IN List Query string length is 3037
Last received via IN list: 499
Elapsed time for 500 IN list elements: 99 ms
5 elements/ms
Elapsed time for 500 parameterized elements: 232 ms
2 elements/ms
Last received via parameterized elements: 499
IN List Query string length is 3637
Last received via IN list: 599
Elapsed time for 600 IN list elements: 150 ms
4 elements/ms
Elapsed time for 600 parameterized elements: 182 ms
3 elements/ms
Last received via parameterized elements: 599
IN List Query string length is 4237
Last received via IN list: 699
Elapsed time for 700 IN list elements: 256 ms
2 elements/ms
Elapsed time for 700 parameterized elements: 219 ms
3 elements/ms
Last received via parameterized elements: 699
IN List Query string length is 4837
Last received via IN list: 799
Elapsed time for 800 IN list elements: 370 ms
2 elements/ms
Elapsed time for 800 parameterized elements: 245 ms
3 elements/ms
Last received via parameterized elements: 799
IN List Query string length is 5437
Last received via IN list: 899
Elapsed time for 900 IN list elements: 511 ms
1 elements/ms
Elapsed time for 900 parameterized elements: 265 ms
3 elements/ms
Last received via parameterized elements: 899
Curiously, the difference between local->local and local->remote here is
small. The breakeven point was about 650 elements, versus slightly closer
to 700 in local->local.
Local(Intel PIII-M 1Ghz)-to-REMOTE(AMD1600Mhz), PostgreSQL 7.3.2
1000 -> 10000 by 1000's.
IN List Query string length is 6037
Last received via IN list: 999
Elapsed time for 1000 IN list elements: 772 ms
1 elements/ms
Elapsed time for 1000 parameterized elements: 495 ms
2 elements/ms
Last received via parameterized elements: 999
IN List Query string length is 12037
Last received via IN list: 1999
Elapsed time for 2000 IN list elements: 3055 ms
0 elements/ms
Elapsed time for 2000 parameterized elements: 681 ms
2 elements/ms
Last received via parameterized elements: 1999
IN List Query string length is 18037
Last received via IN list: 2999
Elapsed time for 3000 IN list elements: 7205 ms
0 elements/ms
Elapsed time for 3000 parameterized elements: 888 ms
3 elements/ms
Last received via parameterized elements: 2999
IN List Query string length is 24037
Last received via IN list: 3999
Elapsed time for 4000 IN list elements: 12912 ms
0 elements/ms
Elapsed time for 4000 parameterized elements: 1162 ms
3 elements/ms
Last received via parameterized elements: 3999
IN List Query string length is 30037
Last received via IN list: 4999
Elapsed time for 5000 IN list elements: 21844 ms
0 elements/ms
Elapsed time for 5000 parameterized elements: 1461 ms
3 elements/ms
Last received via parameterized elements: 4999
IN List Query string length is 36037
Last received via IN list: 5999
Elapsed time for 6000 IN list elements: 32595 ms
0 elements/ms
Elapsed time for 6000 parameterized elements: 1780 ms
3 elements/ms
Last received via parameterized elements: 5999
IN List Query string length is 42037
Last received via IN list: 6999
Elapsed time for 7000 IN list elements: 46566 ms
0 elements/ms
Elapsed time for 7000 parameterized elements: 2027 ms
3 elements/ms
Last received via parameterized elements: 6999
IN List Query string length is 48037
java.sql.SQLException: ERROR: out of free buffers: time to abort!
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:126)
at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:451)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:281)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:144)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:132)
at INList.main(INList.java:775)
*/
class INList
{
public static void main(String[] args) {
try {
final int NROWS = 1000000 ; // one million
final int IDSTART = 10000 ; // first ID value
// ---------------- MySql driver -----------------
// -classpath .:/usr/java/mysql-connector-java-2.0.14/mysql-connector-java-2.0.14-bin.jar
// RH9: -classpath .:/home/dave/mysql-connector-java-3.0.8-stable/mysql-connector-java-3.0.8-stable-bin.jar
//Class.forName("com.mysql.jdbc.Driver") ;
//Connection conn = DriverManager.getConnection("jdbc:mysql:///test", "dave", "") ;
//String innodb = " TYPE=INNODB" ;
// --------------- postgres 7.3.2 distribution JDBC2 driver linked as /usr/java/postgresql-jdbc.jar
// -classpath .:/usr/java/postgresql-jdbc.jar
Class.forName("org.postgresql.Driver") ;
Connection conn = DriverManager.getConnection("jdbc:postgresql:test", "dave", "") ;
String innodb = "" ;
conn.setAutoCommit(false) ;
long oldmillis, newmillis, elapsed ;
Statement statement = conn.createStatement() ;
PreparedStatement pstatement ;
ResultSet rs ;
int lastReceived ;
if (false) {
statement.executeUpdate("CREATE TABLE MILLION (ID INTEGER PRIMARY KEY, VAL INTEGER)" + innodb) ;
conn.commit() ;
// Read that "all statements of a connection commit/rollback as a group, API certainly implies this
// conn.commit() ; pstatement should potentially fail because table isn't there without this!
// Create the rows
pstatement = conn.prepareStatement("INSERT INTO MILLION (ID, VAL) VALUES(?, ?)") ;
oldmillis = System.currentTimeMillis() ;
for (int i = 0 ; i < NROWS ; i++) {
pstatement.setInt(1, IDSTART+i) ;
pstatement.setInt(2, i) ;
pstatement.executeUpdate() ;
}
conn.commit() ;
newmillis = System.currentTimeMillis() ;
pstatement.close() ;
System.out.println("Table update time: " + (newmillis-oldmillis) + " ms") ;
rs = statement.executeQuery("SELECT COUNT(*) FROM MILLION") ;
rs.first() ;
if (rs.getInt(1) != NROWS)
System.out.println("**** Expected " + (NROWS) + " rows, got " + rs.getInt(1)) ;
conn.commit() ;
// Determine largest IN LIST
StringBuffer sb = new StringBuffer(8000*1024) ;
boolean successful = false ;
int tryAmount = NROWS ;
while (!successful) {
sb.setLength(0) ;
sb.append("SELECT MAX(VAL) FROM MILLION WHERE ID IN (") ;
for (int i = 0 ; i < tryAmount ; i++) {
sb.append(i+IDSTART) ;
if (i < tryAmount-1)
sb.append(',') ;
}
sb.append(')') ;
try {
rs = statement.executeQuery(sb.toString()) ;
successful = true ;
}
catch (SQLException e) {
System.out.println("IN list failed for " + tryAmount + " entries, buffer size was " +
sb.length()) ;
tryAmount -= 10000 ;
if (tryAmount <= 0)
break ; // be done with the while loop
}
} // while (!successful)
conn.commit() ; // more for the next txn than this one, eliminate txn caching
System.out.println("Largest IN list: " + tryAmount + " elements.") ;
} // if (false)
else {
for (int tryAmount = 100 ; tryAmount < 1000 ; tryAmount+= 100) {
StringBuffer sb = new StringBuffer(tryAmount*20) ;
sb.append("SELECT VAL FROM MILLION WHERE ID IN (") ;
for (int i = 0 ; i < tryAmount ; i++) {
sb.append(i+IDSTART) ;
if (i+1 < tryAmount)
sb.append(',') ;
}
sb.append(')') ;
// Try query with largest inlist, getting resultset with all values
String query = sb.toString() ;
System.out.println("IN List Query string length is " + query.length()) ;
//query = query.replaceFirst("MAX(VAL)", "VAL") ;
oldmillis = System.currentTimeMillis() ;
rs = statement.executeQuery(query) ;
lastReceived = -1 ;
while (rs.next())
lastReceived = rs.getInt(1) ;
System.out.println("Last received via IN list: " + lastReceived) ;
conn.commit() ;
newmillis = System.currentTimeMillis() ;
elapsed = newmillis - oldmillis ;
System.out.println("Elapsed time for " + tryAmount + " IN list elements: " + elapsed + " ms") ;
System.out.println((tryAmount/elapsed) + " elements/ms") ;
// Try the query with prepared statements
lastReceived = -1 ;
pstatement = conn.prepareStatement("SELECT VAL FROM MILLION WHERE ID = ?") ;
oldmillis = System.currentTimeMillis() ;
for (int i = 0 ; i < tryAmount ; i++) {
pstatement.setInt(1, i+IDSTART) ;
rs = pstatement.executeQuery() ;
if (!rs.first())
System.out.println("Pstatement query failed") ;
else
lastReceived = rs.getInt(1) ;
}
conn.commit() ;
newmillis = System.currentTimeMillis() ;
pstatement.close() ;
elapsed = newmillis - oldmillis ;
System.out.println("Elapsed time for " + tryAmount + " parameterized elements: " + elapsed + " ms") ;
System.out.println((tryAmount/elapsed) + " elements/ms") ;
System.out.println("Last received via parameterized elements: " + lastReceived) ;
} // for (tryAmount ...)
} // else (if (false))
// Done, delete our table
// statement.executeUpdate("DROP TABLE MILLION") ;
conn.commit() ;
}
catch (Exception e) {
e.printStackTrace() ;
}
} // main()
} // class INList