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 | 52028222 | 28980811 | 5056841 | 8882591 | 5531061 | 2243549 | 863546 | 334376 | 110619 | 23198 | 1630 |
|---|
| Avg. Per Day | 16128.166 | 8983.727 | 1567.564 | 2753.504 | 1714.567 | 695.475 | 267.690 | 103.653 | 34.291 | 7.191 | 0.505 |
| 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 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-25 | 2020-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 | 175.1 | 7.1 | 121.1 | 478.7 | 489.0 | 406.8 | 323.8 | 296.4 | 315.6 | 418.4 | 617.0 |
| Avg. Seconds | 6.895 | 1.540 | 3.963 | 10.101 | 19.397 | 26.110 | 27.554 | 29.531 | 33.653 | 43.404 | 51.410 |
|---|
| Avg. Words Per Sec | 25915.7 | 116038.4 | 45089.5 | 17690.2 | 9212.4 | 6843.8 | 6485.1 | 6050.9 | 5309.9 | 4116.9 | 3475.8 |
|---|
Len
2 | Searches | 200012 | 200012 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 62.001 | 62.001 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2020-01-26 | 2020-01-26 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.5 | 27.5 |
| Avg. Seconds | 0.149 | 0.149 |
|---|
| Avg. Words Per Sec | 1198963.7 | 1198963.7 |
|---|
Len
3 | Searches | 1458934 | 1008922 | 450012 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 452.253 | 312.755 | 139.499 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2020-01-26 | 2020-01-26 | 2020-01-26 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 150.0 | 25.7 | 428.8 |
| Avg. Seconds | 0.457 | 0.272 | 0.871 |
|---|
| Avg. Words Per Sec | 391380.6 | 657137.6 | 205266.4 |
|---|
Len
4 | Searches | 3322153 | 2424113 | 628861 | 269179 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1029.830 | 751.448 | 194.940 | 83.442 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 179.0 | 18.2 | 295.5 | 1354.8 |
| Avg. Seconds | 1.026 | 0.525 | 1.700 | 3.970 |
|---|
| Avg. Words Per Sec | 174094.1 | 340453.0 | 105131.1 | 45008.9 |
|---|
Len
5 | Searches | 6013183 | 3743113 | 756051 | 1509943 | 4076 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1864.019 | 1160.323 | 234.367 | 468.065 | 1.264 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-25 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 261.8 | 11.1 | 152.2 | 935.1 | 1474.5 |
| Avg. Seconds | 2.637 | 0.937 | 2.996 | 6.652 | 9.484 |
|---|
| Avg. Words Per Sec | 67766.2 | 190658.8 | 59636.5 | 26863.6 | 18840.4 |
|---|
Len
6 | Searches | 7381609 | 4496429 | 707724 | 2061806 | 115650 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2288.216 | 1393.843 | 219.387 | 639.137 | 35.850 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 210.6 | 7.2 | 92.8 | 629.3 | 1373.4 | |
| Avg. Seconds | 4.227 | 1.481 | 4.493 | 9.517 | 15.104 | |
|---|
| Avg. Words Per Sec | 42268.8 | 120685.8 | 39775.3 | 18776.2 | 11831.1 | |
|---|
Len
7 | Searches | 8349425 | 4766225 | 651899 | 2043995 | 885931 | 1375 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2588.228 | 1477.476 | 202.081 | 633.616 | 274.629 | 0.426 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 203.9 | 5.3 | 53.6 | 407.2 | 912.8 | 1004.4 | |
| Avg. Seconds | 6.545 | 2.040 | 5.863 | 11.937 | 18.815 | 25.696 | |
|---|
| Avg. Words Per Sec | 27301.7 | 87597.2 | 30477.1 | 14970.1 | 9497.1 | 6954.0 | |
|---|
Len
8 | Searches | 7492057 | 4004589 | 508904 | 1308733 | 1615531 | 53969 | 331 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2322.454 | 1241.378 | 157.755 | 405.693 | 500.797 | 16.730 | 0.103 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-15 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 177.1 | 3.4 | 20.0 | 203.8 | 602.3 | 1166.0 | 1658.1 | |
| Avg. Seconds | 8.825 | 2.380 | 6.521 | 13.284 | 21.204 | 29.901 | 40.546 | |
|---|
| Avg. Words Per Sec | 20247.7 | 75067.6 | 27401.4 | 13451.5 | 8427.2 | 5976.1 | 4407.1 | |
|---|
Len
9 | Searches | 5748364 | 2890431 | 411378 | 699013 | 1387643 | 359518 | 381 | | | N/A | N/A |
|---|
| Avg. Per Day | 1781.928 | 896.001 | 127.523 | 216.686 | 430.154 | 111.447 | 0.118 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-25 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 154.3 | 2.6 | 9.4 | 81.2 | 397.4 | 742.9 | 1203.0 | | |
| Avg. Seconds | 10.523 | 2.300 | 6.378 | 13.202 | 21.681 | 33.062 | 40.989 | | |
|---|
| Avg. Words Per Sec | 16981.6 | 77684.1 | 28016.5 | 13534.7 | 8241.9 | 5404.8 | 4359.5 | | |
|---|
Len
10 | Searches | 3848214 | 1865249 | 292088 | 353027 | 705728 | 608866 | 23184 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1192.903 | 578.206 | 90.544 | 109.434 | 218.768 | 188.742 | 7.187 | 0.018 | 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 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 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 | 130.5 | 2.2 | 5.9 | 37.1 | 212.6 | 512.8 | 902.8 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.235 | 1.902 | 5.437 | 11.468 | 20.066 | 31.089 | 41.278 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 15904.3 | 93926.7 | 32864.7 | 15581.8 | 8905.1 | 5747.7 | 4329.0 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2657461 | 1240676 | 210548 | 221400 | 328824 | 530970 | 123235 | 1808 | | | |
|---|
| Avg. Per Day | 823.783 | 384.596 | 65.268 | 68.632 | 101.932 | 164.595 | 38.201 | 0.560 | | | |
| 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 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-25 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-25 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 111.6 | 2.0 | 4.7 | 23.8 | 108.3 | 333.4 | 593.6 | 1066.2 | | | |
| Avg. Seconds | 11.279 | 1.427 | 4.267 | 9.263 | 17.442 | 27.256 | 40.206 | 50.405 | | | |
|---|
| Avg. Words Per Sec | 15843.1 | 125245.1 | 41876.8 | 19291.7 | 10244.8 | 6555.9 | 4444.3 | 3545.1 | | | |
|---|
Len
12 | Searches | 1909372 | 854670 | 155105 | 150035 | 198708 | 288457 | 242230 | 20118 | 49 | | |
|---|
| Avg. Per Day | 591.884 | 264.938 | 48.081 | 46.509 | 61.597 | 89.418 | 75.089 | 6.236 | 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 | 2020-01-26 | 2020-01-26 | 2020-01-25 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-22 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 94.4 | 1.9 | 3.7 | 12.4 | 68.4 | 178.5 | 400.2 | 701.4 | 1150.4 | | |
| Avg. Seconds | 10.795 | 1.038 | 3.080 | 6.870 | 13.346 | 22.449 | 33.558 | 47.526 | 80.102 | | |
|---|
| Avg. Words Per Sec | 16552.7 | 172100.6 | 58022.8 | 26009.9 | 13388.8 | 7959.9 | 5324.8 | 3759.9 | 2230.8 | | |
|---|
Len
13 | Searches | 1391260 | 600529 | 111489 | 105636 | 124827 | 186658 | 196458 | 63814 | 1849 | | |
|---|
| Avg. Per Day | 431.275 | 186.157 | 34.560 | 32.746 | 38.695 | 57.862 | 60.900 | 19.782 | 0.573 | | |
| 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 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-25 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 79.5 | 1.7 | 3.3 | 8.9 | 37.6 | 140.9 | 245.3 | 433.3 | 746.3 | | |
| Avg. Seconds | 9.705 | 0.740 | 2.156 | 4.728 | 9.487 | 16.981 | 26.760 | 40.899 | 52.506 | | |
|---|
| Avg. Words Per Sec | 18412.4 | 241587.9 | 82882.1 | 37792.8 | 18835.2 | 10523.2 | 6677.5 | 4369.1 | 3403.2 | | |
|---|
Len
14 | Searches | 1006200 | 417036 | 77392 | 72123 | 76565 | 111769 | 133725 | 100853 | 16694 | 43 | |
|---|
| Avg. Per Day | 311.911 | 129.276 | 23.991 | 22.357 | 23.734 | 34.647 | 41.453 | 31.263 | 5.175 | 0.013 | |
| 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 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-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 | 74.6 | 1.7 | 2.8 | 6.6 | 21.8 | 92.3 | 176.2 | 292.4 | 513.5 | 568.2 | |
| Avg. Seconds | 9.024 | 0.517 | 1.376 | 3.054 | 6.249 | 11.943 | 20.345 | 31.878 | 47.100 | 48.488 | |
|---|
| Avg. Words Per Sec | 19802.2 | 345617.7 | 129837.7 | 58514.7 | 28596.0 | 14962.4 | 8782.8 | 5605.4 | 3793.9 | 3685.3 | |
|---|
Len
15 | Searches | 729943 | 289243 | 54454 | 51141 | 52565 | 65185 | 93669 | 81117 | 38183 | 4386 | |
|---|
| Avg. Per Day | 226.274 | 89.662 | 16.880 | 15.853 | 16.295 | 20.207 | 29.036 | 25.145 | 11.836 | 1.360 | |
| 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 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-01-26 | 2020-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 | 67.0 | 1.4 | 2.2 | 4.5 | 13.1 | 51.6 | 136.4 | 199.2 | 329.7 | 593.0 | |
| Avg. Seconds | 7.886 | 0.308 | 0.792 | 1.815 | 3.847 | 7.610 | 13.810 | 23.506 | 36.839 | 51.574 | |
|---|
| Avg. Words Per Sec | 22659.1 | 580871.9 | 225574.3 | 98449.2 | 46444.8 | 23480.9 | 12939.1 | 7602.0 | 4850.6 | 3464.8 | |
|---|
Len
16 | Searches | 520035 | 179574 | 40936 | 36560 | 35013 | 36782 | 50333 | 66607 | 53831 | 18769 | 1630 |
|---|
| Avg. Per Day | 161.205 | 55.666 | 12.690 | 11.333 | 10.854 | 11.402 | 15.603 | 20.647 | 16.687 | 5.818 | 0.505 |
| 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 | 2020-01-26 | 2020-01-25 | 2020-01-25 | 2020-01-26 | 2020-01-26 | 2020-01-25 | 2020-01-25 | 2020-01-26 | 2020-01-26 | 2020-01-25 | 2020-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 | 65.9 | 1.0 | 1.4 | 2.4 | 4.9 | 14.8 | 61.5 | 147.0 | 228.0 | 377.3 | 617.0 |
| Avg. Seconds | 7.994 | 0.153 | 0.392 | 0.958 | 2.187 | 4.514 | 8.999 | 16.400 | 26.531 | 41.483 | 51.410 |
|---|
| Avg. Words Per Sec | 22353.6 | 1166060.9 | 455906.7 | 186559.3 | 81690.2 | 39583.8 | 19857.4 | 10895.7 | 6735.3 | 4307.6 | 3475.8 |
|---|