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 | 7072572 | 4749830 | 810495 | 784347 | 407665 | 195238 | 73110 | 34980 | 13496 | 3378 | 33 |
|---|
| Avg. Per Day | 7145.324 | 4798.689 | 818.832 | 792.415 | 411.858 | 197.246 | 73.862 | 35.340 | 13.635 | 3.413 | 0.033 |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | 2013-12-11 | 2013-09-14 |
| 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 | 97.5 | 7.7 | 94.3 | 393.3 | 386.8 | 379.9 | 287.1 | 272.0 | 297.2 | 450.8 | 527.2 |
| Avg. Seconds | 6.021 | 1.929 | 3.926 | 12.005 | 25.148 | 33.342 | 31.780 | 31.422 | 35.949 | 45.743 | 51.313 |
|---|
| Avg. Words Per Sec | 29676.9 | 92637.8 | 45514.4 | 14884.9 | 7105.6 | 5359.4 | 5622.7 | 5686.7 | 4970.7 | 3906.4 | 3482.4 |
|---|
Len
2 | Searches | 30653 | 30653 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 30.968 | 30.968 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2013-12-12 | 2013-12-12 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 26.1 | 26.1 |
| Avg. Seconds | 0.309 | 0.309 |
|---|
| Avg. Words Per Sec | 578291.6 | 578291.6 |
|---|
Len
3 | Searches | 225348 | 172557 | 52791 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 227.666 | 174.332 | 53.334 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2013-12-12 | 2013-12-12 | 2013-12-12 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 115.9 | 24.0 | 416.3 |
| Avg. Seconds | 0.642 | 0.501 | 1.103 |
|---|
| Avg. Words Per Sec | 278366.7 | 356858.8 | 161939.4 |
|---|
Len
4 | Searches | 582580 | 483267 | 80732 | 18581 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 588.573 | 488.238 | 81.562 | 18.772 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 94.5 | 16.4 | 273.3 | 1348.8 |
| Avg. Seconds | 1.118 | 0.814 | 2.061 | 4.930 |
|---|
| Avg. Words Per Sec | 159862.4 | 219628.3 | 86690.6 | 36248.0 |
|---|
Len
5 | Searches | 1021049 | 796248 | 106700 | 118027 | 74 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1031.552 | 804.439 | 107.798 | 119.241 | 0.075 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 129.1 | 10.2 | 142.7 | 919.1 | 405.0 |
| Avg. Seconds | 2.368 | 1.348 | 3.323 | 8.375 | 13.058 |
|---|
| Avg. Words Per Sec | 75467.5 | 132533.0 | 53770.5 | 21335.5 | 13683.9 |
|---|
Len
6 | Searches | 1173879 | 904420 | 107902 | 154734 | 6823 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1185.954 | 913.723 | 109.012 | 156.326 | 6.893 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 100.5 | 7.1 | 82.5 | 601.5 | 1398.3 | |
| Avg. Seconds | 3.817 | 2.050 | 4.575 | 12.879 | 20.545 | |
|---|
| Avg. Words Per Sec | 46818.0 | 87180.3 | 39057.0 | 13875.1 | 8697.5 | |
|---|
Len
7 | Searches | 1128758 | 820952 | 104950 | 150374 | 52281 | 201 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1140.369 | 829.397 | 106.030 | 151.921 | 52.819 | 0.203 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 97.5 | 5.6 | 46.5 | 360.5 | 883.0 | 1097.1 | |
| Avg. Seconds | 5.901 | 2.720 | 5.482 | 15.967 | 27.629 | 30.952 | |
|---|
| Avg. Words Per Sec | 30283.0 | 65686.8 | 32593.0 | 11190.9 | 6467.5 | 5773.2 | |
|---|
Len
8 | Searches | 865341 | 556727 | 90166 | 112536 | 101699 | 4171 | 42 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 874.242 | 562.454 | 91.093 | 113.694 | 102.745 | 4.214 | 0.042 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-09 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 97.1 | 3.8 | 20.1 | 177.4 | 543.6 | 1146.0 | 1533.0 | |
| Avg. Seconds | 8.618 | 3.034 | 5.751 | 16.250 | 31.993 | 39.799 | 42.415 | |
|---|
| Avg. Words Per Sec | 20734.1 | 58899.2 | 31073.0 | 10996.4 | 5585.3 | 4489.9 | 4212.9 | |
|---|
Len
9 | Searches | 628503 | 336625 | 78593 | 77458 | 99400 | 36405 | 22 | | | N/A | N/A |
|---|
| Avg. Per Day | 634.968 | 340.088 | 79.401 | 78.255 | 100.422 | 36.779 | 0.022 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-10-05 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 105.1 | 2.8 | 9.3 | 65.0 | 334.8 | 715.6 | 298.5 | | |
| Avg. Seconds | 11.165 | 2.747 | 5.487 | 13.550 | 30.157 | 44.296 | 60.369 | | |
|---|
| Avg. Words Per Sec | 16005.2 | 65052.3 | 32567.1 | 13187.9 | 5925.4 | 4034.0 | 2960.0 | | |
|---|
Len
10 | Searches | 420065 | 203262 | 57136 | 48286 | 54365 | 54681 | 2277 | 45 | 13 | | N/A |
|---|
| Avg. Per Day | 424.386 | 205.353 | 57.724 | 48.783 | 54.924 | 55.243 | 2.300 | 0.045 | 0.013 | |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | 2013-12-04 | 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 | 95.0 | 2.3 | 6.2 | 29.7 | 166.4 | 483.5 | 939.6 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.898 | 2.199 | 4.731 | 10.585 | 24.538 | 42.468 | 48.485 | 56.675 | 41.984 | |
|---|
| Avg. Words Per Sec | 15018.1 | 81254.4 | 37773.6 | 16881.0 | 7282.2 | 4207.7 | 3685.5 | 3152.9 | 4256.2 | |
|---|
Len
11 | Searches | 299888 | 137775 | 41881 | 33695 | 32301 | 40379 | 13513 | 344 | | | |
|---|
| Avg. Per Day | 302.973 | 139.192 | 42.312 | 34.042 | 32.633 | 40.794 | 13.652 | 0.348 | | | |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-07 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 77.9 | 2.1 | 4.9 | 20.2 | 83.0 | 283.1 | 568.0 | 1154.7 | | | |
| Avg. Seconds | 11.199 | 1.599 | 3.763 | 8.300 | 18.108 | 35.580 | 48.852 | 55.587 | | | |
|---|
| Avg. Words Per Sec | 15956.2 | 111767.9 | 47487.4 | 21528.4 | 9868.0 | 5022.2 | 3657.8 | 3214.6 | | | |
|---|
Len
12 | Searches | 217991 | 100636 | 29816 | 23580 | 20890 | 21449 | 18633 | 2982 | 5 | | |
|---|
| Avg. Per Day | 220.233 | 101.671 | 30.123 | 23.823 | 21.105 | 21.670 | 18.825 | 3.013 | 0.005 | | |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | 2013-12-11 | 2013-12-11 | 2013-11-30 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 59.5 | 2.0 | 3.9 | 11.8 | 45.7 | 142.7 | 340.2 | 677.4 | 946.0 | | |
| Avg. Seconds | 9.493 | 1.149 | 2.742 | 6.117 | 12.741 | 25.073 | 41.087 | 52.781 | 156.499 | | |
|---|
| Avg. Words Per Sec | 18823.9 | 155489.8 | 65169.8 | 29211.4 | 14025.2 | 7126.7 | 4349.1 | 3385.5 | 1141.8 | | |
|---|
Len
13 | Searches | 166482 | 76979 | 21922 | 17406 | 15109 | 14126 | 13437 | 7212 | 291 | | |
|---|
| Avg. Per Day | 168.195 | 77.771 | 22.148 | 17.585 | 15.264 | 14.271 | 13.575 | 7.286 | 0.294 | | |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-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 | 45.1 | 1.8 | 3.3 | 8.6 | 26.9 | 90.0 | 182.2 | 390.2 | 683.1 | | |
| Avg. Seconds | 7.695 | 0.817 | 1.935 | 4.198 | 8.810 | 16.927 | 30.420 | 42.604 | 49.783 | | |
|---|
| Avg. Words Per Sec | 23221.5 | 218752.1 | 92337.5 | 42567.4 | 20283.3 | 10556.8 | 5874.1 | 4194.2 | 3589.4 | | |
|---|
Len
14 | Searches | 128321 | 58430 | 16020 | 12413 | 10005 | 9760 | 10270 | 8756 | 2624 | 43 | |
|---|
| Avg. Per Day | 129.641 | 59.031 | 16.185 | 12.541 | 10.108 | 9.860 | 10.376 | 8.846 | 2.651 | 0.043 | |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | 2013-12-11 | 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 | 43.9 | 1.8 | 2.9 | 6.3 | 17.9 | 58.8 | 128.4 | 236.1 | 474.2 | 568.2 | |
| Avg. Seconds | 7.018 | 0.571 | 1.237 | 2.702 | 5.570 | 11.525 | 21.090 | 34.183 | 48.652 | 48.488 | |
|---|
| Avg. Words Per Sec | 25461.6 | 313115.5 | 144444.2 | 66127.6 | 32079.8 | 15504.7 | 8472.8 | 5227.5 | 3672.8 | 3685.3 | |
|---|
Len
15 | Searches | 102294 | 44079 | 11692 | 9508 | 8109 | 7220 | 7528 | 8116 | 4952 | 1090 | |
|---|
| Avg. Per Day | 103.346 | 44.532 | 11.812 | 9.606 | 8.192 | 7.294 | 7.605 | 8.199 | 5.003 | 1.101 | |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | 2013-12-11 | 2013-12-11 | 2013-12-11 | 2013-12-10 | |
| 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 | 42.9 | 1.5 | 2.2 | 4.4 | 11.2 | 32.7 | 80.0 | 157.6 | 279.4 | 605.3 | |
| Avg. Seconds | 6.379 | 0.341 | 0.724 | 1.637 | 3.455 | 6.926 | 13.216 | 24.125 | 37.229 | 51.196 | |
|---|
| Avg. Words Per Sec | 28012.6 | 524141.6 | 246763.4 | 109190.0 | 51718.2 | 25800.5 | 13521.0 | 7406.8 | 4799.7 | 3490.3 | |
|---|
Len
16 | Searches | 81420 | 27220 | 10194 | 7749 | 6609 | 6846 | 7388 | 7525 | 5611 | 2245 | 33 |
|---|
| Avg. Per Day | 82.258 | 27.500 | 10.299 | 7.829 | 6.677 | 6.916 | 7.464 | 7.602 | 5.669 | 2.268 | 0.033 |
| 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 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-12 | 2013-12-11 | 2013-12-11 | 2013-12-11 | 2013-12-11 | 2013-09-14 |
| 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 | 43.3 | 1.0 | 1.5 | 2.4 | 4.9 | 15.2 | 53.1 | 124.2 | 203.9 | 373.6 | 527.2 |
| Avg. Seconds | 5.982 | 0.166 | 0.357 | 0.847 | 1.978 | 3.869 | 8.042 | 15.645 | 28.039 | 43.043 | 51.313 |
|---|
| Avg. Words Per Sec | 29870.8 | 1078878.2 | 500063.3 | 211031.0 | 90356.0 | 46180.4 | 22220.7 | 11421.7 | 6372.9 | 4151.5 | 3482.4 |
|---|