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

Откуда: от верблюда
Сообщений: 428
 		Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)

есть таблицы
DECLARE @Table1 table (ID int, ssum int, scount int)
INSERT @Table1 values(1,500,5)
INSERT @Table1 values(2,300,2)
INSERT @Table1 values(3,700,8)

DECLARE @T2d table (ID int, sumlimit int, cntlimit int)
INSERT @T2d values(1,400,0)
INSERT @T2d values(1,500,0)
INSERT @T2d values(1,600,0)
INSERT @T2d values(2,600,0)
INSERT @T2d values(2,700,0)
INSERT @T2d values(2,900,0)
INSERT @T2d values(3,0,4)
INSERT @T2d values(3,0,7)
INSERT @T2d values(3,0,9)


нужно выбрать из таблицы 2 все записи коррелирующие по ID с таблицей 1, где sumlimit<=ssum и cntlimit<=scount (максимальные не превышающие)
sumlimit и cntlimit не бывают заполнены одновременно внутри одного ИД

далее, к результату нужно присоединить колонку с максимальным значением внутри текущего ИД

2 варианта присоединения колонки, оптимизатор говорит что 2й вариант более производительный
вопрос в том всегда ли так будет или это частный случай?
select
	t1.id,T2d.sumlimit,T2d.cntlimit,maxLimits.maxlimit
from
	@table1 t1
join
	(select
		id, isnull(nullif(max(sumlimit),0),max(cntlimit)) maxlimit
	 from
		@T2d T2d
	 group by id) maxLimits
on maxlimits.id = t1.id
join
	@T2d T2d
on T2d.id = t1.id
--*****************************************
select
	t1.id,limits.sumlimit,limits.cntlimit,
	(select
		isnull(nullif(max(sumlimit),0),max(cntlimit)) maxlimit
	 from
		@T2d maxLimits
	 where
		maxLimits.id = t1.id) maxlimit
from
	@table1 t1
join
	@T2d limits
on limits.id = t1.id


поиск максимального не превышающего
select
	t1.id,limits.sumlimit,limits.cntlimit,
	(select
		isnull(nullif(max(sumlimit),0),max(cntlimit)) maxlimit
	 from
		@T2d maxLimits
	 where
		maxLimits.id = t1.id) maxlimit
from
	@table1 t1
join
	@T2d limits
on limits.id = t1.id
join
	(select
		tab1.id, max(T2d.sumlimit) msumlimit, max(T2d.cntlimit) mcntlimit
	 from
		@table1 tab1
	 join
		@T2d T2d
	 on T2d.id = tab1.id
		and
		T2d.sumlimit <= tab1.ssum
		and
		T2d.cntlimit <= tab1.scount
	 group by tab1.id) matchedlimits
	 on matchedlimits.id = t1.id
		and
		matchedlimits.msumlimit = limits.sumlimit
		and
		matchedlimits.mcntlimit = limits.cntlimit

в этом случае получается, что для каждой строки максимальное значение вычисляется для всех ID, которые будут в таблице 1 а потом отсеивается, т.е. выполняются лишние действия, что как-то не очень хорошо...

хочется нечто подобное этому
select
	t1.id,matchedlimits.sumlimit,matchedlimits.cntlimit,
	(select
		isnull(nullif(max(sumlimit),0),max(cntlimit)) maxlimit
	 from
		@T2d maxLimits
	 where
		maxLimits.id = t1.id) maxlimit
from
	@table1 t1
join
	@T2d limits
on limits.id = t1.id
join
	(select
		t1.id, max(T2d.sumlimit) msumlimit, max(T2d.cntlimit) mcntlimit
	 from
		@T2d T2d
	 where T2d.id = t1.id
		and
		T2d.sumlimit <= t1.ssum
		and
		T2d.cntlimit <= t1.scount
	 ) matchedlimits
on matchedlimits.id = t1.id

но, понятное дело, это не работает, ссылаясь на сообщение The column prefix 't1' does not match with a table name or alias name used in the query.
какие есть еще варианты?
сразу хочу сказать, что по возможности хотелось бы варьировать последним джойном переключаясь между join и left join, чтобы в результат попадали в первом случае только те что удовлетворяют, а во 2м - все, соответственно
(в предпоследнем варианте именно так), в одних обстоятельствах меня интересуют лишь удовлетворяющие, а в других все
а разница в минимальном изменении запроса
но это лишь пожелание... если не получится, буду запускать два разных варианта для разных случаев - ничего страшного

если где-то в рассуждениях я ошибаюсь, уж не кидайте камнями :)

спс :)
28 авг 13, 15:25    [14766646]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
TJ001
2 варианта присоединения колонки, оптимизатор говорит что 2й вариант более производительный
вопрос в том всегда ли так будет или это частный случай?

Никакой план не является абсолютно лучшим для всех случаев
Но он может быть таким для большого количества случаев
28 авг 13, 15:38    [14766720]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
 		а почему у вас строка 1,600,0,600
попадает в результат, если
автор
нужно выбрать из таблицы 2 все записи коррелирующие по ID с таблицей 1, где sumlimit<=ssum и cntlimit<=scount
? Вроде как 600>500 т.е. sumlimit >ssum для id=1
28 авг 13, 17:19    [14767252]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Мистер Хенки
а почему у вас строка 1,600,0,600
попадает в результат, если
автор
нужно выбрать из таблицы 2 все записи коррелирующие по ID с таблицей 1, где sumlimit<=ssum и cntlimit<=scount
? Вроде как 600>500 т.е. sumlimit >ssum для id=1


не понимаю о чем речь
у меня результат вот такой:

idsumlimitcntlimitmaxlimit
15000600
3079

что удовлетворяет требованиям задачи
28 авг 13, 18:01    [14767451]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TJ001
2 варианта присоединения колонки, оптимизатор говорит что 2й вариант более производительный
вопрос в том всегда ли так будет или это частный случай?

С точки зрения SQL Server запросы не эквивалентны.
Когда подзапрос встречается в предложении select он, во многих случаях, но не во всех, может быть раскрыт. Примерно так, как сделали вы. Но разница в том, что в подзапрос в select не может повлиять на итоговое число строк, а если он в соединении то может. Поменяйте тип соединения на left и вы увидите одинаковые (почти) по стоимости планы одинаковой формы.

Касательно тестов на производительность, вам правильно сказали что нет одного плана идеального для всех случаев. Я бы даже сузил тезис до - нет одного идеального плана для одного и того же запроса, но с разным распределением данных (именно по этому есть понятие статистика, селективность и т.д.).
Отсюда вытекает два момента:
- Если у вас проблема с производительностью, публикуйте действительные планы запросов, запрос (если его нет в плане), структуру таблиц и индексов (самый шик, это еще и дамп статистики, тогда вообще можно попытаться до цифр расписать решения оптимизатора)
- Если вы делаете предварительные исследования, пытаясь выяснить у сообщества наилучшее решение, сделайте репро ваших предполагаемых данных и структуры (тестовые таблицы, с тестовым заполнением данными наиболее характерного распределения данных в будущем - тут может быть не важна точность и точные значения - важны распределения, порядок, прирост).

Чего не следует делать никогда:
- Полагаться на тесты основанные на табличных переменных, если только вы не совершенно точно уверены, что принимаете во внимание все их особенности (например, отсутствие статистики) и демонстрируемые эффекты точно такие же, как и на обычных таблицах
- Делать тесты с небольшой порцией (часто - не репрезентативных) данных, с верой в то, что это транслируется на реальные данные (в реальности слишком много вовлечено в оптимизаторе условий - число строк, размер строк, параллелизм и т.д.)

П.С.
И еще, переходите уже хотя бы на 2005 =) Убеждайте руководство.
Т.к. даже соблюдай вы все эти условия, лично я бы, все равно, не рискнул давать конкретные советы, т.к. у меня нет под рукой стенда с 2000, чтобы это проверить, и разворачивать его не хочется =)
28 авг 13, 19:50    [14767793]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
 		SomewhereSomehow,

да, если сделать через left join, то планы становятся почти одинаковыми, в первом варианте даже меньше на одну логическую операцию "Compute Scalar"

признаться, про left join я даже не подумал т.к. в моем случае в т2 всегда есть запись с соответствующим ИД, а про то что СУБД об этом не знает я забыл :)

спасибо за подсказку!:)
29 авг 13, 09:23    [14769155]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
 		...однако, на реальных данных первый вариант сохраняет свою производительность, а второй захлебывается, ну это я так... это как раз то, о чем шла речь - нужно подбирать под ситуацию :)
29 авг 13, 09:42    [14769250]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
 		ой, не первый, что я говорю?! - второй!, где максимум вычисляется селектом, что в качестве колонки, а не джойна
29 авг 13, 09:44    [14769257]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
TJ001
...однако, на реальных данных первый вариант сохраняет свою производительность, а второй захлебывается, ну это я так... это как раз то, о чем шла речь - нужно подбирать под ситуацию :)

ой, не первый, что я говорю?! - второй!, где максимум вычисляется селектом, что в качестве колонки, а не джойна

А почему "однако"? Я же вроде сказал, что запросы разные - планы разные - время выполнения разное. Все логично.
29 авг 13, 11:25    [14769911]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
 		SomewhereSomehow,
ну, это я к тому, что сначала мне показалось, что с левым соединением получается даже лучше, а когда попробовал на реальных данных, то картинка изменилась
это только рассуждения "вслух" о своих же экспериментах, а не претензии :)
29 авг 13, 13:03    [14770561]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить