Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Никакой план не является абсолютно лучшим для всех случаев Но он может быть таким для большого количества случаев |
||
28 авг 13, 15:38 [14766720] Ответить | Цитировать Сообщить модератору |
Мистер Хенки Member Откуда: канализация Сообщений: 6615 |
а почему у вас строка 1,600,0,600 попадает в результат, если
|
||
28 авг 13, 17:19 [14767252] Ответить | Цитировать Сообщить модератору |
TJ001 Member Откуда: от верблюда Сообщений: 428 |
не понимаю о чем речь у меня результат вот такой:
что удовлетворяет требованиям задачи |
|||||||||||||||||
28 авг 13, 18:01 [14767451] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
С точки зрения SQL Server запросы не эквивалентны. Когда подзапрос встречается в предложении select он, во многих случаях, но не во всех, может быть раскрыт. Примерно так, как сделали вы. Но разница в том, что в подзапрос в select не может повлиять на итоговое число строк, а если он в соединении то может. Поменяйте тип соединения на left и вы увидите одинаковые (почти) по стоимости планы одинаковой формы. Касательно тестов на производительность, вам правильно сказали что нет одного плана идеального для всех случаев. Я бы даже сузил тезис до - нет одного идеального плана для одного и того же запроса, но с разным распределением данных (именно по этому есть понятие статистика, селективность и т.д.). Отсюда вытекает два момента: - Если у вас проблема с производительностью, публикуйте действительные планы запросов, запрос (если его нет в плане), структуру таблиц и индексов (самый шик, это еще и дамп статистики, тогда вообще можно попытаться до цифр расписать решения оптимизатора) - Если вы делаете предварительные исследования, пытаясь выяснить у сообщества наилучшее решение, сделайте репро ваших предполагаемых данных и структуры (тестовые таблицы, с тестовым заполнением данными наиболее характерного распределения данных в будущем - тут может быть не важна точность и точные значения - важны распределения, порядок, прирост). Чего не следует делать никогда: - Полагаться на тесты основанные на табличных переменных, если только вы не совершенно точно уверены, что принимаете во внимание все их особенности (например, отсутствие статистики) и демонстрируемые эффекты точно такие же, как и на обычных таблицах - Делать тесты с небольшой порцией (часто - не репрезентативных) данных, с верой в то, что это транслируется на реальные данные (в реальности слишком много вовлечено в оптимизаторе условий - число строк, размер строк, параллелизм и т.д.) П.С. И еще, переходите уже хотя бы на 2005 =) Убеждайте руководство. Т.к. даже соблюдай вы все эти условия, лично я бы, все равно, не рискнул давать конкретные советы, т.к. у меня нет под рукой стенда с 2000, чтобы это проверить, и разворачивать его не хочется =) |
||
28 авг 13, 19:50 [14767793] Ответить | Цитировать Сообщить модератору |
TJ001 Member Откуда: от верблюда Сообщений: 428 |
SomewhereSomehow, да, если сделать через left join, то планы становятся почти одинаковыми, в первом варианте даже меньше на одну логическую операцию "Compute Scalar" признаться, про left join я даже не подумал т.к. в моем случае в т2 всегда есть запись с соответствующим ИД, а про то что СУБД об этом не знает я забыл :) спасибо за подсказку!:) |
29 авг 13, 09:23 [14769155] Ответить | Цитировать Сообщить модератору |
TJ001 Member Откуда: от верблюда Сообщений: 428 |
...однако, на реальных данных первый вариант сохраняет свою производительность, а второй захлебывается, ну это я так... это как раз то, о чем шла речь - нужно подбирать под ситуацию :) |
29 авг 13, 09:42 [14769250] Ответить | Цитировать Сообщить модератору |
TJ001 Member Откуда: от верблюда Сообщений: 428 |
ой, не первый, что я говорю?! - второй!, где максимум вычисляется селектом, что в качестве колонки, а не джойна |
29 авг 13, 09:44 [14769257] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
А почему "однако"? Я же вроде сказал, что запросы разные - планы разные - время выполнения разное. Все логично. |
||
29 авг 13, 11:25 [14769911] Ответить | Цитировать Сообщить модератору |
TJ001 Member Откуда: от верблюда Сообщений: 428 |
SomewhereSomehow, ну, это я к тому, что сначала мне показалось, что с левым соединением получается даже лучше, а когда попробовал на реальных данных, то картинка изменилась это только рассуждения "вслух" о своих же экспериментах, а не претензии :) |
29 авг 13, 13:03 [14770561] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |