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 | 47222029 | 26214151 | 4737035 | 8083254 | 4962565 | 2025126 | 769857 | 305325 | 101313 | 21942 | 1461 |
|---|
| Avg. Per Day | 16214.201 | 9000.916 | 1626.513 | 2775.474 | 1703.951 | 695.349 | 264.339 | 104.837 | 34.787 | 7.534 | 0.502 |
| 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-17 | 2019-03-15 |
| 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 | 172.1 | 7.1 | 117.1 | 468.7 | 482.6 | 406.5 | 321.3 | 296.9 | 316.0 | 419.4 | 598.9 |
| Avg. Seconds | 7.109 | 1.578 | 4.070 | 10.474 | 20.098 | 27.041 | 28.653 | 30.454 | 34.726 | 44.174 | 52.601 |
|---|
| Avg. Words Per Sec | 25134.4 | 113254.1 | 43909.3 | 17061.1 | 8890.8 | 6608.2 | 6236.3 | 5867.7 | 5145.8 | 4045.2 | 3397.1 |
|---|
Len
2 | Searches | 182769 | 182769 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 62.756 | 62.756 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2019-03-18 | 2019-03-18 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.5 | 27.5 |
| Avg. Seconds | 0.156 | 0.156 |
|---|
| Avg. Words Per Sec | 1147376.5 | 1147376.5 |
|---|
Len
3 | Searches | 1303164 | 904944 | 398220 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 447.456 | 310.722 | 136.733 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2019-03-18 | 2019-03-18 | 2019-03-18 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 147.8 | 25.7 | 425.2 |
| Avg. Seconds | 0.476 | 0.285 | 0.910 |
|---|
| Avg. Words Per Sec | 375141.2 | 626133.6 | 196311.9 |
|---|
Len
4 | Searches | 2976640 | 2186388 | 569277 | 220975 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1022.062 | 750.720 | 195.467 | 75.874 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 169.4 | 18.1 | 290.2 | 1355.6 |
| Avg. Seconds | 1.053 | 0.546 | 1.766 | 4.234 |
|---|
| Avg. Words Per Sec | 169723.4 | 327458.5 | 101203.0 | 42201.0 |
|---|
Len
5 | Searches | 5492274 | 3442081 | 714801 | 1331840 | 3552 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1885.832 | 1181.876 | 245.435 | 457.302 | 1.220 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 253.9 | 11.0 | 150.6 | 933.9 | 1460.6 |
| Avg. Seconds | 2.694 | 0.963 | 3.061 | 6.953 | 9.769 |
|---|
| Avg. Words Per Sec | 66317.5 | 185524.0 | 58373.8 | 25698.7 | 18291.3 |
|---|
Len
6 | Searches | 6753660 | 4121762 | 669398 | 1863128 | 99372 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2318.943 | 1415.252 | 229.845 | 639.725 | 34.120 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 206.9 | 7.1 | 93.0 | 627.9 | 1368.9 | |
| Avg. Seconds | 4.340 | 1.518 | 4.584 | 9.880 | 15.856 | |
|---|
| Avg. Words Per Sec | 41175.2 | 117716.7 | 38983.7 | 18085.3 | 11269.4 | |
|---|
Len
7 | Searches | 7615982 | 4338815 | 617784 | 1872093 | 786084 | 1206 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2615.031 | 1489.780 | 212.123 | 642.804 | 269.911 | 0.414 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-11 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 201.1 | 5.2 | 53.8 | 404.9 | 911.0 | 1031.0 | |
| Avg. Seconds | 6.726 | 2.087 | 5.960 | 12.322 | 19.574 | 27.019 | |
|---|
| Avg. Words Per Sec | 26566.9 | 85604.9 | 29982.2 | 14502.2 | 9128.8 | 6613.5 | |
|---|
Len
8 | Searches | 6789740 | 3604587 | 482160 | 1209792 | 1445002 | 47883 | 316 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2331.332 | 1237.674 | 165.555 | 415.395 | 496.157 | 16.441 | 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-14 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 175.1 | 3.4 | 19.9 | 202.2 | 599.4 | 1168.6 | 1664.0 | |
| Avg. Seconds | 9.102 | 2.437 | 6.620 | 13.648 | 22.012 | 31.121 | 41.320 | |
|---|
| Avg. Words Per Sec | 19632.8 | 73321.8 | 26992.1 | 13093.3 | 8117.9 | 5741.9 | 4324.5 | |
|---|
Len
9 | Searches | 5193599 | 2574799 | 392476 | 656188 | 1238543 | 331270 | 323 | | | N/A | N/A |
|---|
| Avg. Per Day | 1783.279 | 884.085 | 134.761 | 225.309 | 425.267 | 113.745 | 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-17 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 153.5 | 2.6 | 9.3 | 80.0 | 393.8 | 743.3 | 1312.9 | | |
| Avg. Seconds | 10.916 | 2.361 | 6.461 | 13.483 | 22.565 | 34.013 | 43.068 | | |
|---|
| Avg. Words Per Sec | 16370.1 | 75671.3 | 27657.4 | 13253.0 | 7919.1 | 5253.6 | 4149.1 | | |
|---|
Len
10 | Searches | 3477455 | 1658148 | 279311 | 333959 | 636186 | 549214 | 20565 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1194.022 | 569.343 | 95.904 | 114.668 | 218.441 | 188.579 | 7.061 | 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 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.3 | 2.2 | 5.9 | 36.9 | 207.7 | 512.2 | 899.8 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.641 | 1.955 | 5.497 | 11.648 | 20.789 | 32.229 | 43.050 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 15349.6 | 91382.5 | 32508.9 | 15341.5 | 8595.5 | 5544.5 | 4150.8 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2407125 | 1103021 | 200559 | 208925 | 303820 | 475118 | 114041 | 1641 | | | |
|---|
| Avg. Per Day | 826.513 | 378.734 | 68.864 | 71.737 | 104.320 | 163.137 | 39.157 | 0.563 | | | |
| 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-15 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 110.7 | 2.0 | 4.7 | 23.8 | 106.5 | 330.7 | 589.3 | 1058.4 | | | |
| Avg. Seconds | 11.682 | 1.467 | 4.313 | 9.391 | 17.873 | 28.319 | 41.246 | 52.340 | | | |
|---|
| Avg. Words Per Sec | 15296.5 | 121826.2 | 41431.7 | 19028.3 | 9997.6 | 6310.0 | 4332.3 | 3414.1 | | | |
|---|
Len
12 | Searches | 1720525 | 759641 | 146212 | 140081 | 183838 | 257697 | 214423 | 18591 | 42 | | |
|---|
| Avg. Per Day | 590.761 | 260.831 | 50.203 | 48.098 | 63.123 | 88.483 | 73.624 | 6.383 | 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-17 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 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.6 | 1.8 | 3.7 | 12.2 | 67.8 | 175.6 | 396.2 | 697.3 | 1117.5 | | |
| Avg. Seconds | 11.144 | 1.068 | 3.123 | 6.980 | 13.632 | 23.298 | 35.021 | 48.663 | 81.720 | | |
|---|
| Avg. Words Per Sec | 16034.8 | 167235.9 | 57213.5 | 25600.3 | 13108.3 | 7669.8 | 5102.4 | 3672.0 | 2186.6 | | |
|---|
Len
13 | Searches | 1254038 | 535389 | 104630 | 98117 | 114915 | 167832 | 171422 | 60003 | 1730 | | |
|---|
| Avg. Per Day | 430.588 | 183.832 | 35.926 | 33.690 | 39.457 | 57.627 | 58.860 | 20.603 | 0.594 | | |
| 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-11 | | |
| 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.4 | 1.7 | 3.3 | 8.8 | 37.4 | 139.5 | 240.0 | 433.3 | 742.6 | | |
| Avg. Seconds | 10.020 | 0.761 | 2.190 | 4.814 | 9.701 | 17.553 | 28.011 | 41.701 | 53.446 | | |
|---|
| Avg. Words Per Sec | 17832.6 | 234714.2 | 81608.0 | 37120.5 | 18419.9 | 10180.3 | 6379.3 | 4285.1 | 3343.4 | | |
|---|
Len
14 | Searches | 911196 | 373161 | 72548 | 66683 | 70211 | 101685 | 119339 | 91678 | 15848 | 43 | |
|---|
| Avg. Per Day | 312.869 | 128.129 | 24.910 | 22.896 | 24.108 | 34.915 | 40.976 | 31.479 | 5.442 | 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-17 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-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 | 74.4 | 1.7 | 2.8 | 6.5 | 21.7 | 91.2 | 174.7 | 291.5 | 513.0 | 568.2 | |
| Avg. Seconds | 9.319 | 0.532 | 1.399 | 3.115 | 6.387 | 12.268 | 21.089 | 32.846 | 47.794 | 48.488 | |
|---|
| Avg. Words Per Sec | 19175.2 | 335611.9 | 127712.9 | 57361.9 | 27978.9 | 14566.1 | 8473.0 | 5440.3 | 3738.7 | 3685.3 | |
|---|
Len
15 | Searches | 654999 | 259815 | 50140 | 46369 | 47556 | 58114 | 81475 | 71682 | 35610 | 4238 | |
|---|
| Avg. Per Day | 224.901 | 89.210 | 17.216 | 15.921 | 16.329 | 19.954 | 27.975 | 24.613 | 12.227 | 1.455 | |
| 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-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-18 | 2019-03-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.4 | 1.4 | 2.2 | 4.5 | 13.0 | 51.0 | 132.4 | 198.0 | 328.9 | 592.5 | |
| Avg. Seconds | 8.159 | 0.317 | 0.811 | 1.866 | 3.955 | 7.873 | 14.441 | 24.454 | 37.625 | 51.897 | |
|---|
| Avg. Words Per Sec | 21899.9 | 563646.5 | 220448.0 | 95750.6 | 45182.5 | 22696.2 | 12374.2 | 7307.2 | 4749.2 | 3443.2 | |
|---|
Len
16 | Searches | 488863 | 168831 | 39519 | 35104 | 33486 | 35107 | 47953 | 61671 | 48070 | 17661 | 1461 |
|---|
| Avg. Per Day | 167.856 | 57.970 | 13.569 | 12.053 | 11.498 | 12.054 | 16.465 | 21.175 | 16.505 | 6.064 | 0.502 |
| 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-03-18 | 2019-03-18 | 2019-03-15 | 2019-03-11 | 2019-03-17 | 2019-03-17 | 2019-03-17 | 2019-03-18 | 2019-03-18 | 2019-03-17 | 2019-03-15 |
| 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.9 | 61.7 | 146.3 | 224.9 | 377.5 | 598.9 |
| Avg. Seconds | 8.053 | 0.156 | 0.397 | 0.972 | 2.222 | 4.588 | 9.147 | 16.833 | 27.552 | 42.310 | 52.601 |
|---|
| Avg. Words Per Sec | 22189.1 | 1146337.1 | 449948.1 | 183814.4 | 80426.8 | 38943.8 | 19535.6 | 10615.8 | 6485.5 | 4223.3 | 3397.1 |
|---|