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 | 38318233 | 20650256 | 4392298 | 6583967 | 3855437 | 1758486 | 686185 | 278052 | 92047 | 20314 | 1191 |
|---|
| Avg. Per Day | 16370.055 | 8822.062 | 1876.448 | 2812.758 | 1647.094 | 751.248 | 293.147 | 118.787 | 39.324 | 8.678 | 0.509 |
| 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-20 | 2017-08-23 |
| 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 | 159.4 | 7.0 | 109.5 | 424.9 | 431.3 | 390.0 | 314.2 | 292.7 | 315.3 | 418.0 | 592.9 |
| Avg. Seconds | 7.673 | 1.700 | 4.213 | 11.263 | 21.604 | 28.157 | 29.741 | 31.425 | 36.012 | 45.323 | 55.721 |
|---|
| Avg. Words Per Sec | 23288.0 | 105102.5 | 42419.1 | 15865.4 | 8271.2 | 6346.3 | 6008.3 | 5686.2 | 4962.0 | 3942.6 | 3206.9 |
|---|
Len
2 | Searches | 152159 | 152159 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 65.004 | 65.004 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2017-08-23 | 2017-08-23 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 27.4 | 27.4 |
| Avg. Seconds | 0.169 | 0.169 |
|---|
| Avg. Words Per Sec | 1058868.4 | 1058868.4 |
|---|
Len
3 | Searches | 1071653 | 738535 | 333118 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 457.824 | 315.512 | 142.312 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2017-08-23 | 2017-08-23 | 2017-08-23 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 148.4 | 25.8 | 420.4 |
| Avg. Seconds | 0.514 | 0.311 | 0.963 |
|---|
| Avg. Words Per Sec | 347878.9 | 574530.5 | 185573.2 |
|---|
Len
4 | Searches | 2350609 | 1703147 | 498326 | 149136 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1004.211 | 727.607 | 212.891 | 63.713 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 158.4 | 18.1 | 280.8 | 1351.8 |
| Avg. Seconds | 1.125 | 0.600 | 1.844 | 4.707 |
|---|
| Avg. Words Per Sec | 158878.5 | 297597.3 | 96890.1 | 37959.6 |
|---|
Len
5 | Searches | 4230840 | 2597258 | 659311 | 973015 | 1256 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1807.471 | 1109.583 | 281.666 | 415.685 | 0.537 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 242.9 | 11.0 | 145.8 | 926.3 | 1224.5 |
| Avg. Seconds | 2.908 | 1.065 | 3.147 | 7.654 | 11.566 |
|---|
| Avg. Words Per Sec | 61453.7 | 167859.0 | 56773.2 | 23345.6 | 15449.5 |
|---|
Len
6 | Searches | 5176381 | 3092251 | 621643 | 1400503 | 61984 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2211.418 | 1321.050 | 265.574 | 598.313 | 26.480 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 197.0 | 7.2 | 91.9 | 611.1 | 1367.1 | |
| Avg. Seconds | 4.712 | 1.676 | 4.707 | 10.824 | 18.123 | |
|---|
| Avg. Words Per Sec | 37922.6 | 106617.2 | 37959.3 | 16509.1 | 9860.1 | |
|---|
Len
7 | Searches | 5896620 | 3289755 | 581334 | 1506216 | 518343 | 972 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2519.114 | 1405.427 | 248.354 | 643.475 | 221.443 | 0.415 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 184.4 | 5.3 | 53.9 | 381.1 | 894.8 | 1030.6 | |
| Avg. Seconds | 7.215 | 2.286 | 6.089 | 13.231 | 22.240 | 29.083 | |
|---|
| Avg. Words Per Sec | 24765.1 | 78158.8 | 29344.8 | 13505.2 | 8034.5 | 6144.2 | |
|---|
Len
8 | Searches | 5401364 | 2828438 | 456210 | 1044279 | 1034517 | 37626 | 294 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 2307.534 | 1208.346 | 194.899 | 446.130 | 441.959 | 16.074 | 0.126 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-20 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 159.0 | 3.4 | 19.9 | 189.1 | 578.5 | 1157.4 | 1673.8 | |
| Avg. Seconds | 9.653 | 2.629 | 6.749 | 14.353 | 24.519 | 33.479 | 41.864 | |
|---|
| Avg. Words Per Sec | 18511.5 | 67975.8 | 26478.1 | 12449.8 | 7288.0 | 5337.4 | 4268.4 | |
|---|
Len
9 | Searches | 4363166 | 2109275 | 376728 | 616053 | 976966 | 283841 | 303 | | | N/A | N/A |
|---|
| Avg. Per Day | 1864.002 | 901.110 | 160.943 | 263.186 | 417.373 | 121.261 | 0.129 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-05 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 144.1 | 2.6 | 9.3 | 77.5 | 371.1 | 737.4 | 1305.2 | | |
| Avg. Seconds | 11.537 | 2.504 | 6.551 | 13.793 | 24.452 | 35.889 | 43.918 | | |
|---|
| Avg. Words Per Sec | 15488.7 | 71350.0 | 27276.0 | 12955.0 | 7307.7 | 4979.0 | 4068.7 | | |
|---|
Len
10 | Searches | 3004514 | 1388663 | 270024 | 320261 | 544551 | 462768 | 18175 | 59 | 13 | | N/A |
|---|
| Avg. Per Day | 1283.568 | 593.255 | 115.358 | 136.820 | 232.639 | 197.701 | 7.765 | 0.025 | 0.006 | |
| 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 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 | 123.2 | 2.2 | 5.9 | 36.4 | 192.0 | 503.4 | 893.4 | 21.0 | 2710.5 | |
| Avg. Seconds | 12.219 | 2.051 | 5.558 | 11.813 | 21.940 | 34.170 | 44.920 | 54.461 | 41.984 | |
|---|
| Avg. Words Per Sec | 14623.7 | 87106.8 | 32152.1 | 15126.6 | 8144.4 | 5229.5 | 3978.0 | 3281.1 | 4256.2 | |
|---|
Len
11 | Searches | 2127700 | 936095 | 195040 | 201509 | 286827 | 402243 | 104504 | 1482 | | | |
|---|
| Avg. Per Day | 908.982 | 399.912 | 83.324 | 86.087 | 122.536 | 171.844 | 44.645 | 0.633 | | | |
| 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-21 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 106.8 | 2.0 | 4.7 | 23.5 | 102.6 | 317.1 | 585.4 | 1059.4 | | | |
| Avg. Seconds | 12.220 | 1.528 | 4.349 | 9.495 | 18.219 | 29.962 | 42.594 | 54.220 | | | |
|---|
| Avg. Words Per Sec | 14622.5 | 116937.8 | 41092.3 | 18819.3 | 9807.8 | 5964.0 | 4195.2 | 3295.7 | | | |
|---|
Len
12 | Searches | 1541756 | 649596 | 142162 | 135602 | 175334 | 231800 | 190240 | 16980 | 42 | | |
|---|
| Avg. Per Day | 658.659 | 277.516 | 60.733 | 57.931 | 74.905 | 99.028 | 81.273 | 7.254 | 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 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 | 90.6 | 1.8 | 3.7 | 12.2 | 65.4 | 168.7 | 388.5 | 690.1 | 1117.5 | | |
| Avg. Seconds | 11.638 | 1.111 | 3.148 | 7.044 | 13.832 | 24.134 | 36.488 | 50.321 | 81.720 | | |
|---|
| Avg. Words Per Sec | 15354.2 | 160881.3 | 56768.4 | 25368.9 | 12918.8 | 7404.3 | 4897.3 | 3551.0 | 2186.6 | | |
|---|
Len
13 | Searches | 1127695 | 461191 | 101583 | 94908 | 110576 | 154873 | 146592 | 56368 | 1604 | | |
|---|
| Avg. Per Day | 481.766 | 197.027 | 43.398 | 40.546 | 47.240 | 66.164 | 62.626 | 24.081 | 0.685 | | |
| 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-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 | 76.0 | 1.7 | 3.3 | 8.8 | 37.3 | 133.4 | 228.7 | 430.3 | 744.7 | | |
| Avg. Seconds | 10.404 | 0.791 | 2.207 | 4.855 | 9.808 | 18.004 | 29.463 | 42.617 | 55.005 | | |
|---|
| Avg. Words Per Sec | 17175.7 | 225899.5 | 80974.4 | 36803.1 | 18219.5 | 9924.9 | 6064.9 | 4192.9 | 3248.7 | | |
|---|
Len
14 | Searches | 826398 | 324693 | 70341 | 64263 | 67361 | 95527 | 107121 | 82081 | 14968 | 43 | |
|---|
| Avg. Per Day | 353.048 | 138.713 | 30.051 | 27.454 | 28.778 | 40.810 | 45.764 | 35.066 | 6.395 | 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-21 | 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 | 72.7 | 1.7 | 2.8 | 6.5 | 21.8 | 88.7 | 167.1 | 285.4 | 509.0 | 568.2 | |
| Avg. Seconds | 9.643 | 0.552 | 1.410 | 3.143 | 6.456 | 12.500 | 21.814 | 34.001 | 48.766 | 48.488 | |
|---|
| Avg. Words Per Sec | 18530.6 | 323515.6 | 126740.1 | 56860.7 | 27678.5 | 14294.7 | 8191.7 | 5255.5 | 3664.2 | 3685.3 | |
|---|
Len
15 | Searches | 596482 | 228590 | 48343 | 44524 | 45622 | 55257 | 73186 | 63738 | 33284 | 3938 | |
|---|
| Avg. Per Day | 254.825 | 97.657 | 20.653 | 19.021 | 19.490 | 23.607 | 31.266 | 27.230 | 14.219 | 1.682 | |
| 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-20 | |
| 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.5 | 1.4 | 2.2 | 4.5 | 13.0 | 50.8 | 124.8 | 190.6 | 326.1 | 585.6 | |
| Avg. Seconds | 8.418 | 0.329 | 0.818 | 1.885 | 3.997 | 7.987 | 14.928 | 25.381 | 38.439 | 53.157 | |
|---|
| Avg. Words Per Sec | 21227.1 | 543682.9 | 218492.7 | 94810.7 | 44711.1 | 22372.2 | 11970.2 | 7040.5 | 4648.7 | 3361.6 | |
|---|
Len
16 | Searches | 450896 | 150610 | 38135 | 33698 | 32100 | 33579 | 45770 | 57344 | 42136 | 16333 | 1191 |
|---|
| Avg. Per Day | 192.629 | 64.343 | 16.292 | 14.396 | 13.714 | 14.345 | 19.554 | 24.498 | 18.001 | 6.978 | 0.509 |
| 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-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-23 | 2017-08-20 | 2017-08-23 |
| 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.4 | 1.0 | 1.5 | 2.4 | 4.9 | 14.8 | 61.5 | 144.0 | 220.0 | 377.1 | 592.9 |
| Avg. Seconds | 8.214 | 0.161 | 0.401 | 0.984 | 2.249 | 4.646 | 9.279 | 17.250 | 28.793 | 43.426 | 55.721 |
|---|
| Avg. Words Per Sec | 21754.2 | 1107499.5 | 445118.4 | 181677.1 | 79456.7 | 38462.4 | 19256.7 | 10359.2 | 6206.0 | 4114.8 | 3206.9 |
|---|