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 | 44794278 | 24709575 | 4641960 | 7672289 | 4650463 | 1951777 | 746395 | 299317 | 99382 | 21694 | 1426 |
|---|
| Avg. Per Day | 16237.827 | 8957.167 | 1682.700 | 2781.188 | 1685.783 | 707.515 | 270.567 | 108.502 | 36.026 | 7.864 | 0.517 |
| 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 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-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 | 169.2 | 7.1 | 115.0 | 459.6 | 471.7 | 402.0 | 318.6 | 295.8 | 316.2 | 419.4 | 595.3 |
| Avg. Seconds | 7.278 | 1.614 | 4.114 | 10.717 | 20.615 | 27.432 | 29.008 | 30.722 | 35.053 | 44.396 | 53.099 |
|---|
| Avg. Words Per Sec | 24553.7 | 110689.2 | 43437.1 | 16672.9 | 8667.9 | 6514.0 | 6160.2 | 5816.5 | 5097.8 | 4025.0 | 3365.2 |
|---|
Len
2 | Searches | 177448 | 177448 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 64.325 | 64.325 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2018-10-15 | 2018-10-15 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.5 | 27.5 |
| Avg. Seconds | 0.158 | 0.158 |
|---|
| Avg. Words Per Sec | 1129207.0 | 1129207.0 |
|---|
Len
3 | Searches | 1248060 | 867264 | 380796 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 452.419 | 314.381 | 138.038 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2018-10-15 | 2018-10-15 | 2018-10-15 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 147.2 | 25.7 | 423.8 |
| Avg. Seconds | 0.485 | 0.292 | 0.926 |
|---|
| Avg. Words Per Sec | 368166.9 | 612675.4 | 192867.3 |
|---|
Len
4 | Searches | 2817031 | 2066261 | 548218 | 202552 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1021.168 | 749.015 | 198.728 | 73.425 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 166.8 | 18.1 | 288.2 | 1355.9 |
| Avg. Seconds | 1.074 | 0.560 | 1.793 | 4.369 |
|---|
| Avg. Words Per Sec | 166362.9 | 318921.3 | 99642.8 | 40900.5 |
|---|
Len
5 | Searches | 5156034 | 3216658 | 698934 | 1237308 | 3134 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1869.051 | 1166.031 | 253.362 | 448.521 | 1.136 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 252.0 | 11.0 | 149.4 | 933.7 | 1442.6 |
| Avg. Seconds | 2.762 | 0.992 | 3.091 | 7.159 | 10.098 |
|---|
| Avg. Words Per Sec | 64691.6 | 180045.6 | 57811.7 | 24961.3 | 17696.1 |
|---|
Len
6 | Searches | 6300037 | 3822209 | 652740 | 1734001 | 91087 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2283.750 | 1385.542 | 236.617 | 628.572 | 33.019 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 205.8 | 7.2 | 92.5 | 625.3 | 1368.7 | |
| Avg. Seconds | 4.469 | 1.568 | 4.634 | 10.176 | 16.391 | |
|---|
| Avg. Words Per Sec | 39984.0 | 113986.6 | 38559.7 | 17559.8 | 10901.7 | |
|---|
Len
7 | Searches | 7119046 | 4024407 | 607156 | 1772058 | 714271 | 1154 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2580.639 | 1458.839 | 220.093 | 642.367 | 258.922 | 0.418 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-14 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 198.4 | 5.3 | 53.8 | 400.4 | 907.4 | 1035.9 | |
| Avg. Seconds | 6.916 | 2.153 | 6.006 | 12.617 | 20.347 | 27.521 | |
|---|
| Avg. Words Per Sec | 25836.8 | 82979.2 | 29752.2 | 14162.4 | 8782.3 | 6492.8 | |
|---|
Len
8 | Searches | 6387919 | 3377484 | 475901 | 1159710 | 1329355 | 45154 | 315 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2315.607 | 1224.331 | 172.513 | 420.392 | 481.888 | 16.368 | 0.114 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-08-16 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 171.7 | 3.4 | 19.9 | 199.3 | 595.5 | 1165.4 | 1664.4 | |
| Avg. Seconds | 9.316 | 2.502 | 6.659 | 13.906 | 22.801 | 31.883 | 41.385 | |
|---|
| Avg. Words Per Sec | 19181.6 | 71426.3 | 26836.3 | 12849.8 | 7837.1 | 5604.5 | 4317.8 | |
|---|
Len
9 | Searches | 4956846 | 2447071 | 389210 | 643496 | 1159441 | 317308 | 320 | | | N/A | N/A |
|---|
| Avg. Per Day | 1796.846 | 887.058 | 141.088 | 233.266 | 420.295 | 115.023 | 0.116 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-09-18 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 150.8 | 2.6 | 9.3 | 79.1 | 389.0 | 741.8 | 1318.2 | | |
| Avg. Seconds | 11.118 | 2.409 | 6.485 | 13.604 | 23.221 | 34.669 | 43.270 | | |
|---|
| Avg. Words Per Sec | 16071.9 | 74176.1 | 27555.4 | 13135.0 | 7695.2 | 5154.2 | 4129.6 | | |
|---|
Len
10 | Searches | 3354757 | 1594794 | 277878 | 331142 | 606492 | 524616 | 19763 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1216.092 | 578.109 | 100.730 | 120.038 | 219.852 | 190.172 | 7.164 | 0.021 | 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 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 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 | 127.0 | 2.2 | 5.9 | 36.6 | 203.4 | 510.1 | 896.9 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.793 | 1.985 | 5.509 | 11.692 | 21.228 | 32.882 | 43.786 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 15152.4 | 90017.7 | 32435.9 | 15283.4 | 8417.8 | 5434.2 | 4081.0 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2343869 | 1069539 | 199680 | 207421 | 299790 | 455137 | 110724 | 1578 | | | |
|---|
| Avg. Per Day | 849.647 | 387.706 | 72.384 | 75.190 | 108.673 | 164.986 | 40.137 | 0.572 | | | |
| 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 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-12 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 109.0 | 2.0 | 4.7 | 23.6 | 105.5 | 327.4 | 588.3 | 1059.5 | | | |
| Avg. Seconds | 11.794 | 1.484 | 4.321 | 9.421 | 17.983 | 28.860 | 41.797 | 53.271 | | | |
|---|
| Avg. Words Per Sec | 15151.5 | 120405.5 | 41350.9 | 18967.4 | 9936.8 | 6191.6 | 4275.2 | 3354.4 | | | |
|---|
Len
12 | Searches | 1682053 | 739202 | 145652 | 139396 | 182165 | 250579 | 206873 | 18144 | 42 | | |
|---|
| Avg. Per Day | 609.741 | 267.959 | 52.799 | 50.531 | 66.034 | 90.834 | 74.991 | 6.577 | 0.015 | | |
| 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 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-14 | 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.3 | 1.8 | 3.7 | 12.2 | 67.1 | 173.8 | 393.9 | 695.9 | 1117.5 | | |
| Avg. Seconds | 11.228 | 1.080 | 3.129 | 6.995 | 13.688 | 23.588 | 35.565 | 49.191 | 81.720 | | |
|---|
| Avg. Words Per Sec | 15914.4 | 165493.7 | 57116.3 | 25544.5 | 13054.8 | 7575.5 | 5024.3 | 3632.6 | 2186.6 | | |
|---|
Len
13 | Searches | 1228476 | 522023 | 104216 | 97700 | 114233 | 164922 | 164649 | 59058 | 1675 | | |
|---|
| Avg. Per Day | 445.320 | 189.232 | 37.778 | 35.416 | 41.409 | 59.784 | 59.685 | 21.408 | 0.607 | | |
| 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 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-12 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-14 | 2018-09-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 | 77.3 | 1.7 | 3.3 | 8.8 | 37.3 | 138.1 | 237.0 | 432.0 | 743.9 | | |
| Avg. Seconds | 10.088 | 0.769 | 2.194 | 4.823 | 9.727 | 17.689 | 28.496 | 41.999 | 54.229 | | |
|---|
| Avg. Words Per Sec | 17713.3 | 232425.2 | 81461.8 | 37049.2 | 18369.9 | 10101.7 | 6270.7 | 4254.6 | 3295.1 | | |
|---|
Len
14 | Searches | 896046 | 364439 | 72311 | 66429 | 69883 | 100424 | 116776 | 90029 | 15712 | 43 | |
|---|
| Avg. Per Day | 324.815 | 132.108 | 26.213 | 24.080 | 25.332 | 36.403 | 42.331 | 32.635 | 5.696 | 0.016 | |
| 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 | 2018-10-15 | 2018-10-15 | 2018-10-09 | 2018-10-12 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 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.0 | 1.7 | 2.8 | 6.5 | 21.7 | 90.6 | 173.3 | 290.6 | 512.1 | 568.2 | |
| Avg. Seconds | 9.389 | 0.537 | 1.401 | 3.120 | 6.400 | 12.335 | 21.286 | 33.107 | 47.983 | 48.488 | |
|---|
| Avg. Words Per Sec | 19032.5 | 332523.6 | 127523.6 | 57265.8 | 27921.0 | 14486.3 | 8394.6 | 5397.4 | 3724.1 | 3685.3 | |
|---|
Len
15 | Searches | 644016 | 254350 | 49930 | 46155 | 47315 | 57581 | 79385 | 69830 | 35282 | 4188 | |
|---|
| Avg. Per Day | 233.454 | 92.201 | 18.100 | 16.731 | 17.152 | 20.873 | 28.777 | 25.313 | 12.790 | 1.518 | |
| 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 | 2018-10-15 | 2018-10-15 | 2018-10-12 | 2018-10-12 | 2018-10-11 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-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 | 65.8 | 1.4 | 2.2 | 4.5 | 13.0 | 50.9 | 130.1 | 196.5 | 328.8 | 591.4 | |
| Avg. Seconds | 8.211 | 0.320 | 0.812 | 1.870 | 3.964 | 7.905 | 14.599 | 24.725 | 37.784 | 52.158 | |
|---|
| Avg. Words Per Sec | 21762.5 | 558788.1 | 220030.8 | 95549.7 | 45080.5 | 22604.5 | 12239.7 | 7227.0 | 4729.3 | 3426.0 | |
|---|
Len
16 | Searches | 482640 | 166426 | 39338 | 34921 | 33297 | 34902 | 47590 | 60619 | 46658 | 17463 | 1426 |
|---|
| Avg. Per Day | 174.956 | 60.329 | 14.260 | 12.659 | 12.070 | 12.652 | 17.251 | 21.974 | 16.913 | 6.330 | 0.517 |
| 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 | 2018-10-15 | 2018-10-15 | 2018-10-09 | 2018-10-09 | 2018-10-07 | 2018-10-08 | 2018-10-15 | 2018-10-15 | 2018-10-15 | 2018-10-13 | 2018-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 | 63.5 | 1.0 | 1.5 | 2.4 | 4.9 | 14.9 | 61.6 | 145.7 | 223.9 | 377.8 | 595.3 |
| Avg. Seconds | 8.071 | 0.157 | 0.398 | 0.974 | 2.227 | 4.600 | 9.177 | 16.961 | 27.901 | 42.524 | 53.099 |
|---|
| Avg. Words Per Sec | 22139.1 | 1139322.9 | 449032.6 | 183397.1 | 80221.4 | 38844.4 | 19470.6 | 10535.6 | 6404.5 | 4202.1 | 3365.2 |
|---|