Недавно велись дебаты о наиболее эффективном способе поиска в базе данных MS SQL с использованием LIKE
и подстановочных знаков. Мы сравниваем, используя %abc%
, %abc
и abc%
. Один человек сказал, что у вас всегда должен быть подстановочный знак в конце термина (abc%
).. Итак, по их словам, если бы мы хотели найти что-то, что заканчивается на «abc», было бы наиболее эффективно использовать `reverse (column )LIKE reverse ('%abc' ).
Я настроил тест, используя SQL Server 2008 (R2 ), чтобы сравнить каждое из следующих утверждений:
select * from CLMASTER where ADDRESS like '%STREET'
select * from CLMASTER where ADDRESS like '%STREET%'
select * from CLMASTER where ADDRESS like reverse('TEERTS%')
select * from CLMASTER where reverse(ADDRESS) like reverse('%STREET')
CLMASTER содержит около 500 000 записей, около 7 400 адресов, которые заканчиваются на «улица», и около 8 500 адресов, которые содержат «улицу», но не обязательно в конце. Каждый тестовый прогон занимал 2 секунды, и все они возвращали одинаковое количество строк, за исключением %STREET%
, который нашел около 900 дополнительных результатов, потому что подбирал адреса с номером квартиры на конце.
Поскольку тест SQL Server не показал никакой разницы во времени выполнения, я перешел на PHP, где использовал следующий код, переключаясь между операторами, для быстрого выполнения нескольких тестов:
<?php
require_once("config.php");
$connection = odbc_connect( $connection_string, $U, $P );
for ($i = 0; $i < 500; $i++) {
$m_time = explode(" ",microtime());
$m_time = $m_time[0] + $m_time[1];
$starttime = $m_time;
$Message=odbc_exec($connection,"select * from CLMASTER where ADDRESS like '%STREET%'");
$Message=odbc_result($Message,1);
$m_time = explode(" ",microtime());
$m_time = $m_time[0] + $m_time[1];
$endtime = $m_time;
$totaltime[] = ($endtime - $starttime);
}
odbc_close($connection);
echo "<b>Test took and average of:</b> ".round(array_sum($totaltime)/count($totaltime),8)." seconds per run.<br>";
echo "<b>Test took a total of:</b> ".round(array_sum($totaltime),8)." seconds to run.<br>";
?>
Результаты этого теста были примерно такими же неоднозначными, как и результаты тестирования в SQL Server.
%STREET
выполнено за 166,5823 секунды (, в среднем 0,3331 на запрос ), и усреднено 500 результатов, найденных за 0,0228.
%STREET%
выполнено за 149,4500 секунд (, в среднем 0,2989 на запрос ), и в среднем 500 результатов найдено за 0,0177. (Более быстрое время для каждого результата, потому что он находит больше результатов, чем другие, за одинаковое время.)
reverse(ADDRESS) like reverse('%STREET')
выполнено за 134,0115 секунд (, в среднем 0,2680 на запрос ), и в среднем 500 результатов были найдены за 0,0183 секунды.
reverse('TREETS%')
выполнено за 167,6960 секунд (, в среднем 0,3354 на запрос ), и усреднено 500 результатов, найденных за 0,0229.
Мы ожидали, что этот тест покажет, что %STREET%
будет самым медленным в целом, хотя на самом деле он был самым быстрым в выполнении и имел лучшее среднее время для возврата 500 результатов. В то время как предложенный reverse('%STREET')
был самым быстрым в целом, но был немного медленнее по времени, чтобы вернуть 500 результатов.
Дополнительное удовольствие :Коллега запустил профилировщик на сервере, пока мы проводили тесты, и обнаружил, что использование двойного подстановочного знака привело к значительному увеличению загрузки ЦП, в то время как другие тесты были в пределах 1 -2% друг от друга.
Есть ли какие-либо эксперты по эффективности SQL, которые могут объяснить, почему наличие подстановочного знака в конце строки поиска было бы лучше, чем в начале, и, возможно, почему поиск с подстановочными знаками в начале и в конце строки был быстрее, чем с подстановочным знаком только в начале?