SQL Select Top с предложением Left Join и Where
У меня есть две таблицы базы данных:
Города со столбцами:
Country_Code | City_Code | City_Name
Страны со столбцами
Country_Code | Country_Name
На основе нескольких символов, введенных пользователем, он проверяет столбец City_Name, чтобы вернуть результаты для заполнения поля автозаполнения City. Результат должен иметь код города, название города, код страны и название страны, поэтому необходимо объединение.
Я использую запрос
SELECT TOP 10
ci.Country_Code, ci.City_Code, ci.City_Name, co.Country_Name
FROM
Cities ci
LEFT OUTER JOIN
Countries co ON ci.Country_Code = co.Country_Code
WHERE
ci.City_Name LIKE '@CityName'
ORDER BY
ci.City_Name
Результаты, которые я получаю, верны, но выполнение запроса занимает много времени. Насколько я понимаю, во-первых, результаты содержат объединение обеих таблиц, затем срабатывает предложение where, чтобы получить только определенные строки, которые упорядочены по названию города и возвращены 10 лучших результатов.
Мой вопрос в том, есть ли способ ускорить запрос. Проверьте предложение where, а затем выполните соединение, а еще лучше выполните его только для первых 10 результатов? Я попытался поместить свое предложение WHERE в предложение ON, но это дало неправильные результаты.
Обновлено: @CityName содержит 2-3 символа, введенных пользователем, а затем «%».
Как выглядит @CityName?
Насколько медленно это медленно? В мире тысячи городов, а не миллионы. Насколько велики столы?
Ответы 2
Я бы предложил начать с добавления кластеризованный индекс на Countries.Country_Code (также сделать его первичным ключом таблицы стран, если это еще не так). Индекс будет сортировать таблицу таким образом, чтобы скорость поиска в объединении увеличивалась.
Вероятно, также необходимо добавить индекс на CityName.
Похоже, это ваш запрос:
SELECT TOP 10 ci.Country_Code, ci.City_Code, ci.City_Name, co.Country_Name
FROM Cities ci LEFT OUTER JOIN
Countries co
ON ci.Country_Code = co.Country_Code
WHERE ci.City_Name LIKE @CityName
ORDER BY ci.City_Name ;
Кавычки не нужны вокруг @CityName.
Я не понимаю LEFT JOIN. Это говорит о том, что есть города без действительного Country_Code, и это маловероятно.
Предполагая, что @CityNameнет начинается с подстановочного знака (как было предложено в вашем вопросе), тогда можно использовать индекс. Я бы предложил следующие индексы:
cities(city_name, country_code)countries(country_code, country_name)
Второй не нужен, если country_code является первичным ключом.
Устранение неполадок с производительностью — сильно зависит от поставщика, поэтому добавьте тег, чтобы указать, используете ли вы
mysql,postgresql,sql-server,oracleилиdb2— или что-то другое.