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 | 33832465 | 17717438 | 4221959 | 6059186 | 3366032 | 1517041 | 603753 | 246635 | 81664 | 17944 | 813 |
|---|
| Avg. Per Day | 16798.238 | 8796.928 | 2096.255 | 3008.461 | 1671.277 | 753.230 | 299.771 | 122.457 | 40.547 | 8.909 | 0.404 |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-30 | 2016-09-30 |
| 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 | 156.0 | 7.0 | 107.9 | 410.0 | 406.9 | 368.3 | 301.6 | 284.1 | 308.6 | 415.2 | 577.1 |
| Avg. Seconds | 7.891 | 1.753 | 4.251 | 11.491 | 22.183 | 28.782 | 30.236 | 31.925 | 36.715 | 46.213 | 59.162 |
|---|
| Avg. Words Per Sec | 22644.9 | 101926.6 | 42033.6 | 15550.4 | 8055.3 | 6208.4 | 5909.8 | 5597.1 | 4866.9 | 3866.7 | 3020.4 |
|---|
Len
2 | Searches | 125550 | 125550 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 62.337 | 62.337 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2016-10-01 | 2016-10-01 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.3 | 27.3 |
| Avg. Seconds | 0.182 | 0.182 |
|---|
| Avg. Words Per Sec | 981920.2 | 981920.2 |
|---|
Len
3 | Searches | 947698 | 635342 | 312356 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 470.544 | 315.455 | 155.089 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2016-10-01 | 2016-10-01 | 2016-10-01 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 155.0 | 25.7 | 418.0 |
| Avg. Seconds | 0.541 | 0.328 | 0.975 |
|---|
| Avg. Words Per Sec | 330233.2 | 544944.8 | 183318.1 |
|---|
Len
4 | Searches | 2064122 | 1462433 | 474516 | 127173 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1024.862 | 726.116 | 235.603 | 63.143 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 159.8 | 18.1 | 278.3 | 1346.2 |
| Avg. Seconds | 1.173 | 0.628 | 1.861 | 4.861 |
|---|
| Avg. Words Per Sec | 152400.2 | 284414.0 | 96007.5 | 36758.6 |
|---|
Len
5 | Searches | 3728761 | 2221124 | 636884 | 869998 | 755 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1851.376 | 1102.816 | 316.221 | 431.965 | 0.375 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-27 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 245.7 | 10.9 | 144.8 | 918.2 | 1019.0 |
| Avg. Seconds | 3.034 | 1.110 | 3.168 | 7.840 | 12.523 |
|---|
| Avg. Words Per Sec | 58899.7 | 161023.6 | 56408.8 | 22792.9 | 14268.7 |
|---|
Len
6 | Searches | 4546425 | 2635397 | 599801 | 1262529 | 48698 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2257.356 | 1308.507 | 297.809 | 626.861 | 24.179 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 197.7 | 7.1 | 91.7 | 601.1 | 1360.8 | |
| Avg. Seconds | 4.918 | 1.743 | 4.741 | 11.080 | 19.144 | |
|---|
| Avg. Words Per Sec | 36336.8 | 102515.5 | 37692.8 | 16128.0 | 9334.1 | |
|---|
Len
7 | Searches | 5142183 | 2785824 | 558145 | 1376527 | 420821 | 866 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2553.158 | 1383.196 | 277.126 | 683.463 | 208.943 | 0.430 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-21 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 180.6 | 5.3 | 54.0 | 371.5 | 882.6 | 1030.1 | |
| Avg. Seconds | 7.487 | 2.374 | 6.139 | 13.508 | 23.383 | 29.926 | |
|---|
| Avg. Words Per Sec | 23867.4 | 75283.0 | 29107.1 | 13229.0 | 7641.8 | 5971.1 | |
|---|
Len
8 | Searches | 4705749 | 2398436 | 438020 | 974440 | 866906 | 27676 | 271 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2336.463 | 1190.853 | 217.482 | 483.822 | 430.430 | 13.741 | 0.135 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-28 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 152.7 | 3.4 | 19.9 | 185.1 | 564.3 | 1142.5 | 1685.8 | |
| Avg. Seconds | 9.970 | 2.719 | 6.804 | 14.584 | 25.608 | 35.751 | 42.017 | |
|---|
| Avg. Words Per Sec | 17923.6 | 65710.5 | 26263.6 | 12252.7 | 6977.8 | 4998.3 | 4252.8 | |
|---|
Len
9 | Searches | 3839429 | 1806361 | 363584 | 588437 | 852927 | 227885 | 235 | | | N/A | N/A |
|---|
| Avg. Per Day | 1906.324 | 896.881 | 180.524 | 292.166 | 423.489 | 113.148 | 0.117 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-21 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 136.9 | 2.6 | 9.2 | 76.6 | 359.4 | 726.6 | 1258.4 | | |
| Avg. Seconds | 11.836 | 2.578 | 6.598 | 13.931 | 25.284 | 37.801 | 45.838 | | |
|---|
| Avg. Words Per Sec | 15097.1 | 69319.1 | 27082.0 | 12826.5 | 7067.2 | 4727.1 | 3898.3 | | |
|---|
Len
10 | Searches | 2669965 | 1205565 | 261489 | 307634 | 496195 | 384357 | 14653 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1325.671 | 598.578 | 129.833 | 152.744 | 246.367 | 190.838 | 7.275 | 0.029 | 0.006 | |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 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 | 116.0 | 2.2 | 5.9 | 36.1 | 185.7 | 492.1 | 891.0 | 21.0 | 2710.5 | |
| Avg. Seconds | 12.434 | 2.103 | 5.591 | 11.910 | 22.404 | 35.719 | 46.931 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 14371.0 | 84958.3 | 31960.1 | 15003.3 | 7975.7 | 5002.7 | 3807.5 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 1915046 | 822957 | 189232 | 193793 | 269805 | 348672 | 89364 | 1223 | | | |
|---|
| Avg. Per Day | 950.844 | 408.608 | 93.956 | 96.221 | 133.962 | 173.120 | 44.370 | 0.607 | | | |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-28 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 101.5 | 2.0 | 4.6 | 23.4 | 99.7 | 308.1 | 578.6 | 1052.7 | | | |
| Avg. Seconds | 12.401 | 1.562 | 4.373 | 9.567 | 18.456 | 30.968 | 44.033 | 56.382 | | | |
|---|
| Avg. Words Per Sec | 14409.6 | 114400.4 | 40861.7 | 18678.5 | 9681.8 | 5770.1 | 4058.1 | 3169.3 | | | |
|---|
Len
12 | Searches | 1399592 | 577149 | 137908 | 130533 | 165950 | 211167 | 162009 | 14834 | 42 | | |
|---|
| Avg. Per Day | 694.915 | 286.562 | 68.473 | 64.811 | 82.396 | 104.847 | 80.439 | 7.365 | 0.021 | | |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 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 | 85.5 | 1.8 | 3.7 | 12.1 | 63.4 | 163.9 | 378.5 | 679.0 | 1117.5 | | |
| Avg. Seconds | 11.729 | 1.133 | 3.164 | 7.089 | 13.973 | 24.602 | 37.758 | 51.613 | 81.720 | | |
|---|
| Avg. Words Per Sec | 15234.9 | 157692.1 | 56478.7 | 25206.7 | 12787.9 | 7263.3 | 4732.5 | 3462.1 | 2186.6 | | |
|---|
Len
13 | Searches | 1031151 | 411624 | 98500 | 91261 | 105719 | 143076 | 129761 | 49837 | 1373 | | |
|---|
| Avg. Per Day | 511.979 | 204.376 | 48.906 | 45.312 | 52.491 | 71.039 | 64.428 | 24.745 | 0.682 | | |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-30 | 2016-09-28 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 72.9 | 1.7 | 3.2 | 8.7 | 36.8 | 130.0 | 221.5 | 423.6 | 737.5 | | |
| Avg. Seconds | 10.497 | 0.807 | 2.218 | 4.887 | 9.880 | 18.277 | 30.209 | 43.550 | 56.340 | | |
|---|
| Avg. Words Per Sec | 17023.3 | 221519.5 | 80555.8 | 36562.9 | 18085.5 | 9776.7 | 5915.1 | 4103.2 | 3171.6 | | |
|---|
Len
14 | Searches | 752936 | 290003 | 67938 | 61655 | 64030 | 89345 | 96726 | 69969 | 13227 | 43 | |
|---|
| Avg. Per Day | 373.842 | 143.990 | 33.732 | 30.612 | 31.792 | 44.361 | 48.026 | 34.740 | 6.567 | 0.021 | |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-30 | 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 | 69.0 | 1.7 | 2.8 | 6.4 | 21.6 | 86.4 | 162.4 | 276.9 | 502.8 | 568.2 | |
| Avg. Seconds | 9.649 | 0.563 | 1.417 | 3.161 | 6.502 | 12.640 | 22.227 | 35.069 | 49.835 | 48.488 | |
|---|
| Avg. Words Per Sec | 18518.7 | 317124.2 | 126061.5 | 56532.5 | 27480.9 | 14136.8 | 8039.2 | 5095.4 | 3585.7 | 3685.3 | |
|---|
Len
15 | Searches | 545093 | 204199 | 46547 | 42679 | 43367 | 51846 | 67077 | 56999 | 28949 | 3430 | |
|---|
| Avg. Per Day | 270.645 | 101.387 | 23.111 | 21.191 | 21.532 | 25.742 | 33.305 | 28.301 | 14.374 | 1.703 | |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-30 | 2016-09-30 | 2016-10-01 | 2016-09-30 | 2016-09-30 | 2016-09-30 | |
| 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 | 61.8 | 1.4 | 2.2 | 4.5 | 12.8 | 49.7 | 121.1 | 185.7 | 319.2 | 582.8 | |
| Avg. Seconds | 8.436 | 0.336 | 0.822 | 1.894 | 4.025 | 8.061 | 15.143 | 25.881 | 39.429 | 54.152 | |
|---|
| Avg. Words Per Sec | 21182.5 | 532116.2 | 217307.4 | 94322.9 | 44394.9 | 22166.5 | 11800.6 | 6904.3 | 4532.0 | 3299.8 | |
|---|
Len
16 | Searches | 418765 | 135474 | 37039 | 32527 | 30859 | 32151 | 43657 | 53714 | 38060 | 14471 | 813 |
|---|
| Avg. Per Day | 207.922 | 67.265 | 18.390 | 16.150 | 15.322 | 15.963 | 21.676 | 26.670 | 18.897 | 7.185 | 0.404 |
| 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 | 2016-10-01 | 2016-10-01 | 2016-10-01 | 2016-09-30 | 2016-09-30 | 2016-10-01 | 2016-09-30 | 2016-10-01 | 2016-10-01 | 2016-09-30 | 2016-09-30 |
| 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 | 60.5 | 1.0 | 1.5 | 2.4 | 4.9 | 14.8 | 60.9 | 142.5 | 215.9 | 375.0 | 577.1 |
| Avg. Seconds | 8.213 | 0.165 | 0.403 | 0.987 | 2.257 | 4.664 | 9.337 | 17.440 | 29.333 | 44.324 | 59.162 |
|---|
| Avg. Words Per Sec | 21756.9 | 1081687.4 | 443308.8 | 181023.4 | 79180.9 | 38309.8 | 19138.5 | 10245.9 | 6091.8 | 4031.5 | 3020.4 |
|---|