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 | 47484268 | 26322354 | 4748559 | 8156094 | 5012937 | 2037274 | 775663 | 306320 | 101595 | 22008 | 1464 |
|---|
| Avg. Per Day | 16090.076 | 8919.347 | 1609.052 | 2763.698 | 1698.637 | 690.332 | 262.834 | 103.797 | 34.426 | 7.457 | 0.496 |
| 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-25 | 2019-04-19 |
| 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 | 173.2 | 7.1 | 117.5 | 470.7 | 484.2 | 407.4 | 322.1 | 297.2 | 316.2 | 419.3 | 599.7 |
| Avg. Seconds | 7.101 | 1.576 | 4.063 | 10.429 | 20.016 | 26.974 | 28.566 | 30.410 | 34.678 | 44.108 | 52.553 |
|---|
| Avg. Words Per Sec | 25165.7 | 113410.7 | 43974.8 | 17134.2 | 8927.2 | 6624.6 | 6255.5 | 5876.1 | 5152.8 | 4051.2 | 3400.2 |
|---|
Len
2 | Searches | 183455 | 183455 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 62.164 | 62.164 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2019-04-26 | 2019-04-26 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.5 | 27.5 |
| Avg. Seconds | 0.155 | 0.155 |
|---|
| Avg. Words Per Sec | 1149723.0 | 1149723.0 |
|---|
Len
3 | Searches | 1309093 | 908180 | 400913 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 443.587 | 307.737 | 135.850 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2019-04-26 | 2019-04-26 | 2019-04-26 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 148.2 | 25.7 | 425.6 |
| Avg. Seconds | 0.476 | 0.285 | 0.908 |
|---|
| Avg. Words Per Sec | 375670.4 | 627254.3 | 196832.9 |
|---|
Len
4 | Searches | 2990892 | 2191940 | 572431 | 226521 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1013.466 | 742.740 | 193.969 | 76.757 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 171.4 | 18.1 | 290.4 | 1354.7 |
| Avg. Seconds | 1.054 | 0.545 | 1.762 | 4.197 |
|---|
| Avg. Words Per Sec | 169463.3 | 327837.1 | 101440.3 | 42578.3 |
|---|
Len
5 | Searches | 5521714 | 3450156 | 716649 | 1351309 | 3600 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1871.036 | 1169.088 | 242.837 | 457.892 | 1.220 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-25 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 255.9 | 11.0 | 150.8 | 933.6 | 1461.7 |
| Avg. Seconds | 2.696 | 0.962 | 3.058 | 6.913 | 9.734 |
|---|
| Avg. Words Per Sec | 66275.3 | 185708.8 | 58440.0 | 25848.8 | 18357.1 |
|---|
Len
6 | Searches | 6792809 | 4135763 | 670976 | 1885464 | 100606 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2301.748 | 1401.406 | 227.361 | 638.891 | 34.090 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 208.2 | 7.1 | 93.1 | 628.5 | 1368.3 | |
| Avg. Seconds | 4.338 | 1.516 | 4.579 | 9.831 | 15.781 | |
|---|
| Avg. Words Per Sec | 41194.3 | 117879.5 | 39024.4 | 18176.1 | 11323.1 | |
|---|
Len
7 | Searches | 7662544 | 4356713 | 618594 | 1888724 | 797301 | 1212 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2596.458 | 1476.275 | 209.611 | 639.995 | 270.166 | 0.411 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 202.3 | 5.2 | 53.8 | 405.8 | 911.1 | 1029.0 | |
| Avg. Seconds | 6.720 | 2.084 | 5.956 | 12.274 | 19.462 | 26.957 | |
|---|
| Avg. Words Per Sec | 26589.2 | 85744.5 | 30000.2 | 14558.7 | 9181.5 | 6628.8 | |
|---|
Len
8 | Searches | 6835750 | 3624031 | 482504 | 1216315 | 1464135 | 48449 | 316 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2316.298 | 1228.005 | 163.497 | 412.149 | 496.123 | 16.417 | 0.107 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-03-14 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 176.1 | 3.4 | 19.9 | 202.6 | 600.1 | 1168.3 | 1664.0 | |
| Avg. Seconds | 9.088 | 2.432 | 6.618 | 13.613 | 21.888 | 30.969 | 41.320 | |
|---|
| Avg. Words Per Sec | 19661.9 | 73476.7 | 27000.8 | 13126.3 | 8164.0 | 5770.0 | 4324.5 | |
|---|
Len
9 | Searches | 5227187 | 2590855 | 392758 | 657456 | 1251854 | 333940 | 324 | | | N/A | N/A |
|---|
| Avg. Per Day | 1771.236 | 877.913 | 133.086 | 222.779 | 424.192 | 113.156 | 0.110 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-10 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 154.2 | 2.6 | 9.3 | 80.1 | 394.7 | 743.4 | 1313.7 | | |
| Avg. Seconds | 10.893 | 2.356 | 6.459 | 13.471 | 22.456 | 33.887 | 43.003 | | |
|---|
| Avg. Words Per Sec | 16404.7 | 75856.4 | 27666.5 | 13265.3 | 7957.3 | 5273.1 | 4155.3 | | |
|---|
Len
10 | Searches | 3495821 | 1667801 | 279523 | 334347 | 640343 | 552957 | 20778 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1184.561 | 565.135 | 94.717 | 113.294 | 216.981 | 187.370 | 7.041 | 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-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 | 129.7 | 2.2 | 5.9 | 36.9 | 208.4 | 512.7 | 899.4 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.618 | 1.951 | 5.495 | 11.641 | 20.727 | 32.127 | 42.852 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 15381.0 | 91592.7 | 32520.0 | 15349.8 | 8621.2 | 5562.0 | 4169.9 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2417739 | 1108652 | 200677 | 209088 | 304398 | 478430 | 114845 | 1649 | | | |
|---|
| Avg. Per Day | 819.252 | 375.667 | 68.000 | 70.850 | 103.145 | 162.116 | 38.915 | 0.559 | | | |
| 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-23 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 111.1 | 2.0 | 4.7 | 23.8 | 106.7 | 331.2 | 589.9 | 1058.4 | | | |
| Avg. Seconds | 11.663 | 1.464 | 4.312 | 9.387 | 17.857 | 28.228 | 41.110 | 52.223 | | | |
|---|
| Avg. Words Per Sec | 15320.7 | 122075.0 | 41442.9 | 19035.3 | 10006.8 | 6330.2 | 4346.7 | 3421.7 | | | |
|---|
Len
12 | Searches | 1727396 | 762797 | 146316 | 140189 | 184100 | 258656 | 216551 | 18745 | 42 | | |
|---|
| Avg. Per Day | 585.329 | 258.474 | 49.579 | 47.503 | 62.382 | 87.646 | 73.378 | 6.352 | 0.014 | | |
| 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 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 | 93.1 | 1.8 | 3.7 | 12.2 | 67.9 | 175.9 | 397.0 | 697.9 | 1117.5 | | |
| Avg. Seconds | 11.135 | 1.067 | 3.122 | 6.978 | 13.623 | 23.256 | 34.868 | 48.485 | 81.720 | | |
|---|
| Avg. Words Per Sec | 16047.3 | 167517.2 | 57232.3 | 25609.5 | 13117.2 | 7683.6 | 5124.7 | 3685.5 | 2186.6 | | |
|---|
Len
13 | Searches | 1258597 | 537395 | 104738 | 98237 | 115062 | 168300 | 172934 | 60189 | 1742 | | |
|---|
| Avg. Per Day | 426.476 | 182.097 | 35.491 | 33.288 | 38.989 | 57.029 | 58.599 | 20.395 | 0.590 | | |
| 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-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 | 78.7 | 1.7 | 3.3 | 8.8 | 37.4 | 139.7 | 240.9 | 433.6 | 743.3 | | |
| Avg. Seconds | 10.012 | 0.760 | 2.189 | 4.811 | 9.695 | 17.529 | 27.903 | 41.641 | 53.285 | | |
|---|
| Avg. Words Per Sec | 17848.3 | 235068.7 | 81645.3 | 37141.0 | 18430.8 | 10193.8 | 6404.1 | 4291.2 | 3353.5 | | |
|---|
Len
14 | Searches | 913694 | 374435 | 72644 | 66790 | 70313 | 101903 | 119810 | 91881 | 15875 | 43 | |
|---|
| Avg. Per Day | 309.606 | 126.878 | 24.615 | 22.632 | 23.826 | 34.530 | 40.598 | 31.134 | 5.379 | 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 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.3 | 175.0 | 291.6 | 513.3 | 568.2 | |
| Avg. Seconds | 9.307 | 0.532 | 1.398 | 3.113 | 6.382 | 12.255 | 21.051 | 32.812 | 47.756 | 48.488 | |
|---|
| Avg. Words Per Sec | 19200.5 | 336085.4 | 127790.8 | 57404.8 | 27998.0 | 14580.6 | 8488.4 | 5445.9 | 3741.7 | 3685.3 | |
|---|
Len
15 | Searches | 657262 | 260793 | 50232 | 46463 | 47651 | 58234 | 82056 | 71915 | 35676 | 4242 | |
|---|
| Avg. Per Day | 222.714 | 88.370 | 17.021 | 15.744 | 16.147 | 19.733 | 27.805 | 24.368 | 12.089 | 1.437 | |
| 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-15 | |
| 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.5 | 1.4 | 2.2 | 4.5 | 13.0 | 51.0 | 133.1 | 198.1 | 329.0 | 592.6 | |
| Avg. Seconds | 8.147 | 0.317 | 0.810 | 1.864 | 3.951 | 7.866 | 14.395 | 24.417 | 37.593 | 51.874 | |
|---|
| Avg. Words Per Sec | 21933.9 | 564530.8 | 220632.9 | 95841.4 | 45223.5 | 22717.8 | 12413.2 | 7318.2 | 4753.4 | 3444.7 | |
|---|
Len
16 | Searches | 490315 | 169388 | 39604 | 35191 | 33574 | 35193 | 48049 | 61882 | 48247 | 17723 | 1464 |
|---|
| Avg. Per Day | 166.144 | 57.397 | 13.420 | 11.924 | 11.377 | 11.925 | 16.281 | 20.969 | 16.349 | 6.005 | 0.496 |
| 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-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-26 | 2019-04-25 | 2019-04-19 |
| 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.1 | 1.0 | 1.5 | 2.4 | 4.9 | 14.8 | 61.6 | 146.4 | 225.1 | 377.5 | 599.7 |
| Avg. Seconds | 8.043 | 0.156 | 0.397 | 0.971 | 2.219 | 4.583 | 9.138 | 16.805 | 27.505 | 42.239 | 52.553 |
|---|
| Avg. Words Per Sec | 22216.6 | 1147985.5 | 450388.4 | 184022.3 | 80525.0 | 38986.9 | 19553.8 | 10633.1 | 6496.6 | 4230.5 | 3400.2 |
|---|