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 | 8257163 | 5282651 | 1007140 | 1034890 | 535935 | 243303 | 90949 | 42499 | 15963 | 3800 | 33 |
|---|
| Avg. Per Day | 7651.162 | 4894.952 | 933.225 | 958.938 | 496.602 | 225.447 | 84.274 | 39.380 | 14.791 | 3.521 | 0.031 |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-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 | 107.1 | 7.6 | 99.2 | 394.5 | 382.1 | 364.6 | 276.4 | 264.4 | 293.3 | 445.5 | 527.2 |
| Avg. Seconds | 6.548 | 1.932 | 4.096 | 12.378 | 25.715 | 33.218 | 32.113 | 32.246 | 37.108 | 46.617 | 51.313 |
|---|
| Avg. Words Per Sec | 27289.2 | 92480.7 | 43627.9 | 14436.0 | 6948.8 | 5379.4 | 5564.5 | 5541.5 | 4815.4 | 3833.2 | 3482.4 |
|---|
Len
2 | Searches | 34896 | 34896 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 32.335 | 32.335 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2014-03-11 | 2014-03-11 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 26.3 | 26.3 |
| Avg. Seconds | 0.291 | 0.291 |
|---|
| Avg. Words Per Sec | 614218.1 | 614218.1 |
|---|
Len
3 | Searches | 263497 | 194832 | 68665 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 244.159 | 180.533 | 63.626 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2014-03-11 | 2014-03-11 | 2014-03-11 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 127.0 | 24.2 | 418.5 |
| Avg. Seconds | 0.641 | 0.478 | 1.102 |
|---|
| Avg. Words Per Sec | 278800.3 | 373467.5 | 162165.2 |
|---|
Len
4 | Searches | 658806 | 528671 | 106559 | 23576 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 610.456 | 489.871 | 98.739 | 21.846 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 106.0 | 16.7 | 274.1 | 1348.6 |
| Avg. Seconds | 1.156 | 0.797 | 2.071 | 5.076 |
|---|
| Avg. Words Per Sec | 154587.2 | 224302.1 | 86275.6 | 35206.4 |
|---|
Len
5 | Searches | 1148997 | 855670 | 140280 | 152960 | 87 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1064.671 | 792.872 | 129.985 | 141.734 | 0.081 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-02-28 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 147.2 | 10.2 | 143.7 | 916.8 | 405.0 |
| Avg. Seconds | 2.546 | 1.332 | 3.409 | 8.537 | 13.079 |
|---|
| Avg. Words Per Sec | 70188.1 | 134104.0 | 52422.6 | 20932.5 | 13662.5 |
|---|
Len
6 | Searches | 1322597 | 971465 | 136016 | 206806 | 8310 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1225.530 | 900.168 | 126.034 | 191.628 | 7.700 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 116.4 | 7.1 | 85.4 | 598.7 | 1392.0 | |
| Avg. Seconds | 4.126 | 2.034 | 4.774 | 12.847 | 21.051 | |
|---|
| Avg. Words Per Sec | 43305.8 | 87836.7 | 37426.9 | 13909.7 | 8488.6 | |
|---|
Len
7 | Searches | 1299738 | 897953 | 128645 | 207501 | 65396 | 243 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1204.349 | 832.051 | 119.204 | 192.272 | 60.597 | 0.225 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-07 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 110.3 | 5.6 | 48.2 | 359.6 | 876.6 | 1048.7 | |
| Avg. Seconds | 6.387 | 2.707 | 5.794 | 15.940 | 27.679 | 31.484 | |
|---|
| Avg. Words Per Sec | 27977.3 | 66013.0 | 30841.7 | 11209.9 | 6455.8 | 5675.5 | |
|---|
Len
8 | Searches | 1030057 | 633651 | 106396 | 152274 | 132924 | 4765 | 47 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 954.460 | 587.147 | 98.587 | 141.098 | 123.169 | 4.415 | 0.044 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-10 | 2013-12-21 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 106.4 | 3.8 | 20.1 | 181.1 | 541.9 | 1140.8 | 1533.0 | |
| Avg. Seconds | 9.221 | 3.037 | 6.110 | 16.515 | 31.714 | 40.102 | 43.521 | |
|---|
| Avg. Words Per Sec | 19378.9 | 58831.9 | 29247.6 | 10820.0 | 5634.5 | 4455.9 | 4105.9 | |
|---|
Len
9 | Searches | 768095 | 399205 | 93039 | 100840 | 133782 | 41207 | 22 | | | N/A | N/A |
|---|
| Avg. Per Day | 711.724 | 369.907 | 86.211 | 93.439 | 123.964 | 38.183 | 0.020 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2013-10-05 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 108.6 | 2.7 | 9.3 | 67.4 | 338.8 | 711.4 | 298.5 | | |
| Avg. Seconds | 11.872 | 2.788 | 5.919 | 14.415 | 31.201 | 44.310 | 60.369 | | |
|---|
| Avg. Words Per Sec | 15051.8 | 64090.3 | 30189.6 | 12396.3 | 5727.1 | 4032.7 | 2960.0 | | |
|---|
Len
10 | Searches | 515352 | 244501 | 68099 | 61063 | 73745 | 65440 | 2443 | 48 | 13 | | N/A |
|---|
| Avg. Per Day | 477.530 | 226.557 | 63.101 | 56.582 | 68.333 | 60.637 | 2.264 | 0.044 | 0.012 | |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-01-26 | 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.2 | 2.3 | 6.2 | 30.8 | 170.0 | 479.0 | 926.8 | 21.0 | 2710.5 | |
| Avg. Seconds | 12.348 | 2.251 | 5.076 | 11.455 | 25.336 | 42.433 | 48.919 | 57.821 | 41.984 | |
|---|
| Avg. Words Per Sec | 14471.5 | 79395.0 | 35205.3 | 15599.6 | 7052.8 | 4211.1 | 3652.8 | 3090.4 | 4256.2 | |
|---|
Len
11 | Searches | 367780 | 164068 | 50572 | 42286 | 42969 | 52312 | 15216 | 357 | | | |
|---|
| Avg. Per Day | 340.788 | 152.027 | 46.860 | 39.183 | 39.815 | 48.473 | 14.099 | 0.331 | | | |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-02-24 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 79.0 | 2.1 | 4.9 | 20.8 | 85.1 | 285.6 | 563.6 | 1157.6 | | | |
| Avg. Seconds | 11.787 | 1.643 | 4.043 | 9.024 | 19.317 | 35.974 | 49.105 | 56.868 | | | |
|---|
| Avg. Words Per Sec | 15160.0 | 108735.7 | 44199.2 | 19802.2 | 9250.6 | 4967.2 | 3639.0 | 3142.2 | | | |
|---|
Len
12 | Searches | 268008 | 118971 | 36579 | 29594 | 27754 | 29224 | 22603 | 3275 | 8 | | |
|---|
| Avg. Per Day | 248.339 | 110.240 | 33.894 | 27.422 | 25.717 | 27.079 | 20.944 | 3.035 | 0.007 | | |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 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 | 60.5 | 1.9 | 3.9 | 12.0 | 48.7 | 145.3 | 339.6 | 673.6 | 946.0 | | |
| Avg. Seconds | 10.136 | 1.183 | 2.948 | 6.650 | 13.783 | 26.363 | 41.659 | 53.604 | 132.110 | | |
|---|
| Avg. Words Per Sec | 17628.6 | 150991.5 | 60608.8 | 26870.9 | 12964.3 | 6778.0 | 4289.4 | 3333.6 | 1352.6 | | |
|---|
Len
13 | Searches | 203147 | 89448 | 26702 | 21633 | 19705 | 19356 | 17786 | 8206 | 311 | | |
|---|
| Avg. Per Day | 188.238 | 82.883 | 24.742 | 20.045 | 18.259 | 17.935 | 16.481 | 7.604 | 0.288 | | |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-06 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 47.3 | 1.8 | 3.3 | 8.7 | 28.6 | 95.1 | 188.4 | 389.1 | 689.7 | | |
| Avg. Seconds | 8.475 | 0.838 | 2.078 | 4.579 | 9.578 | 18.422 | 32.166 | 43.750 | 50.790 | | |
|---|
| Avg. Words Per Sec | 21084.3 | 213303.1 | 86007.7 | 39025.4 | 18657.1 | 9699.9 | 5555.2 | 4084.3 | 3518.3 | | |
|---|
Len
14 | Searches | 156430 | 67350 | 19616 | 15515 | 13020 | 13268 | 13855 | 10857 | 2906 | 43 | |
|---|
| Avg. Per Day | 144.949 | 62.407 | 18.176 | 14.376 | 12.064 | 12.294 | 12.838 | 10.060 | 2.693 | 0.040 | |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-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 | 46.3 | 1.8 | 2.9 | 6.4 | 18.5 | 64.9 | 134.4 | 239.1 | 476.6 | 568.2 | |
| Avg. Seconds | 7.727 | 0.583 | 1.331 | 2.954 | 6.173 | 12.554 | 22.654 | 35.884 | 49.906 | 48.488 | |
|---|
| Avg. Words Per Sec | 23124.9 | 306338.5 | 134212.9 | 60483.7 | 28946.8 | 14233.4 | 7887.7 | 4979.7 | 3580.5 | 3685.3 | |
|---|
Len
15 | Searches | 123046 | 50357 | 14073 | 11672 | 10332 | 9371 | 9972 | 10260 | 5830 | 1179 | |
|---|
| Avg. Per Day | 114.016 | 46.661 | 13.040 | 10.815 | 9.574 | 8.683 | 9.240 | 9.507 | 5.402 | 1.092 | |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-06 | |
| 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 | 44.2 | 1.5 | 2.2 | 4.4 | 11.7 | 35.4 | 85.3 | 159.9 | 277.8 | 603.4 | |
| Avg. Seconds | 6.961 | 0.348 | 0.779 | 1.781 | 3.802 | 7.597 | 14.497 | 25.713 | 38.958 | 51.990 | |
|---|
| Avg. Words Per Sec | 25669.3 | 513516.0 | 229270.0 | 100333.7 | 47003.5 | 23519.9 | 12325.8 | 6949.4 | 4586.8 | 3437.0 | |
|---|
Len
16 | Searches | 96717 | 31613 | 11899 | 9170 | 7911 | 8117 | 9005 | 9496 | 6895 | 2578 | 33 |
|---|
| Avg. Per Day | 89.619 | 29.293 | 11.026 | 8.497 | 7.330 | 7.521 | 8.344 | 8.799 | 6.389 | 2.389 | 0.031 |
| 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 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-11 | 2014-03-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 | 44.4 | 1.0 | 1.5 | 2.4 | 4.9 | 15.5 | 53.4 | 125.2 | 205.9 | 371.2 | 527.2 |
| Avg. Seconds | 6.460 | 0.170 | 0.381 | 0.909 | 2.109 | 4.176 | 8.703 | 16.782 | 29.414 | 44.129 | 51.313 |
|---|
| Avg. Words Per Sec | 27660.0 | 1052338.5 | 469434.7 | 196538.2 | 84718.5 | 42792.8 | 20531.9 | 10647.7 | 6075.0 | 4049.3 | 3482.4 |
|---|