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 | 38886434 | 20974395 | 4408060 | 6682304 | 3948491 | 1785662 | 692716 | 280464 | 92751 | 20384 | 1207 |
|---|
| Avg. Per Day | 16232.789 | 8755.571 | 1840.105 | 2789.467 | 1648.262 | 745.408 | 289.168 | 117.077 | 38.718 | 8.509 | 0.504 |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-16 |
| 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 | 161.1 | 7.1 | 109.8 | 428.6 | 437.1 | 392.8 | 315.0 | 293.1 | 315.3 | 418.2 | 593.0 |
| Avg. Seconds | 7.664 | 1.695 | 4.208 | 11.227 | 21.529 | 28.109 | 29.690 | 31.371 | 35.939 | 45.296 | 55.599 |
|---|
| Avg. Words Per Sec | 23316.9 | 105433.5 | 42466.6 | 15916.0 | 8300.0 | 6357.0 | 6018.6 | 5696.0 | 4972.0 | 3944.9 | 3213.9 |
|---|
Len
2 | Searches | 155556 | 155556 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 64.935 | 64.935 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2017-10-17 | 2017-10-17 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.4 | 27.4 |
| Avg. Seconds | 0.167 | 0.167 |
|---|
| Avg. Words Per Sec | 1067307.2 | 1067307.2 |
|---|
Len
3 | Searches | 1087896 | 751912 | 335984 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 454.132 | 313.879 | 140.253 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2017-10-17 | 2017-10-17 | 2017-10-17 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 147.8 | 25.8 | 420.8 |
| Avg. Seconds | 0.511 | 0.309 | 0.962 |
|---|
| Avg. Words Per Sec | 349895.9 | 577862.6 | 185831.4 |
|---|
Len
4 | Searches | 2391042 | 1735864 | 501182 | 153996 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 998.119 | 724.621 | 209.214 | 64.284 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 159.3 | 18.2 | 281.3 | 1352.7 |
| Avg. Seconds | 1.122 | 0.597 | 1.842 | 4.684 |
|---|
| Avg. Words Per Sec | 159316.3 | 299083.4 | 96987.3 | 38149.0 |
|---|
Len
5 | Searches | 4309936 | 2652267 | 661882 | 994429 | 1358 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1799.144 | 1107.165 | 276.297 | 415.115 | 0.567 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-16 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 243.7 | 11.0 | 146.1 | 927.8 | 1243.5 |
| Avg. Seconds | 2.898 | 1.059 | 3.145 | 7.625 | 11.505 |
|---|
| Avg. Words Per Sec | 61661.8 | 168676.1 | 56814.8 | 23434.1 | 15531.3 |
|---|
Len
6 | Searches | 5268493 | 3150281 | 623839 | 1429489 | 64884 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2199.285 | 1315.056 | 260.416 | 596.727 | 27.085 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 198.4 | 7.2 | 91.9 | 613.1 | 1367.6 | |
| Avg. Seconds | 4.702 | 1.669 | 4.704 | 10.782 | 17.978 | |
|---|
| Avg. Words Per Sec | 38002.4 | 107039.6 | 37985.0 | 16573.1 | 9939.3 | |
|---|
Len
7 | Searches | 6007825 | 3348586 | 583186 | 1533585 | 541484 | 984 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2507.912 | 1397.837 | 243.446 | 640.181 | 226.038 | 0.411 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 187.1 | 5.3 | 53.9 | 383.5 | 896.9 | 1029.2 | |
| Avg. Seconds | 7.218 | 2.278 | 6.086 | 13.184 | 22.052 | 28.994 | |
|---|
| Avg. Words Per Sec | 24754.8 | 78428.4 | 29363.2 | 13553.3 | 8103.1 | 6163.0 | |
|---|
Len
8 | Searches | 5495816 | 2871918 | 457488 | 1055743 | 1071101 | 39266 | 300 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2294.179 | 1198.856 | 190.974 | 440.710 | 447.121 | 16.391 | 0.125 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-03 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 161.7 | 3.4 | 19.9 | 190.3 | 581.5 | 1159.2 | 1671.0 | |
| Avg. Seconds | 9.667 | 2.622 | 6.745 | 14.321 | 24.342 | 33.246 | 41.816 | |
|---|
| Avg. Words Per Sec | 18485.6 | 68160.4 | 26492.3 | 12477.6 | 7340.7 | 5374.8 | 4273.2 | |
|---|
Len
9 | Searches | 4420392 | 2136105 | 377501 | 618360 | 998337 | 289785 | 304 | | | N/A | N/A |
|---|
| Avg. Per Day | 1845.253 | 891.698 | 157.584 | 258.129 | 416.747 | 120.968 | 0.127 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-09-15 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 145.8 | 2.6 | 9.3 | 77.7 | 373.7 | 738.6 | 1306.0 | | |
| Avg. Seconds | 11.538 | 2.499 | 6.549 | 13.783 | 24.340 | 35.739 | 43.930 | | |
|---|
| Avg. Words Per Sec | 15486.9 | 71493.6 | 27286.4 | 12964.8 | 7341.5 | 4999.9 | 4067.6 | | |
|---|
Len
10 | Searches | 3035257 | 1402719 | 270460 | 321042 | 550669 | 471905 | 18390 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1267.041 | 585.552 | 112.901 | 134.016 | 229.872 | 196.992 | 7.677 | 0.025 | 0.005 | |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 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 | 124.4 | 2.2 | 5.9 | 36.4 | 193.1 | 505.0 | 894.8 | 21.0 | 2710.5 | |
| Avg. Seconds | 12.226 | 2.048 | 5.556 | 11.808 | 21.890 | 34.033 | 44.838 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 14615.4 | 87236.1 | 32161.4 | 15133.1 | 8163.1 | 5250.5 | 3985.3 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2144396 | 943701 | 195275 | 201906 | 288228 | 408766 | 105031 | 1489 | | | |
|---|
| Avg. Per Day | 895.159 | 393.939 | 81.516 | 84.284 | 120.318 | 170.636 | 43.844 | 0.622 | | | |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-15 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 107.5 | 2.0 | 4.7 | 23.6 | 103.0 | 318.6 | 585.8 | 1059.8 | | | |
| Avg. Seconds | 12.222 | 1.526 | 4.348 | 9.492 | 18.201 | 29.861 | 42.553 | 54.181 | | | |
|---|
| Avg. Words Per Sec | 14620.9 | 117075.6 | 41101.8 | 18826.1 | 9817.4 | 5984.1 | 4199.3 | 3298.0 | | | |
|---|
Len
12 | Searches | 1551507 | 653836 | 142383 | 135850 | 176063 | 233948 | 192305 | 17080 | 42 | | |
|---|
| Avg. Per Day | 647.663 | 272.938 | 59.436 | 56.709 | 73.496 | 97.659 | 80.276 | 7.130 | 0.018 | | |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 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 | 91.1 | 1.8 | 3.7 | 12.2 | 65.8 | 169.4 | 389.6 | 691.4 | 1117.5 | | |
| Avg. Seconds | 11.642 | 1.110 | 3.147 | 7.041 | 13.821 | 24.090 | 36.409 | 50.266 | 81.720 | | |
|---|
| Avg. Words Per Sec | 15348.5 | 161035.0 | 56785.4 | 25377.5 | 12929.0 | 7417.5 | 4907.9 | 3554.9 | 2186.6 | | |
|---|
Len
13 | Searches | 1134202 | 463992 | 101742 | 95068 | 110876 | 155826 | 148492 | 56598 | 1608 | | |
|---|
| Avg. Per Day | 473.462 | 193.689 | 42.471 | 39.685 | 46.284 | 65.048 | 61.987 | 23.626 | 0.671 | | |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-09-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 | 76.4 | 1.7 | 3.3 | 8.8 | 37.3 | 134.0 | 229.8 | 430.5 | 744.5 | | |
| Avg. Seconds | 10.407 | 0.790 | 2.206 | 4.854 | 9.803 | 17.984 | 29.388 | 42.581 | 54.978 | | |
|---|
| Avg. Words Per Sec | 17169.5 | 226101.1 | 80996.0 | 36814.4 | 18228.8 | 9936.3 | 6080.3 | 4196.5 | 3250.2 | | |
|---|
Len
14 | Searches | 831540 | 326580 | 70467 | 64421 | 67572 | 96043 | 108190 | 83205 | 15019 | 43 | |
|---|
| Avg. Per Day | 347.119 | 136.328 | 29.416 | 26.892 | 28.207 | 40.092 | 45.163 | 34.733 | 6.270 | 0.018 | |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-16 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 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 | 73.2 | 1.7 | 2.8 | 6.5 | 21.8 | 89.1 | 167.8 | 286.3 | 509.3 | 568.2 | |
| Avg. Seconds | 9.655 | 0.552 | 1.409 | 3.141 | 6.452 | 12.489 | 21.772 | 33.916 | 48.727 | 48.488 | |
|---|
| Avg. Words Per Sec | 18507.1 | 323817.6 | 126784.3 | 56884.8 | 27695.8 | 14308.0 | 8207.2 | 5268.7 | 3667.2 | 3685.3 | |
|---|
Len
15 | Searches | 599568 | 229824 | 48431 | 44612 | 45725 | 55442 | 73752 | 64359 | 33475 | 3948 | |
|---|
| Avg. Per Day | 250.284 | 95.938 | 20.217 | 18.623 | 19.087 | 23.144 | 30.787 | 26.866 | 13.974 | 1.648 | |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-16 | |
| 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 | 64.8 | 1.4 | 2.2 | 4.5 | 13.0 | 50.8 | 125.5 | 191.3 | 326.4 | 586.0 | |
| Avg. Seconds | 8.422 | 0.328 | 0.818 | 1.884 | 3.995 | 7.982 | 14.907 | 25.333 | 38.399 | 53.149 | |
|---|
| Avg. Words Per Sec | 21217.8 | 544209.0 | 218556.9 | 94837.3 | 44723.9 | 22385.8 | 11987.0 | 7053.8 | 4653.5 | 3362.1 | |
|---|
Len
16 | Searches | 453008 | 151254 | 38240 | 33803 | 32194 | 33697 | 45952 | 57674 | 42594 | 16393 | 1207 |
|---|
| Avg. Per Day | 189.104 | 63.140 | 15.963 | 14.111 | 13.439 | 14.067 | 19.182 | 24.075 | 17.780 | 6.843 | 0.504 |
| 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 | 2017-10-17 | 2017-10-17 | 2017-10-16 | 2017-10-16 | 2017-10-16 | 2017-10-17 | 2017-10-16 | 2017-10-17 | 2017-10-17 | 2017-10-17 | 2017-10-16 |
| 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 | 62.7 | 1.0 | 1.5 | 2.4 | 4.9 | 14.8 | 61.5 | 144.2 | 220.5 | 377.4 | 593.0 |
| Avg. Seconds | 8.221 | 0.161 | 0.401 | 0.983 | 2.248 | 4.643 | 9.273 | 17.231 | 28.731 | 43.397 | 55.599 |
|---|
| Avg. Words Per Sec | 21737.1 | 1108492.6 | 445344.3 | 181766.7 | 79481.3 | 38484.8 | 19269.7 | 10370.2 | 6219.5 | 4117.6 | 3213.9 |
|---|