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 | 15625555 | 8545118 | 2183569 | 2719372 | 1302071 | 539800 | 207782 | 89851 | 31230 | 6695 | 67 |
|---|
| Avg. Per Day | 11165.512 | 6106.063 | 1560.307 | 1943.175 | 930.417 | 385.723 | 148.474 | 64.205 | 22.316 | 4.784 | 0.048 |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 |
| 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 | 136.2 | 7.4 | 111.4 | 394.5 | 367.4 | 333.6 | 264.7 | 255.8 | 286.3 | 426.0 | 525.9 |
| Avg. Seconds | 6.963 | 1.822 | 4.045 | 11.307 | 21.983 | 28.047 | 28.176 | 29.517 | 34.576 | 44.188 | 50.572 |
|---|
| Avg. Words Per Sec | 25662.6 | 98060.7 | 44173.5 | 15803.5 | 8128.4 | 6371.1 | 6341.9 | 6053.8 | 5168.0 | 4043.9 | 3533.4 |
|---|
Len
2 | Searches | 59833 | 59833 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 42.755 | 42.755 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2015-01-25 | 2015-01-25 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.2 | 27.2 |
| Avg. Seconds | 0.231 | 0.231 |
|---|
| Avg. Words Per Sec | 773550.1 | 773550.1 |
|---|
Len
3 | Searches | 492020 | 323763 | 168257 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 351.581 | 231.350 | 120.231 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2015-01-25 | 2015-01-25 | 2015-01-25 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 164.6 | 25.1 | 432.8 |
| Avg. Seconds | 0.601 | 0.395 | 0.998 |
|---|
| Avg. Words Per Sec | 297283.3 | 452694.5 | 179022.8 |
|---|
Len
4 | Searches | 1087169 | 780329 | 253447 | 53393 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 776.856 | 557.598 | 181.105 | 38.153 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 143.8 | 17.5 | 279.0 | 1349.0 |
| Avg. Seconds | 1.184 | 0.713 | 1.872 | 4.786 |
|---|
| Avg. Words Per Sec | 150982.8 | 250500.2 | 95445.9 | 37334.3 |
|---|
Len
5 | Searches | 1916096 | 1219795 | 329713 | 366387 | 201 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1369.180 | 871.626 | 235.602 | 261.808 | 0.144 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-21 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 204.0 | 10.7 | 147.5 | 898.1 | 405.0 |
| Avg. Seconds | 2.813 | 1.225 | 3.148 | 7.792 | 12.149 |
|---|
| Avg. Words Per Sec | 63529.7 | 145882.7 | 56760.7 | 22932.6 | 14708.0 |
|---|
Len
6 | Searches | 2298798 | 1402078 | 307474 | 574486 | 14760 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1642.646 | 1001.879 | 219.711 | 410.509 | 10.547 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 170.8 | 7.3 | 90.4 | 582.1 | 1373.2 | |
| Avg. Seconds | 4.673 | 1.889 | 4.625 | 11.110 | 19.625 | |
|---|
| Avg. Words Per Sec | 38239.4 | 94617.0 | 38637.8 | 16083.6 | 9105.1 | |
|---|
Len
7 | Searches | 2426731 | 1387834 | 284990 | 615922 | 137560 | 425 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1734.063 | 991.701 | 203.645 | 440.118 | 98.296 | 0.304 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 151.4 | 5.5 | 52.3 | 367.9 | 855.9 | 1041.5 | |
| Avg. Seconds | 6.926 | 2.518 | 5.853 | 13.485 | 24.171 | 30.579 | |
|---|
| Avg. Words Per Sec | 25801.7 | 70958.1 | 30530.4 | 13251.3 | 7392.7 | 5843.7 | |
|---|
Len
8 | Searches | 2076171 | 1094053 | 226324 | 431727 | 316281 | 7732 | 54 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1483.564 | 781.775 | 161.724 | 308.498 | 226.004 | 5.525 | 0.039 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 130.1 | 3.6 | 20.6 | 191.1 | 538.5 | 1114.4 | 1550.0 | |
| Avg. Seconds | 9.329 | 2.835 | 6.373 | 14.379 | 26.339 | 36.853 | 43.316 | |
|---|
| Avg. Words Per Sec | 19153.4 | 63029.0 | 28037.9 | 12427.3 | 6784.4 | 4848.7 | 4125.2 | |
|---|
Len
9 | Searches | 1635200 | 770800 | 188004 | 263150 | 340321 | 72897 | 28 | | | N/A | N/A |
|---|
| Avg. Per Day | 1168.461 | 550.789 | 134.342 | 188.038 | 243.182 | 52.090 | 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-24 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 117.8 | 2.7 | 9.7 | 74.4 | 345.0 | 710.0 | 508.5 | | |
| Avg. Seconds | 11.196 | 2.638 | 6.171 | 13.378 | 25.642 | 39.301 | 55.506 | | |
|---|
| Avg. Words Per Sec | 15960.8 | 67732.2 | 28956.0 | 13356.7 | 6968.7 | 4546.8 | 3219.3 | | |
|---|
Len
10 | Searches | 1100859 | 494334 | 130830 | 143441 | 197746 | 130654 | 3785 | 56 | 13 | | N/A |
|---|
| Avg. Per Day | 786.638 | 353.235 | 93.487 | 102.498 | 141.303 | 93.361 | 2.705 | 0.040 | 0.009 | |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2014-12-17 | 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 | 98.2 | 2.3 | 6.3 | 35.0 | 180.4 | 474.3 | 899.5 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.482 | 2.142 | 5.217 | 11.242 | 21.899 | 36.564 | 46.385 | 55.862 | 41.984 | |
|---|
| Avg. Words Per Sec | 15562.1 | 83441.7 | 34253.6 | 15895.6 | 8159.7 | 4887.1 | 3852.3 | 3198.8 | 4256.2 | |
|---|
Len
11 | Searches | 783810 | 331708 | 95600 | 92760 | 111427 | 124066 | 27795 | 454 | | | |
|---|
| Avg. Per Day | 560.085 | 237.028 | 68.313 | 66.283 | 79.622 | 88.654 | 19.861 | 0.324 | | | |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 83.9 | 2.1 | 4.9 | 22.9 | 93.6 | 289.4 | 564.1 | 1121.7 | | | |
| Avg. Seconds | 11.198 | 1.579 | 4.100 | 8.971 | 17.615 | 30.694 | 44.361 | 55.836 | | | |
|---|
| Avg. Words Per Sec | 15957.6 | 113188.5 | 43582.6 | 19919.7 | 10144.0 | 5821.7 | 4028.1 | 3200.3 | | | |
|---|
Len
12 | Searches | 571726 | 234863 | 69076 | 63253 | 70133 | 78017 | 50983 | 5393 | 8 | | |
|---|
| Avg. Per Day | 408.537 | 167.825 | 49.359 | 45.199 | 50.115 | 55.748 | 36.431 | 3.854 | 0.006 | | |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 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 | 67.7 | 1.9 | 3.9 | 12.5 | 56.3 | 150.6 | 351.5 | 661.7 | 946.0 | | |
| Avg. Seconds | 10.161 | 1.144 | 2.987 | 6.673 | 13.157 | 23.513 | 36.886 | 50.749 | 132.110 | | |
|---|
| Avg. Words Per Sec | 17585.5 | 156225.1 | 59831.2 | 26779.6 | 13581.5 | 7599.6 | 4844.5 | 3521.0 | 1352.6 | | |
|---|
Len
13 | Searches | 428154 | 171283 | 49977 | 45015 | 46977 | 53056 | 45228 | 16068 | 550 | | |
|---|
| Avg. Per Day | 305.945 | 122.393 | 35.712 | 32.166 | 33.568 | 37.912 | 32.318 | 11.482 | 0.393 | | |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-24 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 56.7 | 1.8 | 3.3 | 9.1 | 33.5 | 110.8 | 202.9 | 396.2 | 703.9 | | |
| Avg. Seconds | 8.861 | 0.811 | 2.099 | 4.604 | 9.317 | 17.151 | 28.786 | 41.385 | 51.298 | | |
|---|
| Avg. Words Per Sec | 20166.5 | 220347.8 | 85131.3 | 38814.4 | 19178.5 | 10418.9 | 6207.7 | 4317.8 | 3483.4 | | |
|---|
Len
14 | Searches | 318235 | 124172 | 35180 | 30855 | 29714 | 34424 | 35221 | 23871 | 4755 | 43 | |
|---|
| Avg. Per Day | 227.400 | 88.729 | 25.138 | 22.048 | 21.233 | 24.598 | 25.168 | 17.057 | 3.398 | 0.031 | |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 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 | 53.7 | 1.7 | 2.9 | 6.6 | 20.5 | 74.5 | 145.9 | 250.2 | 476.2 | 568.2 | |
| Avg. Seconds | 8.051 | 0.567 | 1.342 | 2.985 | 6.129 | 11.888 | 20.977 | 33.192 | 47.891 | 48.488 | |
|---|
| Avg. Words Per Sec | 22195.7 | 315282.3 | 133152.4 | 59858.5 | 29155.6 | 15030.8 | 8518.5 | 5383.6 | 3731.2 | 3685.3 | |
|---|
Len
15 | Searches | 241446 | 90483 | 24655 | 22065 | 21401 | 22420 | 25134 | 22357 | 11200 | 1731 | |
|---|
| Avg. Per Day | 172.529 | 64.656 | 17.618 | 15.767 | 15.292 | 16.021 | 17.960 | 15.976 | 8.003 | 1.237 | |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | |
| 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 | 49.9 | 1.5 | 2.3 | 4.5 | 12.6 | 43.2 | 100.4 | 167.8 | 287.8 | 596.4 | |
| Avg. Seconds | 7.184 | 0.339 | 0.784 | 1.790 | 3.786 | 7.503 | 13.945 | 24.228 | 37.084 | 51.087 | |
|---|
| Avg. Words Per Sec | 24872.0 | 526872.6 | 227981.2 | 99851.9 | 47202.1 | 23817.3 | 12813.9 | 7375.5 | 4818.5 | 3497.8 | |
|---|
Len
16 | Searches | 189307 | 59790 | 20042 | 16918 | 15550 | 16109 | 19554 | 21652 | 14704 | 4921 | 67 |
|---|
| Avg. Per Day | 135.273 | 42.724 | 14.321 | 12.089 | 11.112 | 11.511 | 13.973 | 15.472 | 10.507 | 3.516 | 0.048 |
| 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 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 | 2015-01-25 |
| 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 | 48.5 | 1.0 | 1.5 | 2.4 | 4.9 | 14.9 | 54.2 | 129.9 | 205.6 | 364.9 | 525.9 |
| Avg. Seconds | 6.728 | 0.167 | 0.386 | 0.933 | 2.136 | 4.340 | 8.708 | 16.212 | 27.675 | 41.724 | 50.572 |
|---|
| Avg. Words Per Sec | 26560.8 | 1071305.4 | 462641.3 | 191468.0 | 83669.8 | 41169.6 | 20520.0 | 11021.8 | 6456.8 | 4282.7 | 3533.4 |
|---|