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 | 46318269 | 25662324 | 4692442 | 7927921 | 4848973 | 1998428 | 761028 | 303154 | 100695 | 21860 | 1444 |
|---|
| Avg. Per Day | 16225.083 | 8989.397 | 1643.741 | 2777.115 | 1698.574 | 700.040 | 266.585 | 106.193 | 35.273 | 7.657 | 0.506 |
| 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 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-18 |
| 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 | 171.1 | 7.1 | 116.3 | 465.3 | 478.8 | 404.7 | 320.1 | 296.3 | 316.1 | 419.4 | 595.9 |
| Avg. Seconds | 7.170 | 1.590 | 4.089 | 10.563 | 20.279 | 27.180 | 28.782 | 30.546 | 34.829 | 44.245 | 52.829 |
|---|
| Avg. Words Per Sec | 24922.2 | 112386.0 | 43702.2 | 16916.2 | 8811.4 | 6574.5 | 6208.4 | 5849.8 | 5130.6 | 4038.7 | 3382.4 |
|---|
Len
2 | Searches | 181073 | 181073 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 63.429 | 63.429 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2019-01-19 | 2019-01-19 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.5 | 27.5 |
| Avg. Seconds | 0.157 | 0.157 |
|---|
| Avg. Words Per Sec | 1141586.0 | 1141586.0 |
|---|
Len
3 | Searches | 1284901 | 892738 | 392163 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 450.095 | 312.722 | 137.373 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2019-01-19 | 2019-01-19 | 2019-01-19 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 147.4 | 25.7 | 424.4 |
| Avg. Seconds | 0.479 | 0.287 | 0.916 |
|---|
| Avg. Words Per Sec | 372928.9 | 621832.3 | 195127.8 |
|---|
Len
4 | Searches | 2922699 | 2148650 | 560278 | 213771 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1023.808 | 752.662 | 196.263 | 74.883 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 168.0 | 18.1 | 289.4 | 1356.3 |
| Avg. Seconds | 1.059 | 0.550 | 1.778 | 4.284 |
|---|
| Avg. Words Per Sec | 168812.1 | 324842.7 | 100526.3 | 41707.8 |
|---|
Len
5 | Searches | 5377330 | 3371662 | 706448 | 1295798 | 3422 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1883.655 | 1181.078 | 247.466 | 453.912 | 1.199 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 252.6 | 11.0 | 150.1 | 933.8 | 1457.5 |
| Avg. Seconds | 2.713 | 0.972 | 3.077 | 7.028 | 9.861 |
|---|
| Avg. Words Per Sec | 65854.0 | 183867.5 | 58074.9 | 25425.4 | 18120.1 |
|---|
Len
6 | Searches | 6592494 | 4020989 | 660338 | 1814780 | 96387 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2309.321 | 1408.534 | 231.313 | 635.709 | 33.764 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 206.2 | 7.1 | 92.8 | 626.9 | 1369.5 | |
| Avg. Seconds | 4.381 | 1.534 | 4.611 | 9.987 | 16.036 | |
|---|
| Avg. Words Per Sec | 40787.3 | 116505.6 | 38754.4 | 17892.2 | 11143.0 | |
|---|
Len
7 | Searches | 7434634 | 4227567 | 612178 | 1833754 | 759951 | 1184 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2604.319 | 1480.898 | 214.443 | 642.356 | 266.207 | 0.415 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-15 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 200.1 | 5.2 | 53.8 | 403.2 | 910.1 | 1035.3 | |
| Avg. Seconds | 6.790 | 2.109 | 5.984 | 12.431 | 19.837 | 27.236 | |
|---|
| Avg. Words Per Sec | 26315.3 | 84710.3 | 29862.3 | 14374.4 | 9008.0 | 6560.9 | |
|---|
Len
8 | Searches | 6635587 | 3515978 | 478967 | 1191436 | 1402094 | 46797 | 315 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2324.416 | 1231.631 | 167.780 | 417.355 | 491.147 | 16.393 | 0.110 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2018-08-16 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 174.0 | 3.4 | 19.9 | 201.2 | 597.9 | 1167.2 | 1664.4 | |
| Avg. Seconds | 9.184 | 2.461 | 6.639 | 13.741 | 22.290 | 31.412 | 41.385 | |
|---|
| Avg. Words Per Sec | 19457.1 | 72607.6 | 26913.6 | 13004.7 | 8016.5 | 5688.6 | 4317.8 | |
|---|
Len
9 | Searches | 5097004 | 2519303 | 390844 | 651407 | 1209518 | 325611 | 321 | | | N/A | N/A |
|---|
| Avg. Per Day | 1785.458 | 882.501 | 136.911 | 228.185 | 423.689 | 114.060 | 0.112 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2018-12-16 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 152.8 | 2.6 | 9.3 | 79.7 | 392.1 | 742.7 | 1315.2 | | |
| Avg. Seconds | 11.004 | 2.381 | 6.473 | 13.528 | 22.798 | 34.272 | 43.199 | | |
|---|
| Avg. Words Per Sec | 16238.2 | 75036.5 | 27606.9 | 13208.9 | 7838.0 | 5213.9 | 4136.4 | | |
|---|
Len
10 | Searches | 3425743 | 1627220 | 278562 | 332829 | 626398 | 540460 | 20202 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1200.022 | 570.008 | 97.579 | 116.589 | 219.424 | 189.321 | 7.077 | 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 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 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 | 128.9 | 2.2 | 5.9 | 36.8 | 206.4 | 511.4 | 898.8 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.721 | 1.970 | 5.503 | 11.665 | 20.931 | 32.457 | 43.377 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 15245.3 | 90726.0 | 32471.0 | 15318.3 | 8537.2 | 5505.4 | 4119.5 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2379051 | 1085609 | 200233 | 208445 | 302363 | 468046 | 112750 | 1605 | | | |
|---|
| Avg. Per Day | 833.371 | 380.284 | 70.141 | 73.017 | 105.916 | 163.954 | 39.496 | 0.562 | | | |
| 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 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 110.3 | 2.0 | 4.7 | 23.7 | 106.2 | 329.5 | 588.7 | 1058.9 | | | |
| Avg. Seconds | 11.745 | 1.476 | 4.316 | 9.400 | 17.912 | 28.506 | 41.457 | 52.861 | | | |
|---|
| Avg. Words Per Sec | 15213.6 | 121092.8 | 41401.5 | 19008.8 | 9975.8 | 6268.5 | 4310.3 | 3380.4 | | | |
|---|
Len
12 | Searches | 1703391 | 749146 | 146015 | 139868 | 183262 | 255276 | 211406 | 18376 | 42 | | |
|---|
| Avg. Per Day | 596.690 | 262.422 | 51.148 | 48.995 | 64.196 | 89.422 | 74.055 | 6.437 | 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 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 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 | 92.2 | 1.8 | 3.7 | 12.2 | 67.5 | 175.0 | 395.2 | 696.6 | 1117.5 | | |
| Avg. Seconds | 11.191 | 1.074 | 3.125 | 6.985 | 13.651 | 23.396 | 35.235 | 48.916 | 81.720 | | |
|---|
| Avg. Words Per Sec | 15966.9 | 166351.2 | 57178.9 | 25582.8 | 13090.1 | 7637.7 | 5071.4 | 3653.0 | 2186.6 | | |
|---|
Len
13 | Searches | 1243218 | 528830 | 104493 | 97971 | 114679 | 166882 | 169058 | 59595 | 1710 | | |
|---|
| Avg. Per Day | 435.494 | 185.247 | 36.603 | 34.319 | 40.172 | 58.458 | 59.220 | 20.876 | 0.599 | | |
| 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 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 78.1 | 1.7 | 3.3 | 8.8 | 37.4 | 139.1 | 238.8 | 432.6 | 743.4 | | |
| Avg. Seconds | 10.057 | 0.765 | 2.191 | 4.817 | 9.710 | 17.597 | 28.176 | 41.828 | 53.734 | | |
|---|
| Avg. Words Per Sec | 17767.1 | 233599.5 | 81558.3 | 37095.4 | 18402.8 | 10154.8 | 6342.0 | 4272.0 | 3325.5 | | |
|---|
Len
14 | Searches | 904685 | 368859 | 72441 | 66569 | 70056 | 101235 | 118495 | 91178 | 15809 | 43 | |
|---|
| Avg. Per Day | 316.907 | 129.210 | 25.376 | 23.319 | 24.540 | 35.462 | 41.508 | 31.939 | 5.538 | 0.015 | |
| 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 | 2019-01-19 | 2019-01-19 | 2019-01-13 | 2019-01-18 | 2019-01-18 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-16 | 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.4 | 1.7 | 2.8 | 6.5 | 21.7 | 91.1 | 174.3 | 291.1 | 512.6 | 568.2 | |
| Avg. Seconds | 9.357 | 0.535 | 1.400 | 3.117 | 6.393 | 12.291 | 21.153 | 32.924 | 47.847 | 48.488 | |
|---|
| Avg. Words Per Sec | 19096.4 | 334102.9 | 127627.2 | 57319.2 | 27951.9 | 14538.1 | 8447.4 | 5427.3 | 3734.6 | 3685.3 | |
|---|
Len
15 | Searches | 650084 | 256949 | 50040 | 46267 | 47440 | 57918 | 80673 | 71079 | 35497 | 4221 | |
|---|
| Avg. Per Day | 227.722 | 90.008 | 17.529 | 16.207 | 16.618 | 20.288 | 28.259 | 24.899 | 12.434 | 1.479 | |
| 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 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-18 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-18 | |
| 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 | 66.3 | 1.4 | 2.2 | 4.5 | 13.0 | 51.0 | 131.5 | 197.6 | 328.9 | 592.0 | |
| Avg. Seconds | 8.190 | 0.318 | 0.811 | 1.868 | 3.959 | 7.885 | 14.501 | 24.541 | 37.679 | 51.979 | |
|---|
| Avg. Words Per Sec | 21817.1 | 561116.4 | 220249.2 | 95652.6 | 45133.2 | 22662.0 | 12322.8 | 7281.2 | 4742.5 | 3437.8 | |
|---|
Len
16 | Searches | 486375 | 167751 | 39442 | 35026 | 33403 | 35019 | 47808 | 61262 | 47624 | 17596 | 1444 |
|---|
| Avg. Per Day | 170.375 | 58.762 | 13.816 | 12.269 | 11.701 | 12.267 | 16.747 | 21.460 | 16.682 | 6.164 | 0.506 |
| 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 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-19 | 2019-01-18 |
| 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.9 | 1.0 | 1.5 | 2.4 | 4.9 | 14.9 | 61.6 | 146.1 | 224.6 | 377.6 | 595.9 |
| Avg. Seconds | 8.065 | 0.156 | 0.397 | 0.973 | 2.224 | 4.594 | 9.159 | 16.882 | 27.661 | 42.380 | 52.829 |
|---|
| Avg. Words Per Sec | 22156.7 | 1143170.9 | 449547.9 | 183631.0 | 80336.2 | 38900.8 | 19509.6 | 10585.0 | 6460.0 | 4216.4 | 3382.4 |
|---|