Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Отбор записей через подчиненный запрос  [new]
Palarm
Member

Откуда:
Сообщений: 1283
В таблице заказов есть поле NumZak (nvarchar(50)) для номеров заказов. Сами номера 2 видов: временные и основные. Временные формата 00_tmp, основные - yyyy-0000-000. Номерация по году и коду группы.
Сделал функцию для генерации временного или основного номера, но вышел затык с основными номерами. Допустим имеем 2 основных и 1 временный номер:

2015-0001-100
2015-0001-100
10_tmp

Делаю запрос:
SELECT Max(SUBSTRING(tmp.NumZak,6,4))
FROM (SELECT NumZak, CONVERT(int, LEFT(NumZak, 4)) AS YearNum
	FROM	 tab_Zakaz
	WHERE	(NOT (NumZak LIKE '%_tmp%')) AND (OrgKey1 = 52)) tmp
WHERE tmp.YearNum=2015

Выдает:
автор
Conversion failed when converting the nvarchar value '10_t' to data type int

Получается, что не смотря на предварительный отбор записей через вложенный селект, запись с временным номером влияет на фильтр по году? Как такое может быть? Сделал сохраненный запрос вместо вложенного селекта - то же самое, причем ругань именно на WHERE tmp.YearNum=2015. Видимо придется создавать через хранимку временную таблицу вместо подчиненного запроса и потом дропать ее? Оно бы и ладно - но вот почему такое происходит?
28 ноя 15, 20:08    [18485936]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
Добрый Э - Эх
Guest
Palarm
... предварительный отбор записей через вложенный селект...
план запроса смотрел хоть? откуда уверенность, что оптимизатор не раскрыл твой вложенный запрос в линейный, без подзапросов?
28 ноя 15, 20:17    [18485962]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
Palarm
Member

Откуда:
Сообщений: 1283
Посмотрел, увидел фигу :) Ладно, пусть пока хранимка и #tmp
28 ноя 15, 21:01    [18486129]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Palarm
Получается, что не смотря на предварительный отбор записей через вложенный селект, запись с временным номером влияет на фильтр по году? Как такое может быть?
Разумеется, сервер никогда не делает "предварительный отбор записей через вложенный селект".

План строится для запроса целиком, вложенные запросы не являются никакими указаниями. План запроса строится исходя из семантики запроса, а не исходя из "оформления".

Для конкретно вашего запроса план выполнения не изменится, если вы вложенный запрос раскроете.

Для сложных запросов изменение текста без изменения семантики может изменить план, но как изменить, и изменить ли вообще - угадать сложно, а уж точно сказать нельзя, никаких правил для этого нет.
Palarm
Сделал сохраненный запрос вместо вложенного селекта
Что такое "сохраненный запрос"? Как это по английски, "stored query"?
Palarm
Видимо придется создавать через хранимку временную таблицу вместо подчиненного запроса и потом дропать ее?
Можно изменить текст запроса, написав вместо:
CONVERT(int, LEFT(NumZak, 4))
так:
CONVERT(int, LEFT(case when NumZak LIKE '%_tmp%' then null else NumZak end, 4))
28 ноя 15, 23:33    [18486722]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
Palarm
Member

Откуда:
Сообщений: 1283
alexeyvg
План строится для запроса целиком, вложенные запросы не являются никакими указаниями. План запроса строится исходя из семантики запроса, а не исходя из "оформления".
А как же:
Select * From ... Where id In(Select id From... Where...)

тут ведь по любому сначала нужно получить набор записей из вложенного селекта, потом подставить его в условие отбора главного запроса. А если этого не будет - какой смысл делать подзапросы с Where возвращающие все записи?
29 ноя 15, 08:48    [18487298]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Palarm,
Если есть индекс, то Поиск по Left должен быть выгоднее чем like'%.... '.
И в плане будет сначало seek pridicate left..., уже потом для подошедших строк сверху лайком, predicate like.
В данном случае зачем вы кастуете вообзе к инту? Сравнивайте в виде строки '2015'

Смотрите на план, там все должно быть.
29 ноя 15, 10:55    [18487363]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Palarm
alexeyvg
План строится для запроса целиком, вложенные запросы не являются никакими указаниями. План запроса строится исходя из семантики запроса, а не исходя из "оформления".
А как же:
Select * From ... Where id In(Select id From... Where...)


тут ведь по любому сначала нужно получить набор записей из вложенного селекта, потом подставить его в условие отбора главного запроса. А если этого не будет - какой смысл делать подзапросы с Where возвращающие все записи?
Почему по любому, почему этот алгоритм единственный?

Например, можно выбрать все записи из внешнего запроса, а потом для каждой записи из внешнего запроса сделать поиск соотв. записи из внутреннего запроса.
Ещё можно смерджить результаты внешнего и внутреннего запросов.

Я уж не говорю, что в вашем случае у внешнего запроса есть условие, и может оказаться выгоднее отфильтровать сначала по нему.

Mike_za
Если есть индекс, то Поиск по Left должен быть выгоднее чем like'%.... '.
И в плане будет сначало seek pridicate left..., уже потом для подошедших строк сверху лайком, predicate like.
Вот именно, вот сервер и выбрал быстрый поиск по индексу, вместо сказа таблицы с старвнением всех записей. То есть это действительно оказалось выгоднее.
29 ноя 15, 11:45    [18487434]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Palarm
А если этого не будет - какой смысл делать подзапросы с Where возвращающие все записи?
Главное, что нужно понимать - SQL - это функциональный язык программирования.
Для него важна семантика запроса, а "порядок выполнения" в общем случае не задаётся (опустим всякие расширения, фиксы планов, хинты).

Семантика вашего запроса такова:
Выбрать данные из таблицы, которые удовлетворяют трём условиям:
1) NOT (NumZak LIKE '%_tmp%')
2) OrgKey1 = 52
3) CONVERT(int, LEFT(NumZak, 4))=2015

Как вы это напишите - совершенно неважно, хоть в 5ти вложенных запросах, семантика неизменна.

Ну и вот сервер, вооружившись статистикой, метаданными о таблице, индексах, материализованных представлениях, состояния кеша, и ещё кучи параметров, строит планы выполнения.
Один план, второй, ... сотый, много планов.

Для каждого плана он расчитывает расход разных ресурсов для его выполнения.

И выбирает самый оптимальный план, по которому и выполняет запрос.

Если бы у вас был индекс, например, (OrgKey1) INCLUDE(NumZak), а индекса по NumZak не было, то, возможно, этой ошибки и не произошло бы (но не факт, потому что сервер мог бы одновременно делать вычисление вместе с выборкой из инедкса).

Но у вас наверняка есть индекс по NumZak, а упомянутого мной нет, и вот сервер выбрал план, приводящий в данном случае к ошибке.
29 ноя 15, 11:54    [18487446]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
Добрый Э - Эх
Guest
Palarm,

каскадный CASE заюзай и спи спокойно. порядок проверки предикатов будет именно такой, какой пожелаешь...
29 ноя 15, 14:46    [18487698]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
Palarm
Member

Откуда:
Сообщений: 1283
Всем спасибо :) Не думал, что сервер такой умный, что сам меняет порядок выполнения запроса. Стало быть смотреть план и юзать CASE
29 ноя 15, 15:28    [18487805]     Ответить | Цитировать Сообщить модератору
 Re: Отбор записей через подчиненный запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Palarm
Не думал, что сервер такой умный, что сам меняет порядок выполнения запроса.
Да, и мало того, план зависит и от статистики. Например, для CONVERT(int, LEFT(NumZak, 4))=2015 может быть один план, а для CONVERT(int, LEFT(NumZak, 4))=2016 - другой :-)
Palarm
Стало быть смотреть план и юзать CASE
Для вашего простого запроса нужно, как уже давно написали, использовать CASE для исключения ошибочной конвертации.
План нужен был просто для понимания, как вообще сервер обрабатывает запросы.
30 ноя 15, 01:51    [18489646]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить