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 | 4475760 | 3465080 | 432935 | 319960 | 134086 | 65736 | 31310 | 17814 | 7054 | 1766 | 19 |
|---|
| Avg. Per Day | 5715.459 | 4424.841 | 552.850 | 408.583 | 171.225 | 83.944 | 39.982 | 22.748 | 9.008 | 2.255 | 0.024 |
| 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 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-18 | 2013-03-10 |
| 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 | 64.5 | 8.1 | 92.2 | 419.3 | 364.4 | 342.0 | 249.7 | 258.3 | 284.0 | 442.4 | 533.6 |
| Avg. Seconds | 3.474 | 1.840 | 3.373 | 8.713 | 16.556 | 21.575 | 20.392 | 22.526 | 26.392 | 34.158 | 38.477 |
|---|
| Avg. Words Per Sec | 51434.5 | 97108.2 | 52978.6 | 20507.9 | 10793.1 | 8282.4 | 8762.8 | 7932.7 | 6770.6 | 5231.4 | 4644.1 |
|---|
Len
2 | Searches | 21497 | 21497 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 27.451 | 27.451 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2013-05-19 | 2013-05-19 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 25.2 | 25.2 |
| Avg. Seconds | 0.370 | 0.370 |
|---|
| Avg. Words Per Sec | 483085.4 | 483085.4 |
|---|
Len
3 | Searches | 158632 | 131266 | 27366 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 202.570 | 167.624 | 34.946 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 90.9 | 23.7 | 413.3 |
| Avg. Seconds | 0.640 | 0.560 | 1.024 |
|---|
| Avg. Words Per Sec | 279285.0 | 319232.5 | 174527.1 |
|---|
Len
4 | Searches | 441694 | 390288 | 40828 | 10578 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 564.035 | 498.390 | 52.137 | 13.508 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 70.2 | 16.1 | 257.3 | 1341.9 |
| Avg. Seconds | 1.029 | 0.855 | 1.804 | 4.461 |
|---|
| Avg. Words Per Sec | 173698.5 | 209085.8 | 99053.1 | 40059.9 |
|---|
Len
5 | Searches | 774071 | 656509 | 58777 | 58738 | 47 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 988.474 | 838.349 | 75.057 | 75.007 | 0.060 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-18 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 88.6 | 10.1 | 138.7 | 915.9 | 405.0 |
| Avg. Seconds | 1.923 | 1.379 | 2.814 | 7.107 | 10.945 |
|---|
| Avg. Words Per Sec | 92911.9 | 129583.3 | 63490.2 | 25141.6 | 16325.7 |
|---|
Len
6 | Searches | 856287 | 727505 | 63330 | 61896 | 3556 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1093.462 | 929.010 | 80.871 | 79.040 | 4.541 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 61.9 | 7.2 | 80.3 | 609.8 | 1409.3 | |
| Avg. Seconds | 2.833 | 2.060 | 3.814 | 10.114 | 16.641 | |
|---|
| Avg. Words Per Sec | 63083.5 | 86735.2 | 46847.9 | 17668.4 | 10737.7 | |
|---|
Len
7 | Searches | 749028 | 616436 | 62020 | 51086 | 19347 | 139 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 956.494 | 787.177 | 79.198 | 65.236 | 24.706 | 0.178 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 57.7 | 5.9 | 48.0 | 371.3 | 901.7 | 1202.4 | |
| Avg. Seconds | 3.938 | 2.677 | 4.666 | 11.793 | 20.866 | 28.921 | |
|---|
| Avg. Words Per Sec | 45376.7 | 66755.3 | 38294.7 | 15152.9 | 8563.6 | 6178.6 | |
|---|
Len
8 | Searches | 477145 | 360172 | 49127 | 38456 | 27484 | 1906 | | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 609.305 | 459.933 | 62.734 | 49.108 | 35.097 | 2.434 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | | |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-18 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 54.7 | 4.2 | 22.6 | 172.0 | 533.0 | 1166.5 | | |
| Avg. Seconds | 5.025 | 2.847 | 4.919 | 11.441 | 23.038 | 30.112 | | |
|---|
| Avg. Words Per Sec | 35559.1 | 62757.3 | 36323.8 | 15618.9 | 7756.3 | 5934.3 | | |
|---|
Len
9 | Searches | 294506 | 185208 | 39418 | 30373 | 27445 | 12062 | | | | N/A | N/A |
|---|
| Avg. Per Day | 376.078 | 236.507 | 50.336 | 38.786 | 35.047 | 15.403 | | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | | | |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 67.0 | 2.9 | 9.8 | 58.7 | 296.9 | 736.2 | | | |
| Avg. Seconds | 6.365 | 2.399 | 4.688 | 9.710 | 20.186 | 32.859 | | | |
|---|
| Avg. Words Per Sec | 28075.5 | 74474.2 | 38117.5 | 18402.5 | 8852.4 | 5438.2 | | | |
|---|
Len
10 | Searches | 188073 | 106461 | 27412 | 20387 | 17393 | 15298 | 1090 | 19 | 13 | | N/A |
|---|
| Avg. Per Day | 240.166 | 135.949 | 35.005 | 26.034 | 22.211 | 19.535 | 1.392 | 0.024 | 0.017 | |
| 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 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-04-28 | 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 | 60.9 | 2.4 | 6.2 | 26.5 | 131.7 | 463.3 | 991.1 | 21.0 | 2710.5 | |
| Avg. Seconds | 6.588 | 1.866 | 4.028 | 8.109 | 15.599 | 29.393 | 39.084 | 32.063 | 41.984 | |
|---|
| Avg. Words Per Sec | 27122.2 | 95773.2 | 44359.1 | 22037.4 | 11455.2 | 6079.3 | 4572.0 | 5573.1 | 4256.2 | |
|---|
Len
11 | Searches | 138966 | 77056 | 19541 | 14383 | 11696 | 11205 | 4919 | 166 | | | |
|---|
| Avg. Per Day | 177.457 | 98.399 | 24.953 | 18.367 | 14.936 | 14.309 | 6.281 | 0.212 | | | |
| 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 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-12 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 49.1 | 2.2 | 4.9 | 18.0 | 62.8 | 230.9 | 567.1 | 1171.7 | | | |
| Avg. Seconds | 5.955 | 1.395 | 3.147 | 6.480 | 12.095 | 22.557 | 33.441 | 40.540 | | | |
|---|
| Avg. Words Per Sec | 30004.6 | 128137.1 | 56772.7 | 27576.0 | 14773.8 | 7921.7 | 5343.5 | 4407.8 | | | |
|---|
Len
12 | Searches | 107581 | 59764 | 13818 | 10379 | 8265 | 7302 | 6631 | 1422 | | | |
|---|
| Avg. Per Day | 137.379 | 76.317 | 17.645 | 13.254 | 10.554 | 9.325 | 8.468 | 1.816 | | | |
| 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 | | | |
| Latest | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-18 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 40.9 | 2.0 | 3.8 | 10.8 | 35.1 | 111.8 | 304.7 | 694.6 | | | |
| Avg. Seconds | 5.325 | 1.038 | 2.311 | 4.866 | 9.159 | 16.290 | 27.069 | 38.141 | | | |
|---|
| Avg. Words Per Sec | 33559.3 | 172205.1 | 77323.1 | 36723.4 | 19510.6 | 10969.3 | 6601.2 | 4685.1 | | | |
|---|
Len
13 | Searches | 88487 | 48435 | 10764 | 8106 | 6512 | 5693 | 5405 | 3410 | 162 | | |
|---|
| Avg. Per Day | 112.996 | 61.851 | 13.745 | 10.351 | 8.316 | 7.270 | 6.902 | 4.355 | 0.207 | | |
| 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 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-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 | 33.9 | 1.9 | 3.3 | 8.0 | 22.1 | 64.8 | 161.6 | 387.3 | 647.0 | | |
| Avg. Seconds | 4.705 | 0.752 | 1.622 | 3.368 | 6.615 | 11.952 | 20.212 | 31.735 | 40.304 | | |
|---|
| Avg. Words Per Sec | 37980.7 | 237508.2 | 110168.8 | 53051.3 | 27013.0 | 14950.8 | 8840.9 | 5630.7 | 4433.6 | | |
|---|
Len
14 | Searches | 72183 | 38470 | 8105 | 6037 | 4668 | 4395 | 4856 | 4347 | 1279 | 26 | |
|---|
| Avg. Per Day | 92.176 | 49.125 | 10.350 | 7.709 | 5.961 | 5.612 | 6.201 | 5.551 | 1.633 | 0.033 | |
| 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 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-10 | |
| 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 | 34.9 | 1.8 | 2.9 | 6.0 | 15.5 | 43.0 | 111.5 | 226.2 | 461.6 | 582.5 | |
| Avg. Seconds | 4.516 | 0.537 | 1.050 | 2.226 | 4.405 | 8.513 | 14.973 | 24.581 | 34.987 | 41.759 | |
|---|
| Avg. Words Per Sec | 39567.5 | 332676.2 | 170150.1 | 80266.5 | 40567.0 | 20990.9 | 11934.5 | 7269.4 | 5107.4 | 4279.1 | |
|---|
Len
15 | Searches | 59414 | 29102 | 6329 | 4976 | 4021 | 3629 | 4026 | 4147 | 2623 | 561 | |
|---|
| Avg. Per Day | 75.871 | 37.163 | 8.082 | 6.354 | 5.135 | 4.634 | 5.141 | 5.296 | 3.350 | 0.716 | |
| 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 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-17 | |
| 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 | 36.0 | 1.5 | 2.2 | 4.2 | 10.1 | 26.6 | 72.0 | 142.7 | 267.7 | 605.6 | |
| Avg. Seconds | 4.376 | 0.321 | 0.621 | 1.359 | 2.817 | 5.503 | 10.226 | 17.517 | 28.034 | 37.965 | |
|---|
| Avg. Words Per Sec | 40836.8 | 556637.8 | 287601.3 | 131448.6 | 63428.1 | 32473.8 | 17474.6 | 10200.8 | 6374.2 | 4706.8 | |
|---|
Len
16 | Searches | 48196 | 16911 | 6100 | 4565 | 3652 | 4107 | 4383 | 4303 | 2977 | 1179 | 19 |
|---|
| Avg. Per Day | 61.545 | 21.595 | 7.790 | 5.829 | 4.664 | 5.245 | 5.597 | 5.495 | 3.802 | 1.506 | 0.024 |
| 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 | 2013-05-19 | 2013-05-19 | 2013-05-18 | 2013-05-18 | 2013-05-18 | 2013-05-18 | 2013-05-19 | 2013-05-19 | 2013-05-19 | 2013-05-18 | 2013-03-10 |
| 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 | 38.9 | 1.0 | 1.5 | 2.4 | 5.1 | 15.3 | 51.2 | 121.8 | 191.6 | 361.6 | 533.6 |
| Avg. Seconds | 4.311 | 0.151 | 0.321 | 0.747 | 1.620 | 3.335 | 6.562 | 12.082 | 20.428 | 32.178 | 38.477 |
|---|
| Avg. Words Per Sec | 41453.0 | 1187222.6 | 556119.9 | 239355.8 | 110328.5 | 53582.3 | 27230.0 | 14790.4 | 8747.3 | 5553.1 | 4644.1 |
|---|