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 | 46961034 | 26076627 | 4723685 | 8028787 | 4922379 | 2014628 | 766071 | 304421 | 101082 | 21901 | 1453 |
|---|
| Avg. Per Day | 16251.898 | 9024.390 | 1634.735 | 2778.538 | 1703.497 | 697.206 | 265.116 | 105.352 | 34.982 | 7.579 | 0.503 |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-02 |
| 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 | 171.5 | 7.1 | 116.8 | 467.5 | 481.4 | 405.9 | 320.8 | 296.6 | 316.1 | 419.5 | 597.9 |
| Avg. Seconds | 7.123 | 1.581 | 4.076 | 10.505 | 20.163 | 27.097 | 28.709 | 30.493 | 34.765 | 44.212 | 52.710 |
|---|
| Avg. Words Per Sec | 25087.3 | 113044.2 | 43842.1 | 17009.4 | 8862.4 | 6594.6 | 6224.1 | 5860.1 | 5140.0 | 4041.7 | 3390.1 |
|---|
Len
2 | Searches | 182203 | 182203 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 63.055 | 63.055 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2019-02-23 | 2019-02-23 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.5 | 27.5 |
| Avg. Seconds | 0.156 | 0.156 |
|---|
| Avg. Words Per Sec | 1145457.4 | 1145457.4 |
|---|
Len
3 | Searches | 1297313 | 901537 | 395776 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 448.964 | 311.997 | 136.967 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2019-02-23 | 2019-02-23 | 2019-02-23 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 147.5 | 25.7 | 424.9 |
| Avg. Seconds | 0.477 | 0.286 | 0.912 |
|---|
| Avg. Words Per Sec | 374546.9 | 624925.6 | 195826.4 |
|---|
Len
4 | Searches | 2962173 | 2177819 | 566254 | 218100 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1025.125 | 753.682 | 195.965 | 75.478 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 168.6 | 18.1 | 289.9 | 1356.0 |
| Avg. Seconds | 1.053 | 0.547 | 1.770 | 4.254 |
|---|
| Avg. Words Per Sec | 169621.7 | 326864.5 | 100974.0 | 42001.0 |
|---|
Len
5 | Searches | 5462241 | 3427152 | 712575 | 1319001 | 3513 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1890.329 | 1186.041 | 246.602 | 456.469 | 1.216 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 253.0 | 11.0 | 150.5 | 934.0 | 1459.6 |
| Avg. Seconds | 2.697 | 0.965 | 3.065 | 6.980 | 9.800 |
|---|
| Avg. Words Per Sec | 66252.3 | 185174.1 | 58293.3 | 25600.9 | 18233.8 |
|---|
Len
6 | Searches | 6710982 | 4099405 | 667099 | 1846014 | 98464 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2322.483 | 1418.689 | 230.864 | 638.854 | 34.076 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 206.3 | 7.1 | 92.9 | 627.6 | 1369.3 | |
| Avg. Seconds | 4.347 | 1.521 | 4.591 | 9.918 | 15.911 | |
|---|
| Avg. Words Per Sec | 41103.4 | 117449.6 | 38925.2 | 18016.6 | 11230.8 | |
|---|
Len
7 | Searches | 7566253 | 4312432 | 616356 | 1858896 | 777370 | 1199 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2618.468 | 1492.412 | 213.304 | 643.312 | 269.026 | 0.415 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-15 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 200.4 | 5.2 | 53.8 | 404.3 | 910.9 | 1033.5 | |
| Avg. Seconds | 6.739 | 2.093 | 5.966 | 12.359 | 19.661 | 27.096 | |
|---|
| Avg. Words Per Sec | 26514.2 | 85393.7 | 29951.7 | 14458.0 | 9088.6 | 6594.7 | |
|---|
Len
8 | Searches | 6744047 | 3581238 | 481365 | 1203773 | 1429854 | 47502 | 315 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2333.926 | 1239.366 | 166.587 | 416.592 | 494.832 | 16.439 | 0.109 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2018-08-16 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 174.6 | 3.4 | 19.9 | 201.8 | 598.9 | 1168.6 | 1664.4 | |
| Avg. Seconds | 9.121 | 2.443 | 6.625 | 13.678 | 22.110 | 31.221 | 41.385 | |
|---|
| Avg. Words Per Sec | 19590.6 | 73134.4 | 26972.8 | 13064.0 | 8082.0 | 5723.5 | 4317.8 | |
|---|
Len
9 | Searches | 5162913 | 2558855 | 392038 | 654663 | 1227917 | 329119 | 321 | | | N/A | N/A |
|---|
| Avg. Per Day | 1786.739 | 885.548 | 135.673 | 226.561 | 424.948 | 113.899 | 0.111 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2018-12-16 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 153.1 | 2.6 | 9.3 | 79.9 | 393.2 | 743.1 | 1315.2 | | |
| Avg. Seconds | 10.940 | 2.367 | 6.464 | 13.498 | 22.650 | 34.112 | 43.199 | | |
|---|
| Avg. Words Per Sec | 16334.1 | 75488.5 | 27643.8 | 13238.8 | 7889.1 | 5238.4 | 4136.4 | | |
|---|
Len
10 | Searches | 3460466 | 1648753 | 279077 | 333594 | 632661 | 545902 | 20407 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1197.570 | 570.587 | 96.581 | 115.448 | 218.946 | 188.921 | 7.062 | 0.020 | 0.004 | |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2016-01-30 | 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 | 129.0 | 2.2 | 5.9 | 36.8 | 207.2 | 511.9 | 899.5 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.664 | 1.960 | 5.499 | 11.653 | 20.841 | 32.315 | 43.194 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 15319.3 | 91181.0 | 32497.2 | 15334.0 | 8574.2 | 5529.6 | 4137.0 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2397764 | 1098022 | 200444 | 208749 | 303222 | 472210 | 113488 | 1629 | | | |
|---|
| Avg. Per Day | 829.799 | 379.995 | 69.368 | 72.242 | 104.937 | 163.419 | 39.275 | 0.564 | | | |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-20 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 110.4 | 2.0 | 4.7 | 23.7 | 106.4 | 330.2 | 589.0 | 1058.8 | | | |
| Avg. Seconds | 11.698 | 1.469 | 4.314 | 9.394 | 17.890 | 28.396 | 41.336 | 52.508 | | | |
|---|
| Avg. Words Per Sec | 15275.1 | 121612.3 | 41420.9 | 19021.2 | 9988.5 | 6292.7 | 4322.9 | 3403.1 | | | |
|---|
Len
12 | Searches | 1714537 | 756633 | 146098 | 139958 | 183531 | 256694 | 213096 | 18485 | 42 | | |
|---|
| Avg. Per Day | 593.353 | 261.849 | 50.560 | 48.436 | 63.515 | 88.835 | 73.747 | 6.397 | 0.015 | | |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2016-08-17 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 92.4 | 1.8 | 3.7 | 12.2 | 67.6 | 175.4 | 395.8 | 696.9 | 1117.5 | | |
| Avg. Seconds | 11.156 | 1.070 | 3.124 | 6.983 | 13.642 | 23.339 | 35.116 | 48.789 | 81.720 | | |
|---|
| Avg. Words Per Sec | 16018.1 | 166977.7 | 57193.0 | 25590.1 | 13098.7 | 7656.2 | 5088.6 | 3662.6 | 2186.6 | | |
|---|
Len
13 | Searches | 1250377 | 533477 | 104554 | 98038 | 114799 | 167451 | 170481 | 59850 | 1727 | | |
|---|
| Avg. Per Day | 432.720 | 184.621 | 36.183 | 33.928 | 39.729 | 57.950 | 58.999 | 20.712 | 0.598 | | |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-22 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 78.2 | 1.7 | 3.3 | 8.8 | 37.4 | 139.4 | 239.5 | 433.1 | 742.4 | | |
| Avg. Seconds | 10.030 | 0.762 | 2.190 | 4.816 | 9.705 | 17.571 | 28.077 | 41.749 | 53.488 | | |
|---|
| Avg. Words Per Sec | 17814.8 | 234386.6 | 81580.4 | 37106.7 | 18411.5 | 10169.9 | 6364.3 | 4280.1 | 3340.8 | | |
|---|
Len
14 | Searches | 908854 | 371844 | 72487 | 66625 | 70118 | 101468 | 118973 | 91459 | 15837 | 43 | |
|---|
| Avg. Per Day | 314.529 | 128.685 | 25.086 | 23.057 | 24.266 | 35.115 | 41.173 | 31.651 | 5.481 | 0.015 | |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-21 | 2019-02-22 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 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 | 74.4 | 1.7 | 2.8 | 6.5 | 21.7 | 91.2 | 174.6 | 291.3 | 512.9 | 568.2 | |
| Avg. Seconds | 9.331 | 0.533 | 1.400 | 3.116 | 6.390 | 12.279 | 21.118 | 32.881 | 47.810 | 48.488 | |
|---|
| Avg. Words Per Sec | 19150.7 | 335144.5 | 127663.5 | 57339.0 | 27962.5 | 14552.6 | 8461.6 | 5434.5 | 3737.5 | 3685.3 | |
|---|
Len
15 | Searches | 653100 | 258847 | 50084 | 46311 | 47490 | 58022 | 81107 | 71450 | 35556 | 4233 | |
|---|
| Avg. Per Day | 226.020 | 89.580 | 17.333 | 16.027 | 16.435 | 20.080 | 28.069 | 24.727 | 12.305 | 1.465 | |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-22 | 2019-02-21 | 2019-02-22 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | |
| 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 | 66.3 | 1.4 | 2.2 | 4.5 | 13.0 | 51.0 | 132.0 | 197.9 | 328.9 | 592.5 | |
| Avg. Seconds | 8.170 | 0.318 | 0.811 | 1.867 | 3.957 | 7.879 | 14.468 | 24.488 | 37.651 | 51.924 | |
|---|
| Avg. Words Per Sec | 21870.9 | 562777.6 | 220337.2 | 95694.4 | 45154.1 | 22680.0 | 12350.8 | 7297.0 | 4746.0 | 3441.4 | |
|---|
Len
16 | Searches | 487811 | 168410 | 39478 | 35065 | 33440 | 35061 | 47883 | 61489 | 47907 | 17625 | 1453 |
|---|
| Avg. Per Day | 168.818 | 58.282 | 13.662 | 12.135 | 11.573 | 12.134 | 16.571 | 21.280 | 16.579 | 6.100 | 0.503 |
| 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 | 2019-02-23 | 2019-02-23 | 2019-02-16 | 2019-02-23 | 2019-02-15 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-23 | 2019-02-02 |
| 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 | 64.0 | 1.0 | 1.5 | 2.4 | 4.9 | 14.9 | 61.7 | 146.2 | 224.7 | 377.5 | 597.9 |
| Avg. Seconds | 8.058 | 0.156 | 0.397 | 0.973 | 2.223 | 4.591 | 9.153 | 16.855 | 27.592 | 42.349 | 52.710 |
|---|
| Avg. Words Per Sec | 22174.8 | 1145100.2 | 449735.0 | 183721.0 | 80375.6 | 38921.1 | 19522.9 | 10601.6 | 6476.1 | 4219.5 | 3390.1 |
|---|