Search performance using Damerau-Levenshtein distance in T-SQL
This table shows the speed of fuzzy-string dictionary searches using the DLD algorithm
(by Change Limit allowed, and length of word searched for).
- Change Limit is used to stop checking a word when too many Changes are encountered.
- Change Limit is used to avoid checking parts of words that are too far apart.
- Change Limit is used to completely avoid checking words with lengths that are too different.
Phase:
Version:
| Change Limit→ | Any Limit | Limit 1 | Limit 2 | Limit 3 | Limit 4 | Limit 5 | Limit 6 | Limit 7 | Limit 8 | Limit 9 | Limit 10 |
|---|
| ↓Length of Search Word |
|---|
Any
Len | Searches | 8903809 | 5559206 | 1126120 | 1190085 | 597375 | 264462 | 99515 | 45993 | 17036 | 3984 | 33 |
|---|
| Avg. Per Day | 7965.741 | 4973.511 | 1007.477 | 1064.703 | 534.438 | 236.599 | 89.031 | 41.147 | 15.241 | 3.564 | 0.030 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-06 | 2011-04-20 | 2011-04-20 | 2011-04-20 | 2012-08-10 | 2012-08-10 | 2012-08-18 |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2013-09-14 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 111.9 | 7.6 | 100.9 | 397.9 | 379.8 | 355.8 | 270.8 | 260.1 | 290.5 | 444.5 | 527.2 |
| Avg. Seconds | 6.709 | 1.928 | 4.186 | 12.451 | 25.638 | 32.857 | 31.895 | 32.201 | 37.243 | 46.821 | 51.313 |
|---|
| Avg. Words Per Sec | 26635.0 | 92662.3 | 42689.2 | 14351.6 | 6969.7 | 5438.5 | 5602.5 | 5549.3 | 4798.0 | 3816.5 | 3482.4 |
|---|
Len
2 | Searches | 37453 | 37453 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 33.507 | 33.507 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2014-04-18 | 2014-04-18 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 26.4 | 26.4 |
| Avg. Seconds | 0.282 | 0.282 |
|---|
| Avg. Words Per Sec | 633197.7 | 633197.7 |
|---|
Len
3 | Searches | 286376 | 208200 | 78176 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 256.205 | 186.265 | 69.940 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 132.6 | 24.3 | 421.0 |
| Avg. Seconds | 0.640 | 0.467 | 1.102 |
|---|
| Avg. Words Per Sec | 279023.5 | 382582.1 | 162139.1 |
|---|
Len
4 | Searches | 704187 | 556808 | 120750 | 26629 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 629.997 | 498.145 | 108.028 | 23.823 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 111.5 | 16.8 | 275.6 | 1348.8 |
| Avg. Seconds | 1.171 | 0.787 | 2.070 | 5.121 |
|---|
| Avg. Words Per Sec | 152605.5 | 227055.8 | 86310.9 | 34894.9 |
|---|
Len
5 | Searches | 1223977 | 889868 | 158627 | 175381 | 101 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1095.024 | 796.115 | 141.915 | 156.904 | 0.090 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-14 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 157.3 | 10.3 | 144.3 | 914.7 | 405.0 |
| Avg. Seconds | 2.631 | 1.323 | 3.431 | 8.540 | 13.436 |
|---|
| Avg. Words Per Sec | 67917.2 | 135103.6 | 52077.3 | 20924.8 | 13298.9 |
|---|
Len
6 | Searches | 1408944 | 1005723 | 152247 | 241862 | 9112 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1260.504 | 899.764 | 136.207 | 216.380 | 8.152 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 126.1 | 7.1 | 86.0 | 598.1 | 1389.7 | |
| Avg. Seconds | 4.295 | 2.025 | 4.848 | 12.754 | 21.131 | |
|---|
| Avg. Words Per Sec | 41603.3 | 88255.4 | 36858.4 | 14010.9 | 8456.3 | |
|---|
Len
7 | Searches | 1396917 | 933915 | 145450 | 244601 | 72675 | 276 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1249.744 | 835.522 | 130.126 | 218.831 | 65.018 | 0.247 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 117.6 | 5.6 | 48.5 | 361.2 | 872.1 | 1067.8 | |
| Avg. Seconds | 6.635 | 2.700 | 5.943 | 15.816 | 27.594 | 32.284 | |
|---|
| Avg. Words Per Sec | 26932.0 | 66193.5 | 30069.9 | 11298.0 | 6475.8 | 5535.0 | |
|---|
Len
8 | Searches | 1116236 | 669132 | 118788 | 176355 | 146966 | 4948 | 47 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 998.634 | 598.635 | 106.273 | 157.775 | 131.482 | 4.427 | 0.042 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2013-12-21 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 109.7 | 3.8 | 20.2 | 183.8 | 540.5 | 1137.6 | 1533.0 | |
| Avg. Seconds | 9.429 | 3.039 | 6.314 | 16.523 | 31.485 | 40.002 | 43.521 | |
|---|
| Avg. Words Per Sec | 18951.4 | 58790.9 | 28301.3 | 10815.0 | 5675.3 | 4467.0 | 4105.9 | |
|---|
Len
9 | Searches | 837898 | 429410 | 102659 | 114067 | 148914 | 42826 | 22 | | | N/A | N/A |
|---|
| Avg. Per Day | 749.621 | 384.169 | 91.843 | 102.049 | 133.225 | 38.314 | 0.020 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2013-10-05 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 108.1 | 2.7 | 9.4 | 68.3 | 337.4 | 710.6 | 298.5 | | |
| Avg. Seconds | 11.940 | 2.806 | 6.115 | 14.605 | 30.974 | 44.175 | 60.369 | | |
|---|
| Avg. Words Per Sec | 14965.9 | 63677.9 | 29220.2 | 12234.9 | 5769.0 | 4045.1 | 2960.0 | | |
|---|
Len
10 | Searches | 563128 | 265385 | 74831 | 67877 | 83467 | 68986 | 2520 | 49 | 13 | | N/A |
|---|
| Avg. Per Day | 503.799 | 237.425 | 66.947 | 60.726 | 74.673 | 61.718 | 2.255 | 0.044 | 0.012 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-04-05 | 2011-04-06 | 2011-04-06 | 2012-03-23 | 2012-08-15 | 2012-09-20 | 2012-09-18 | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-03-16 | 2013-03-15 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 93.7 | 2.3 | 6.2 | 31.4 | 171.0 | 477.2 | 923.3 | 21.0 | 2710.5 | |
| Avg. Seconds | 12.327 | 2.270 | 5.216 | 11.692 | 25.372 | 42.190 | 48.946 | 58.011 | 41.984 | |
|---|
| Avg. Words Per Sec | 14496.4 | 78710.3 | 34256.2 | 15283.0 | 7042.7 | 4235.4 | 3650.8 | 3080.3 | 4256.2 | |
|---|
Len
11 | Searches | 402948 | 178474 | 55529 | 46826 | 48428 | 57433 | 15898 | 360 | | | |
|---|
| Avg. Per Day | 360.495 | 159.671 | 49.679 | 41.893 | 43.326 | 51.382 | 14.223 | 0.322 | | | |
| Earliest | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2012-05-22 | 2012-08-14 | 2012-08-17 | | | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-03-26 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 78.1 | 2.1 | 4.9 | 21.2 | 86.1 | 284.1 | 562.4 | 1154.6 | | | |
| Avg. Seconds | 11.828 | 1.659 | 4.161 | 9.268 | 19.552 | 35.849 | 48.974 | 56.931 | | | |
|---|
| Avg. Words Per Sec | 15107.6 | 107707.4 | 42947.4 | 19279.7 | 9139.1 | 4984.6 | 3648.7 | 3138.7 | | | |
|---|
Len
12 | Searches | 294585 | 129078 | 40295 | 32899 | 31288 | 33381 | 24264 | 3372 | 8 | | |
|---|
| Avg. Per Day | 263.549 | 115.479 | 36.050 | 29.433 | 27.992 | 29.864 | 21.708 | 3.017 | 0.007 | | |
| Earliest | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-20 | 2012-08-06 | 2012-08-06 | 2013-10-03 | | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-01-29 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 60.0 | 1.9 | 3.9 | 12.1 | 50.3 | 144.5 | 338.5 | 670.6 | 946.0 | | |
| Avg. Seconds | 10.274 | 1.196 | 3.044 | 6.871 | 14.112 | 26.651 | 41.633 | 53.677 | 132.110 | | |
|---|
| Avg. Words Per Sec | 17393.0 | 149414.3 | 58701.3 | 26007.1 | 12662.0 | 6704.8 | 4292.1 | 3329.0 | 1352.6 | | |
|---|
Len
13 | Searches | 222491 | 96323 | 29396 | 23935 | 22009 | 22183 | 19749 | 8584 | 312 | | |
|---|
| Avg. Per Day | 199.050 | 86.175 | 26.299 | 21.413 | 19.690 | 19.846 | 17.668 | 7.680 | 0.279 | | |
| Earliest | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-20 | 2011-04-20 | 2012-08-14 | 2012-08-15 | | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-06 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 47.5 | 1.8 | 3.3 | 8.8 | 29.5 | 97.0 | 188.8 | 389.4 | 689.6 | | |
| Avg. Seconds | 8.648 | 0.846 | 2.144 | 4.723 | 9.809 | 18.868 | 32.352 | 43.971 | 50.738 | | |
|---|
| Avg. Words Per Sec | 20662.1 | 211303.3 | 83338.2 | 37835.9 | 18217.4 | 9470.6 | 5523.3 | 4063.8 | 3521.8 | | |
|---|
Len
14 | Searches | 170856 | 71903 | 21431 | 17123 | 14599 | 15175 | 15821 | 11755 | 3006 | 43 | |
|---|
| Avg. Per Day | 152.855 | 64.328 | 19.173 | 15.319 | 13.061 | 13.576 | 14.154 | 10.517 | 2.689 | 0.038 | |
| Earliest | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-20 | 2011-04-20 | 2012-07-26 | 2012-08-14 | 2012-08-15 | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2013-09-11 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 46.7 | 1.8 | 2.9 | 6.4 | 19.0 | 66.5 | 135.3 | 238.6 | 476.6 | 568.2 | |
| Avg. Seconds | 7.904 | 0.588 | 1.358 | 3.026 | 6.335 | 12.794 | 23.041 | 36.012 | 50.115 | 48.488 | |
|---|
| Avg. Words Per Sec | 22607.9 | 303811.2 | 131608.8 | 59049.5 | 28208.3 | 13966.3 | 7755.3 | 4961.9 | 3565.7 | 3685.3 | |
|---|
Len
15 | Searches | 133339 | 53482 | 15210 | 12653 | 11337 | 10540 | 11313 | 11363 | 6228 | 1213 | |
|---|
| Avg. Per Day | 119.291 | 47.847 | 13.608 | 11.320 | 10.143 | 9.430 | 10.121 | 10.166 | 5.572 | 1.085 | |
| Earliest | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-06 | 2011-04-20 | 2011-04-20 | 2012-04-11 | 2012-08-14 | 2012-08-14 | |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 44.7 | 1.5 | 2.2 | 4.4 | 11.9 | 36.3 | 87.5 | 160.1 | 277.2 | 602.6 | |
| Avg. Seconds | 7.132 | 0.350 | 0.790 | 1.809 | 3.875 | 7.763 | 14.787 | 26.104 | 39.258 | 52.056 | |
|---|
| Avg. Words Per Sec | 25056.1 | 510338.1 | 226295.4 | 98784.1 | 46119.0 | 23017.5 | 12084.4 | 6845.3 | 4551.7 | 3432.7 | |
|---|
Len
16 | Searches | 104474 | 34052 | 12731 | 9877 | 8479 | 8714 | 9881 | 10510 | 7469 | 2728 | 33 |
|---|
| Avg. Per Day | 93.467 | 30.464 | 11.390 | 8.836 | 7.586 | 7.796 | 8.840 | 9.403 | 6.682 | 2.441 | 0.030 |
| Earliest | 2011-04-20 | 2011-04-20 | 2011-04-20 | 2011-04-20 | 2011-04-20 | 2011-04-20 | 2011-04-20 | 2011-04-20 | 2012-08-10 | 2012-08-10 | 2012-08-18 |
| Latest | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2014-04-18 | 2013-09-14 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 44.6 | 1.0 | 1.5 | 2.4 | 4.9 | 15.6 | 53.4 | 125.5 | 205.1 | 372.3 | 527.2 |
| Avg. Seconds | 6.576 | 0.172 | 0.386 | 0.924 | 2.132 | 4.260 | 8.885 | 17.058 | 29.708 | 44.467 | 51.313 |
|---|
| Avg. Words Per Sec | 27172.2 | 1041814.8 | 463227.7 | 193342.1 | 83796.7 | 41946.1 | 20111.0 | 10475.7 | 6014.8 | 4018.5 | 3482.4 |
|---|