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 | 16515271 | 8965163 | 2328513 | 2889628 | 1393312 | 577933 | 223296 | 96515 | 33537 | 7265 | 109 |
|---|
| Avg. Per Day | 11529.351 | 6258.602 | 1625.541 | 2017.256 | 972.675 | 403.456 | 155.883 | 67.377 | 23.412 | 5.072 | 0.076 |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 |
| 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 | 137.0 | 7.4 | 110.7 | 392.9 | 368.2 | 334.0 | 265.3 | 256.7 | 286.4 | 424.6 | 562.3 |
| Avg. Seconds | 7.013 | 1.818 | 4.086 | 11.304 | 21.918 | 27.967 | 28.198 | 29.616 | 34.637 | 44.338 | 53.358 |
|---|
| Avg. Words Per Sec | 25479.6 | 98288.6 | 43736.9 | 15807.2 | 8152.9 | 6389.4 | 6337.1 | 6033.7 | 5158.9 | 4030.2 | 3348.9 |
|---|
Len
2 | Searches | 62872 | 62872 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 43.891 | 43.891 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2015-02-27 | 2015-02-27 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.2 | 27.2 |
| Avg. Seconds | 0.227 | 0.227 |
|---|
| Avg. Words Per Sec | 787788.0 | 787788.0 |
|---|
Len
3 | Searches | 515638 | 337814 | 177824 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 359.968 | 235.829 | 124.139 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2015-02-27 | 2015-02-27 | 2015-02-27 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 165.5 | 25.1 | 432.3 |
| Avg. Seconds | 0.598 | 0.390 | 0.994 |
|---|
| Avg. Words Per Sec | 298788.4 | 458528.0 | 179796.9 |
|---|
Len
4 | Searches | 1136677 | 810217 | 269567 | 56893 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 793.517 | 565.614 | 188.185 | 39.717 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 146.0 | 17.5 | 278.3 | 1348.6 |
| Avg. Seconds | 1.185 | 0.708 | 1.864 | 4.776 |
|---|
| Avg. Words Per Sec | 150754.5 | 252565.1 | 95888.8 | 37411.8 |
|---|
Len
5 | Searches | 2009107 | 1268776 | 351018 | 389110 | 203 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1402.563 | 885.736 | 245.047 | 271.639 | 0.142 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-18 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 206.5 | 10.7 | 146.6 | 899.0 | 405.0 |
| Avg. Seconds | 2.823 | 1.217 | 3.139 | 7.771 | 12.128 |
|---|
| Avg. Words Per Sec | 63296.8 | 146851.2 | 56934.1 | 22994.3 | 14734.1 |
|---|
Len
6 | Searches | 2408522 | 1458031 | 327972 | 606616 | 15903 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1681.395 | 1017.855 | 228.958 | 423.480 | 11.102 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 172.3 | 7.2 | 89.9 | 582.1 | 1372.0 | |
| Avg. Seconds | 4.685 | 1.879 | 4.630 | 11.071 | 19.493 | |
|---|
| Avg. Words Per Sec | 38140.9 | 95106.3 | 38591.5 | 16140.3 | 9166.7 | |
|---|
Len
7 | Searches | 2556663 | 1447656 | 306980 | 652620 | 148940 | 467 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1784.813 | 1010.612 | 214.303 | 455.596 | 103.975 | 0.326 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-20 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 152.7 | 5.5 | 51.8 | 365.5 | 856.9 | 1011.2 | |
| Avg. Seconds | 6.964 | 2.509 | 5.893 | 13.440 | 24.023 | 30.358 | |
|---|
| Avg. Words Per Sec | 25659.3 | 71219.6 | 30324.6 | 13295.2 | 7438.5 | 5886.2 | |
|---|
Len
8 | Searches | 2205435 | 1151799 | 244916 | 461584 | 338637 | 8430 | 69 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1539.620 | 804.074 | 170.976 | 322.233 | 236.403 | 5.885 | 0.048 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 130.6 | 3.6 | 20.4 | 188.5 | 538.4 | 1114.8 | 1626.3 | |
| Avg. Seconds | 9.360 | 2.829 | 6.454 | 14.352 | 26.186 | 36.673 | 43.296 | |
|---|
| Avg. Words Per Sec | 19090.4 | 63163.9 | 27687.1 | 12450.8 | 6823.8 | 4872.5 | 4127.2 | |
|---|
Len
9 | Searches | 1744742 | 818851 | 201492 | 282915 | 362949 | 78502 | 33 | | | N/A | N/A |
|---|
| Avg. Per Day | 1218.009 | 571.642 | 140.662 | 197.504 | 253.376 | 54.802 | 0.023 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-21 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 118.1 | 2.7 | 9.7 | 73.9 | 344.5 | 711.7 | 621.6 | | |
| Avg. Seconds | 11.206 | 2.638 | 6.262 | 13.425 | 25.516 | 39.086 | 54.961 | | |
|---|
| Avg. Words Per Sec | 15946.2 | 67742.6 | 28534.0 | 13310.0 | 7003.0 | 4571.7 | 3251.2 | | |
|---|
Len
10 | Searches | 1173816 | 527240 | 138736 | 153173 | 211703 | 138835 | 4060 | 56 | 13 | | N/A |
|---|
| Avg. Per Day | 819.444 | 368.068 | 96.852 | 106.930 | 147.790 | 96.921 | 2.834 | 0.039 | 0.009 | |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2014-12-17 | 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 | 98.0 | 2.3 | 6.2 | 35.0 | 179.9 | 474.2 | 895.6 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.484 | 2.143 | 5.290 | 11.341 | 21.878 | 36.413 | 46.366 | 55.862 | 41.984 | |
|---|
| Avg. Words Per Sec | 15560.5 | 83386.8 | 33779.9 | 15756.5 | 8167.7 | 4907.4 | 3853.9 | 3198.8 | 4256.2 | |
|---|
Len
11 | Searches | 836606 | 354900 | 101057 | 98633 | 119447 | 132398 | 29695 | 476 | | | |
|---|
| Avg. Per Day | 584.037 | 247.757 | 70.548 | 68.856 | 83.386 | 92.427 | 20.730 | 0.332 | | | |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 84.0 | 2.1 | 4.9 | 22.9 | 93.2 | 289.7 | 564.9 | 1118.3 | | | |
| Avg. Seconds | 11.215 | 1.581 | 4.150 | 9.062 | 17.675 | 30.626 | 44.290 | 55.936 | | | |
|---|
| Avg. Words Per Sec | 15933.7 | 113021.0 | 43058.0 | 19718.5 | 10109.7 | 5834.6 | 4034.6 | 3194.5 | | | |
|---|
Len
12 | Searches | 610168 | 251177 | 72766 | 66965 | 74823 | 83928 | 54775 | 5726 | 8 | | |
|---|
| Avg. Per Day | 425.960 | 175.347 | 50.798 | 46.748 | 52.234 | 58.590 | 38.239 | 3.997 | 0.006 | | |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2014-01-29 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 68.1 | 1.9 | 3.9 | 12.5 | 56.3 | 150.7 | 352.5 | 662.8 | 946.0 | | |
| Avg. Seconds | 10.226 | 1.146 | 3.018 | 6.742 | 13.239 | 23.561 | 36.889 | 50.807 | 132.110 | | |
|---|
| Avg. Words Per Sec | 17474.9 | 155990.0 | 59213.8 | 26505.4 | 13497.0 | 7584.3 | 4844.1 | 3517.1 | 1352.6 | | |
|---|
Len
13 | Searches | 457004 | 183034 | 52546 | 47579 | 49978 | 57173 | 48793 | 17322 | 579 | | |
|---|
| Avg. Per Day | 319.036 | 127.776 | 36.682 | 33.215 | 34.890 | 39.913 | 34.063 | 12.093 | 0.404 | | |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 57.4 | 1.8 | 3.3 | 9.1 | 33.6 | 111.6 | 203.4 | 398.2 | 707.9 | | |
| Avg. Seconds | 8.946 | 0.812 | 2.118 | 4.643 | 9.367 | 17.207 | 28.829 | 41.495 | 51.777 | | |
|---|
| Avg. Words Per Sec | 19975.3 | 220004.4 | 84382.9 | 38483.1 | 19077.5 | 10384.5 | 6198.4 | 4306.3 | 3451.2 | | |
|---|
Len
14 | Searches | 339539 | 132295 | 36874 | 32561 | 31624 | 37126 | 38070 | 25850 | 5096 | 43 | |
|---|
| Avg. Per Day | 237.033 | 92.355 | 25.742 | 22.731 | 22.077 | 25.918 | 26.577 | 18.046 | 3.558 | 0.030 | |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 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 | 54.6 | 1.7 | 2.9 | 6.6 | 20.6 | 75.0 | 146.6 | 251.4 | 477.9 | 568.2 | |
| Avg. Seconds | 8.153 | 0.567 | 1.353 | 3.011 | 6.176 | 11.950 | 21.025 | 33.267 | 47.857 | 48.488 | |
|---|
| Avg. Words Per Sec | 21916.5 | 314906.6 | 132022.4 | 59344.3 | 28931.2 | 14953.0 | 8498.9 | 5371.4 | 3733.8 | 3685.3 | |
|---|
Len
15 | Searches | 256876 | 96239 | 25830 | 23199 | 22667 | 24052 | 27017 | 23979 | 12052 | 1841 | |
|---|
| Avg. Per Day | 179.326 | 67.185 | 18.032 | 16.195 | 15.824 | 16.791 | 18.861 | 16.740 | 8.414 | 1.285 | |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | |
| 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 | 50.7 | 1.5 | 2.3 | 4.5 | 12.7 | 44.0 | 101.7 | 168.4 | 289.1 | 598.1 | |
| Avg. Seconds | 7.271 | 0.340 | 0.789 | 1.803 | 3.813 | 7.555 | 14.017 | 24.302 | 37.219 | 51.434 | |
|---|
| Avg. Words Per Sec | 24575.8 | 526101.5 | 226422.6 | 99089.3 | 46861.1 | 23651.8 | 12748.3 | 7352.9 | 4801.0 | 3474.2 | |
|---|
Len
16 | Searches | 201606 | 64262 | 20935 | 17780 | 16438 | 17022 | 20784 | 23106 | 15789 | 5381 | 109 |
|---|
| Avg. Per Day | 140.742 | 44.861 | 14.615 | 12.412 | 11.475 | 11.883 | 14.509 | 16.130 | 11.022 | 3.756 | 0.076 |
| 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 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 | 2015-02-27 |
| 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 | 49.0 | 1.0 | 1.5 | 2.4 | 4.9 | 15.0 | 54.6 | 130.4 | 204.8 | 364.0 | 562.3 |
| Avg. Seconds | 6.808 | 0.167 | 0.388 | 0.941 | 2.157 | 4.382 | 8.750 | 16.282 | 27.716 | 41.877 | 53.358 |
|---|
| Avg. Words Per Sec | 26248.1 | 1068465.1 | 460703.8 | 189872.0 | 82860.3 | 40780.0 | 20420.7 | 10974.6 | 6447.2 | 4267.0 | 3348.9 |
|---|