ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Конструкция запроса для поиска
22 сообщений из 22, страница 1 из 1
Конструкция запроса для поиска
    #40125683
Kolu4ka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kolu4ka
Гость
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr


Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?
...
Рейтинг: 0 / 0
11.01.2022, 11:03
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125688
Фотография komrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
komrad
Участник
Kolu4ka,

уберите is null
если сиквел не использует индекс, то считает что дешевле просканировать таблицу (меньше I/O)

выполните и сравните результат

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
set statistics io, time on;

Declare @emailadr varchar(50)='email@mail.ru'

Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr

print '------------- INDEX IS FORCED'

Select *
from dbo.UsersTable  with (index=ВашИндекс)
Where @emailadr is null or email=@emailadr

set statistics io, time off;
...
Рейтинг: 0 / 0
11.01.2022, 11:21
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125690
L_argo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
L_argo
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Declare @emailadr varchar(50)='email@mail.ru'

if @emailadr is null
Select *
from dbo.UsersTable 

else 

Select *
from dbo.UsersTable 
Where email=@emailadr
...
Рейтинг: 0 / 0
11.01.2022, 11:23
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125691
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Kolu4ka
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
5.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr
option (recompile)



Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?
...
Рейтинг: 0 / 0
11.01.2022, 11:28
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125698
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Kolu4ka,

@emailadr is null не очень хорошая идея, так как таблица может содержать большое количество строк.
...
Рейтинг: 0 / 0
11.01.2022, 11:42
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125713
Kolu4ka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kolu4ka
Гость
msLex, спасибо, все заработало как хотелось.
...
Рейтинг: 0 / 0
11.01.2022, 12:29
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125731
DaniilSeryi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DaniilSeryi
Участник
Kolu4ka
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr


Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?


Хотите прикол? Ваш запрос не будет возвращать ВСЕ ЗАПИСИ. Он будет возвращать записи, где поле email is null или где поле email равно значению переменной.
...
Рейтинг: 0 / 0
11.01.2022, 13:31
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125734
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
DaniilSeryi
Kolu4ka
Всем привет!
Есть простая задача, искать данные по переменной, если переменная пустая, то возвращать все записи.
Работает вот так.
Код: sql
1.
2.
3.
4.
Declare @emailadr varchar(50)='email@mail.ru'
Select *
from dbo.UsersTable 
Where @emailadr is null or email=@emailadr



Но, по полю email есть индекс и при такой конструкции он не работает.
Можно как-то составить 1 запрос и без динамика, чтобы при заполненной переменной все таки начал использоваться индекс?


Хотите прикол? Ваш запрос не будет возвращать ВСЕ ЗАПИСИ. Он будет возвращать записи, где поле email is null или где поле email равно значению переменной.

брэхня !

там же переменная в запросе на НУЛЛ проверяется, а не поле
...
Рейтинг: 0 / 0
11.01.2022, 13:37
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125739
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
msLex,

по-моему, еще хинтом можно отключить parameter sniffing, но тогда оценка кардинальности будет происходить не по гистограмме, а по плотности, как для подсказки unknown.
...
Рейтинг: 0 / 0
11.01.2022, 13:52
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125740
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Владислав Колосов
msLex,

по-моему, еще хинтом можно отключить parameter sniffing


В данном случае нужно не отключать parameter sniffing, а явно использовать значение параметра при построении плана.
...
Рейтинг: 0 / 0
11.01.2022, 13:55
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125766
Фотография komrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
komrad
Участник
msLex
Владислав Колосов
msLex,

по-моему, еще хинтом можно отключить parameter sniffing


В данном случае нужно не отключать parameter sniffing, а явно использовать значение параметра при построении плана.

надо только упомянуть, что что каждая рекомпиляция (option (recompile)) - это расход ЦПУ и времени
стоит это учитывать при использовании, т.к. при частом вызове эффект будет значительным
...
Рейтинг: 0 / 0
11.01.2022, 14:43
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125776
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
komrad
msLex
пропущено...


В данном случае нужно не отключать parameter sniffing, а явно использовать значение параметра при построении плана.

надо только упомянуть, что что каждая рекомпиляция (option (recompile)) - это расход ЦПУ и времени
стоит это учитывать при использовании, т.к. при частом вызове эффект будет значительным

Все так, и есть альтернатива с if else, но при сколь-нибудь сложных запросах есть вероятность их рассинхронизации.
...
Рейтинг: 0 / 0
11.01.2022, 14:56
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125802
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;
...
Рейтинг: 0 / 0
11.01.2022, 15:45
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125807
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Код: sql
1.
2.
3.
DECLARE @emailadr VARCHAR(50)='email@mail.ru';

SELECT * FROM dbo.UsersTable WHERE email=ALL(SELECT @emailadr WHERE @emailadr IS NOT NULL);

Но в этом варианте проверьте, устроит ли вас план!
...
Рейтинг: 0 / 0
11.01.2022, 15:56
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125808
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;



главное SET ANSI_NULLS OFF не делать
...
Рейтинг: 0 / 0
11.01.2022, 15:57
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125814
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
msLex
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;




главное SET ANSI_NULLS OFF не делать
Истину глаголите!
...
Рейтинг: 0 / 0
11.01.2022, 16:08
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125843
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;



кто бы объяснил задумку
...
Рейтинг: 0 / 0
11.01.2022, 17:39
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125846
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Ролг Хупин
iap
Код: sql
1.
2.
3.
4.
5.
DECLARE @emailadr VARCHAR(50)='email@mail.ru'

SELECT * FROM dbo.UsersTable WHERE @emailadr IS NULL
UNION ALL
SELECT * FROM dbo.UsersTable WHERE email=@emailadr;




кто бы объяснил задумку
Первый SELECT возвращает все строки, если параметр IS NULL, второй SELECT - записи, соответствующие параметру в случае, если параметр IS NOT NULL.
При этом в условии отсутствует OR, который в первоначальном варианте тормозит запрос.
Кстати, как получить записи, для которых email IS NULL, - это большой вопрос, поскольку значению параметра NULL придаётся особый смысл.
...
Рейтинг: 0 / 0
11.01.2022, 17:54
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125851
Kolu4ka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kolu4ka
Гость
iap, с юнионами точно не то.

Дело в том, что я привела задачу частично, более полно выглядит она примерно так, параметров сколько угодно и не все могут быть заполнены.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
Declare @emailadr varchar(50)='email@mail.ru'
, @name varchar(50)='Victor'
, @fam varchar(50)='Ivanov'

Select *
from dbo.UsersTable 
Where (@emailadr is null or email=@emailadr)
and (@name is null or uname=@name )
and (@fam is null or famil=@fam)


На некоторых полях есть индексы, но при таком запросе всегда возникали только сканы, при добавлении option (recompile) начали подхватываться индексы и запрос начал работать быстро.
...
Рейтинг: 0 / 0
11.01.2022, 18:00
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40125932
Фотография Ennor Tiegael
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ennor Tiegael
Участник
Kolu4ka,

В таком случае единственный вариант - это динамическая генерация запроса, либо на клиенте, либо в хранимке. Второе не рекомендую, я так делал однажды, задолбался код отлаживать. Если же клиентское приложение использует Entity Framework или какой другой ORM, то там это будет тривиально.
...
Рейтинг: 0 / 0
12.01.2022, 04:42
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40126073
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
iap
Ролг Хупин
пропущено...


кто бы объяснил задумку
Первый SELECT возвращает все строки, если параметр IS NULL, второй SELECT - записи, соответствующие параметру в случае, если параметр IS NOT NULL.
При этом в условии отсутствует OR, который в первоначальном варианте тормозит запрос.
Кстати, как получить записи, для которых email IS NULL, - это большой вопрос, поскольку значению параметра NULL придаётся особый смысл.


Разве Union all- это не OR в даном случае?
почемуне сделать одним запросом что-то типа:

Код: sql
1.
SELECT * FROM dbo.UsersTable WHERE (@emailadr IS NULL OR (@emailadr IS NOT NULL AND email=@emailadr))
...
Рейтинг: 0 / 0
12.01.2022, 17:22
| Ответить | Цитировать | Написать  
Конструкция запроса для поиска
    #40126077
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Ролг Хупин
iap
пропущено...
Первый SELECT возвращает все строки, если параметр IS NULL, второй SELECT - записи, соответствующие параметру в случае, если параметр IS NOT NULL.
При этом в условии отсутствует OR, который в первоначальном варианте тормозит запрос.
Кстати, как получить записи, для которых email IS NULL, - это большой вопрос, поскольку значению параметра NULL придаётся особый смысл.


Разве Union all- это не OR в даном случае?
почемуне сделать одним запросом что-то типа:

Код: sql
1.
SELECT * FROM dbo.UsersTable WHERE (@emailadr IS NULL OR (@emailadr IS NOT NULL AND email=@emailadr))

Ну не любит сервер OR. Тяжело ему оптимизировать. А логически-то, конечно, не поспоришь.
Переделывание OR на UNION - это хорошо известный способ оптимизации запроса.
Но тут говорят: таких условий слишком много... А первоначальный вопрос - это совсем не то, что подразумевалось. Во как!
...
Рейтинг: 0 / 0
12.01.2022, 17:39
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Конструкция запроса для поиска
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40125734&tid=1683940]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 151ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 15ms
total:  255ms
созд. / загр.: 255ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]