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 | 11105463 | 6587882 | 1480873 | 1712894 | 781785 | 332750 | 126925 | 57148 | 20557 | 4607 | 42 |
|---|
| Avg. Per Day | 9142.584 | 5423.481 | 1219.130 | 1410.142 | 643.605 | 273.937 | 104.491 | 47.047 | 16.924 | 3.793 | 0.035 |
| 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 |
| 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 | 122.8 | 7.6 | 108.8 | 404.7 | 370.9 | 340.1 | 262.8 | 254.5 | 285.9 | 439.0 | 521.5 |
| Avg. Seconds | 6.715 | 1.895 | 4.119 | 11.873 | 24.059 | 30.840 | 30.306 | 31.081 | 36.262 | 46.050 | 51.663 |
|---|
| Avg. Words Per Sec | 26611.0 | 94319.8 | 43378.0 | 15049.7 | 7427.2 | 5794.2 | 5896.3 | 5749.2 | 4927.8 | 3880.3 | 3458.8 |
|---|
Len
2 | Searches | 45492 | 45492 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 37.451 | 37.451 |
| Earliest | 2011-04-05 | 2011-04-05 |
| Latest | 2014-07-24 | 2014-07-24 |
| Avg. Words Checked | 178691.0 | 178691.0 |
| Avg. Words Matched | 26.8 | 26.8 |
| Avg. Seconds | 0.259 | 0.259 |
|---|
| Avg. Words Per Sec | 690926.3 | 690926.3 |
|---|
Len
3 | Searches | 364842 | 250145 | 114697 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 300.357 | 205.932 | 94.424 |
| Earliest | 2011-03-30 | 2011-03-30 | 2011-04-06 |
| Latest | 2014-07-24 | 2014-07-24 | 2014-07-24 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 151.8 | 24.7 | 428.9 |
| Avg. Seconds | 0.627 | 0.434 | 1.049 |
|---|
| Avg. Words Per Sec | 284802.7 | 411548.6 | 170370.7 |
|---|
Len
4 | Searches | 841434 | 637596 | 167556 | 36282 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 692.711 | 524.902 | 137.941 | 29.869 |
| Earliest | 2011-03-29 | 2011-03-29 | 2011-03-29 | 2011-04-16 |
| Latest | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 127.2 | 17.2 | 281.0 | 1349.3 |
| Avg. Seconds | 1.183 | 0.756 | 1.981 | 4.999 |
|---|
| Avg. Words Per Sec | 151028.6 | 236219.9 | 90221.8 | 35744.5 |
|---|
Len
5 | Searches | 1483904 | 1017664 | 216016 | 250090 | 134 | N/A | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1221.626 | 837.793 | 177.835 | 205.887 | 0.110 |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-30 | 2011-03-30 | 2011-08-17 |
| Latest | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-13 |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 |
| Avg. Words Matched | 181.0 | 10.6 | 147.2 | 903.7 | 405.0 |
| Avg. Seconds | 2.741 | 1.281 | 3.312 | 8.184 | 12.904 |
|---|
| Avg. Words Per Sec | 65182.6 | 139443.6 | 53949.4 | 21835.1 | 13847.7 |
|---|
Len
6 | Searches | 1741830 | 1159802 | 203370 | 367601 | 11057 | | N/A | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1433.963 | 954.808 | 167.425 | 302.628 | 9.103 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2012-08-14 | |
| Latest | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 148.3 | 7.3 | 87.8 | 589.7 | 1384.9 | |
| Avg. Seconds | 4.506 | 1.968 | 4.770 | 11.882 | 20.577 | |
|---|
| Avg. Words Per Sec | 39658.2 | 90781.5 | 37461.4 | 15038.9 | 8683.9 | |
|---|
Len
7 | Searches | 1753885 | 1101795 | 190244 | 369335 | 92197 | 314 | | N/A | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1443.888 | 907.054 | 156.619 | 304.055 | 75.901 | 0.259 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-05-12 | 2011-05-12 | 2011-08-17 | |
| Latest | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 132.2 | 5.6 | 49.9 | 368.7 | 864.9 | 1061.1 | |
| Avg. Seconds | 6.761 | 2.629 | 5.931 | 14.636 | 26.226 | 31.559 | |
|---|
| Avg. Words Per Sec | 26428.4 | 67971.7 | 30125.8 | 12208.6 | 6813.4 | 5662.2 | |
|---|
Len
8 | Searches | 1422064 | 818565 | 154103 | 256466 | 187299 | 5583 | 48 | | N/A | N/A | N/A |
|---|
| Avg. Per Day | 1170.716 | 673.884 | 126.865 | 211.136 | 154.194 | 4.596 | 0.040 | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-08-17 | 2011-08-17 | 2013-06-07 | |
| Latest | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-05-27 | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | |
| Avg. Words Matched | 114.2 | 3.7 | 20.3 | 190.8 | 538.7 | 1129.8 | 1533.0 | |
| Avg. Seconds | 9.243 | 2.963 | 6.378 | 15.488 | 29.594 | 39.122 | 43.743 | |
|---|
| Avg. Words Per Sec | 19332.8 | 60303.8 | 28017.2 | 11537.3 | 6038.1 | 4567.5 | 4085.1 | |
|---|
Len
9 | Searches | 1076120 | 542854 | 131192 | 158546 | 193993 | 49512 | 23 | | | N/A | N/A |
|---|
| Avg. Per Day | 885.917 | 446.905 | 108.004 | 130.523 | 159.705 | 40.761 | 0.019 | | |
| Earliest | 2011-03-28 | 2011-03-28 | 2011-03-28 | 2011-04-04 | 2011-04-06 | 2012-08-14 | 2013-05-28 | | |
| Latest | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-05-07 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 105.8 | 2.7 | 9.5 | 70.2 | 334.6 | 709.4 | 294.5 | | |
| Avg. Seconds | 11.387 | 2.751 | 6.184 | 14.052 | 28.897 | 42.701 | 59.078 | | |
|---|
| Avg. Words Per Sec | 15692.4 | 64949.7 | 28897.7 | 12716.6 | 6183.8 | 4184.7 | 3024.7 | | |
|---|
Len
10 | Searches | 718703 | 336635 | 92390 | 91009 | 113365 | 82431 | 2810 | 50 | 13 | | N/A |
|---|
| Avg. Per Day | 591.673 | 277.135 | 76.060 | 74.923 | 93.328 | 67.861 | 2.313 | 0.041 | 0.011 | |
| 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-05-04 | 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 | 90.9 | 2.3 | 6.2 | 32.5 | 170.9 | 473.6 | 914.9 | 21.0 | 2710.5 | |
| Avg. Seconds | 11.744 | 2.236 | 5.208 | 11.455 | 23.819 | 40.337 | 48.171 | 57.816 | 41.984 | |
|---|
| Avg. Words Per Sec | 15215.7 | 79929.2 | 34311.4 | 15599.8 | 7501.9 | 4430.0 | 3709.5 | 3090.7 | 4256.2 | |
|---|
Len
11 | Searches | 510423 | 221036 | 68055 | 61656 | 67018 | 73517 | 18766 | 375 | | | |
|---|
| Avg. Per Day | 420.206 | 181.968 | 56.026 | 50.758 | 55.173 | 60.523 | 15.449 | 0.309 | | | |
| 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-17 | | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | | |
| Avg. Words Matched | 77.6 | 2.1 | 4.9 | 21.7 | 87.9 | 280.5 | 560.1 | 1148.6 | | | |
| Avg. Seconds | 11.451 | 1.643 | 4.120 | 9.083 | 18.563 | 33.809 | 47.456 | 56.761 | | | |
|---|
| Avg. Words Per Sec | 15604.6 | 108779.5 | 43369.7 | 19673.2 | 9626.3 | 5285.3 | 3765.4 | 3148.1 | | | |
|---|
Len
12 | Searches | 372206 | 156682 | 49534 | 42956 | 43361 | 45405 | 30419 | 3841 | 8 | | |
|---|
| Avg. Per Day | 306.419 | 128.989 | 40.779 | 35.364 | 35.697 | 37.380 | 25.042 | 3.162 | 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 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.8 | 1.9 | 3.9 | 12.2 | 51.9 | 143.4 | 337.7 | 666.2 | 946.0 | | |
| Avg. Seconds | 10.151 | 1.187 | 3.006 | 6.737 | 13.603 | 25.234 | 39.911 | 52.907 | 132.110 | | |
|---|
| Avg. Words Per Sec | 17603.3 | 150481.8 | 59446.7 | 26522.5 | 13136.4 | 7081.3 | 4477.2 | 3377.4 | 1352.6 | | |
|---|
Len
13 | Searches | 277864 | 114248 | 35669 | 30550 | 29692 | 30753 | 26253 | 10343 | 356 | | |
|---|
| Avg. Per Day | 228.752 | 94.055 | 29.365 | 25.150 | 24.444 | 25.317 | 21.613 | 8.515 | 0.293 | | |
| 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-23 | | |
| Avg. Words Checked | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | 178691.0 | | |
| Avg. Words Matched | 49.9 | 1.8 | 3.3 | 8.9 | 31.0 | 100.4 | 190.4 | 389.7 | 691.5 | | |
| Avg. Seconds | 8.739 | 0.842 | 2.119 | 4.655 | 9.551 | 18.061 | 30.883 | 43.130 | 51.277 | | |
|---|
| Avg. Words Per Sec | 20448.4 | 212145.3 | 84333.9 | 38389.8 | 18708.8 | 9893.5 | 5786.2 | 4143.1 | 3484.8 | | |
|---|
Len
14 | Searches | 209192 | 83912 | 25364 | 21164 | 19064 | 20608 | 20864 | 14724 | 3449 | 43 | |
|---|
| Avg. Per Day | 172.218 | 69.081 | 20.881 | 17.423 | 15.694 | 16.966 | 17.176 | 12.122 | 2.839 | 0.035 | |
| 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 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 | 48.8 | 1.7 | 2.9 | 6.5 | 19.5 | 69.5 | 137.1 | 239.1 | 473.8 | 568.2 | |
| Avg. Seconds | 7.990 | 0.586 | 1.349 | 3.001 | 6.233 | 12.407 | 22.185 | 34.946 | 49.423 | 48.488 | |
|---|
| Avg. Words Per Sec | 22365.0 | 304827.1 | 132457.0 | 59549.5 | 28670.0 | 14402.0 | 8054.5 | 5113.3 | 3615.5 | 3685.3 | |
|---|
Len
15 | Searches | 161132 | 61602 | 17906 | 15452 | 14150 | 13859 | 15134 | 14235 | 7456 | 1338 | |
|---|
| Avg. Per Day | 132.652 | 50.714 | 14.741 | 12.721 | 11.649 | 11.409 | 12.459 | 11.719 | 6.138 | 1.102 | |
| 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-23 | |
| 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.2 | 1.5 | 2.2 | 4.5 | 12.0 | 38.7 | 91.3 | 160.8 | 277.5 | 601.2 | |
| Avg. Seconds | 7.197 | 0.350 | 0.787 | 1.796 | 3.834 | 7.639 | 14.451 | 25.380 | 38.486 | 51.716 | |
|---|
| Avg. Words Per Sec | 24828.3 | 510182.0 | 226949.4 | 99475.6 | 46609.7 | 23393.3 | 12365.4 | 7040.6 | 4643.0 | 3455.3 | |
|---|
Len
16 | Searches | 126372 | 39854 | 14777 | 11787 | 10455 | 10768 | 12608 | 13580 | 9275 | 3226 | 42 |
|---|
| Avg. Per Day | 104.036 | 32.810 | 12.165 | 9.704 | 8.607 | 8.865 | 10.380 | 11.180 | 7.636 | 2.656 | 0.035 |
| 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-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 | 2014-07-24 |
| 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 | 45.9 | 1.0 | 1.5 | 2.4 | 4.9 | 15.3 | 53.6 | 126.3 | 203.3 | 370.1 | 521.5 |
| Avg. Seconds | 6.652 | 0.173 | 0.386 | 0.924 | 2.129 | 4.275 | 8.788 | 16.707 | 28.912 | 43.668 | 51.663 |
|---|
| Avg. Words Per Sec | 26861.4 | 1034647.6 | 462782.6 | 193397.1 | 83924.1 | 41797.9 | 20332.4 | 10695.3 | 6180.5 | 4092.0 | 3458.8 |
|---|