1. SQL / Говнокод #13925

    −170

    1. 1
    2. 2
    3. 3
    4. 4
    5. 5
    6. 6
    SELECT id FROM (
                SELECT a.id, b.idNOT FROM stable a
                LEFT JOIN 
                   (SELECT idNOT FROM nottable) b
                   ON a.id = b.idNOT ) fromtable
    WHERE idNOT IS NULL

    SQL Server compact не понимает NOT IN.
    Эквивалент будет:
    SELECT id FROM stable
    WHERE id NOT IN (SELECT idNOT FROM nottable)

    Запостил: crastinus, 10 Октября 2013

    Комментарии (92) RSS

    • А что мешает написать NOT(id IN (...)) ? Но вообще вариант с джоином на самом деле лучше. :) Некоторые оптимайзеры некоторых серверов (не буду тыкать пальцем) не соображают что SELECT idNOT FROM nottable можно выполнить только 1 раз...
      Ответить
      • Стоп, что-то я поспешил. Зачем же ещё раз обернули в подзапрос? Или в compact нельзя не выбрать колонку, по которой джойним? Но всё равно тогда лучше бы WHERE занести в подзапрос.
        Ответить
        • И опять слона не заметил. Для третьего подзапроса я вообще объяснения не вижу.
          SELECT a.id FROM stable a LEFT JOIN nottable b ON (a.id = b.idNOT) WHERE b.idNOT IS NULL;
          Ответить
    • > SQL Server compact не понимает NOT IN.
      Там еще много чего... урезано.

      Почему люди себе так усложняют жизнь? Ведь можно просто:
      SELECT a.id FROM stable a
      LEFT JOIN nottable b ON a.id = b.idNOT
      WHERE b.idNOT IS NULL
      Ответить
    • >SQL Server compact не понимает NOT IN.
      а) not in - стрёмная штука, если можно то стоит избегать её использования. в индекс оно не попадает.
      б) подзапросы - плохо
      в) left join на некоторых бд быстрее, так что в этом случае может просто не говно, а даже благо - не даёт написать кривой запрос.
      Выше DBdev предложил вариант.
      Ответить
      • Я его ещё выше предложил. Правда, в одну строчку менее читабельно...
        Но суть не в том: говном были обе попытки автора. :) Первая попадает под ваше а), а вторая... Ну... Утверждение б) не верно, правильно составленные подзапросы там, где они действительно нужны - хорошо. Но в реализации автора они таки не нужны, у него получился очень достойный этого сайта запрос. :)
        Ответить
        • >у него получился очень достойный этого сайта запрос. :)
          потому и запостил;)
          Ответить
      • а) избегать то надо, а почему в индекс то не попадает?
        или это в ms sql не попадает?
        Ответить
        • В ms sql всё норм. Индекс используется.
          Ответить
    • SELECT id FROM stable
      WHERE id NOT IN (SELECT idNOT FROM nottable)

      Один полуебок в подобном стиле заменил join, а потом оказалось, что во внутреннем запросе десятки тысяч идов и мускул тормозил, а потом падал.
      Ответить
      • Я один раз пробовал закодить мелочёвку с mysql, написал запрос с подзапросом. Пока данных было мало, всё было норм, а потом он дох и забирал сервак с собой. С тех пор я отношусь к кроссплатформенности с опаской (к своим медскиллзам тоже).
        Ответить
        • > С тех пор я отношусь к кроссплатформенности с опаской
          >Пока данных было мало,
          Причем тут кроссплатформенность-то?
          Ответить
          • кросссубедешность
            Ответить
            • А что, есть субеде, которые хорошо работают, если подзапрос выдает десятки тысяч строк?
              Ответить
              • есть субд с нормальным планировщиком, которые морщатся, но всё же превращают за пользователя его неоптимальности в оптимальности

                приведи пример, а мы просто посмотрим explain plan
                Ответить
                • SELECT id FROM stable
                  WHERE id IN (SELECT id FROM nottable)

                  Только подзапрос возвращает большое число строк (>10k)
                  Ответить
                  • PostgreSQL:
                    -- приготовим таблички
                    # create table nottable(id serial, a integer);
                    # insert into nottable (a) select generate_series(1, 20000, 1);
                    # create table stable(id serial, b integer);
                    # insert into stable (b) select generate_series(1, 200000, 1);
                    -- подзапрос
                    # select id from stable where id in (select id from nottable);
                    Время: 62,764 мс
                    # explain select id from stable where id in (select id from nottable);
                                                    QUERY PLAN                                
                    --------------------------------------------------------------------------
                     Hash Semi Join  (cost=539.00..4599.00 rows=20000 width=4)
                       Hash Cond: (stable.id = nottable.id)
                       ->  Seq Scan on stable  (cost=0.00..2885.00 rows=200000 width=4)
                       ->  Hash  (cost=289.00..289.00 rows=20000 width=4)
                             ->  Seq Scan on nottable  (cost=0.00..289.00 rows=20000 width=4)
                    -- join
                    # select stable.id from stable join nottable on stable.id = nottable.id;
                    Время: 62,538 мс
                    # explain select stable.id from stable join nottable on stable.id = nottable.id;
                                                    QUERY PLAN                                
                    --------------------------------------------------------------------------
                     Hash Join  (cost=539.00..6624.00 rows=20000 width=4)
                       Hash Cond: (stable.id = nottable.id)
                       ->  Seq Scan on stable  (cost=0.00..2885.00 rows=200000 width=4)
                       ->  Hash  (cost=289.00..289.00 rows=20000 width=4)
                             ->  Seq Scan on nottable  (cost=0.00..289.00 rows=20000 width=4)
                    Устроит?
                    Ответить
                    • Ну и зачем тогда нужен вообще join?
                      Ответить
                      • > Ну и зачем тогда нужен вообще join?
                        Например, чтобы выбирать поля одновременно из джвух и более таблиц:
                        select a.id, a.name, b.name from a join b on a.b_id = b.id
                        Ваш кэп.
                        Ответить
                        • Чем join хуже подзапросов получается?
                          Ответить
                          • Ничем. Когда-то удобней подзапросы (т.к. запись с ними может получиться короче), когда-то джойны (т.к. нужны поля из двух таблиц). Это разные инструменты. Поэтому не спрашивай, пожалуйста, "зачем тогда нужны подзапросы?". :)
                            Ответить
                            • join можно заменить подзапросами, но нас так делать отучали, так вот, теперь мне очень хочется узнать: почему?
                              Ответить
                              • Быстрее было бы погуглить. :)
                                In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.

                                In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.
                                Ответить
                                • Но этот случай туда не попадает?
                                  Ответить
                                  • Сложно сказать. Скорей всего попадает. Просто приведён скорей всего упрощённый до ключевого момента запрос. Подвох тут в том, что ситуация с планом запроса может сильно меняться в зависимости от кучи условий: размера таблиц (высоту/ширину), индексов, других джоинов, группировок и т.п. Т.е. точно понять что будет выгоднее можно только имея ПОЛНЫЙ запрос.

                                    Но реально на практике у меня подзапрос работал быстрее только когда надо было "дырки" в последовательностях значений считать: удалённые ID, пропущенные даты... Там просто с джоином слишком много повторяющихся строк получалось. :)
                                    Ответить
                              • > join можно заменить подзапросами
                                Не всегда, а только если тебе нахер не сдались поля из второй таблицы.

                                Подзапросы учили не юзать готовя вас к встрече с СУБД, которые не умеют их толком оптимизировать ;)

                                А с точки зрения реляционной теории, в таких случаях, если я не ошибаюсь, что in, что exists, что join - все однохуйственно. Главное not in никогда не юзай, у него засада с null'ами и троичной логикой.
                                Ответить
                                • Если бы нам еще обьясняли, почему join лучше in. Впрочем, на лекции у нас ходило человека 4.
                                  Ответить
                                • >реляционной теории
                                  Ты ее в вузе проходил?
                                  Ответить
                                  • > Ты ее в вузе проходил?
                                    Нет конечно. Откуда реляционная теория в нашем Бобруйском Институте?) У нас на предмете, посвященном СУБД, рассказывали о Профессиональной Лисе...
                                    Ответить
                                    • >рассказывали о Профессиональной Лисе
                                      А у нас тип приезжал, постоянно причесывал волосы, но про нормальные формы рассказал. Сети вел придурок зав.кафедры, и расказывал о модуляциях.
                                      Но самое веселое было перед дипломом, ни один из наших кандидатов наук радистов не мог припаять ARM (нужна неделя и десяток микросхем, чтобы заполучить этот скилл). Из моих одногрупников только 2 нормальных, и то один из них одинэсник. Еще один типок также может припаивать армы, но дурачок уже не может использовать их возможности по-серьезному, хотя армы подгоняли много исходников для прошивки. Да, воспоминания.
                                      Ответить
                                      • > припаять ARM
                                        60 ваттным паяльником с жалом в 5мм? :) Феном на хорошо залуженную плату то они влет садятся ;)
                                        Ответить
                                        • Феном я их сжигал. Тот другой типок делал все это дело утюгом. Ложил плату с армом на утюг и снимал, когда видел, что припой плавится. Подход достойный этого сайта)).
                                          А ты где с ними встречался?
                                          Ответить
                                          • > А ты где с ними встречался?
                                            Да игрался для души с ARM и AVR :)

                                            > Тот другой типок делал все это дело утюгом.
                                            Жесть. А остальные детали в это время уплывали со своих мест? :) Самое зверство которое я учинял с электроникой - распайка материнки над печкой, которую топят углём... SMDятина падала и вспыхивала красивыми искорками, а я в перчатках варежках дергал разъемы, полевики, и прочие крупные и полезные вещи... Суровый сибирский демонтаж ;)
                                            Ответить
                                            • >Жесть. А остальные детали в это время уплывали со своих мест? :)
                                              Остальное приходилось делать рассыпухой (её легко впаивать из-за малоножья) и впаивать после этой зверской операции.
                                              Ответить
                                              • > Остальное приходилось делать рассыпухой (её легко впаивать из-за малоножья) и впаивать после этой зверской операции.
                                                Хм. LispGovno == crastinus?
                                                Ответить
                                                • А я подумал LispGovno=МойРыжийДруг. Но рыжий идиот, потому это исключено.
                                                  Хотя, конечно,LispGovno тоже может быть рыжим, но не моим знакомым.
                                                  Ответить
                                            • Прожектором (хуйня на штанге, которыми по ночам освещают памятники культуры) же тру кошерно. Так процессоры выпаивают.
                                              Ответить
                                            • Хоть одна микросхема выжила?
                                              Ответить
                                              • > Хоть одна микросхема выжила?
                                                А хрен знает :) Полевики живые, микрухи, которые ими управляют - тоже. А остальные чипы проверить было нечем, да и незачем...
                                                Ответить
                  • Ну и разность множеств до кучи:
                    -- вариант ОП'а
                    # select id from stable where id not in (select id from nottable);
                    Время: 104,370 мс
                    # explain select id from stable where id not in (select id from nottable);
                                                  QUERY PLAN                              
                    ----------------------------------------------------------------------
                     Seq Scan on stable  (cost=339.00..3724.00 rows=100000 width=4)
                       Filter: (NOT (hashed SubPlan 1))
                       SubPlan 1
                         ->  Seq Scan on nottable  (cost=0.00..289.00 rows=20000 width=4)
                    -- вариант от DBdev
                    # select stable.id from stable left join nottable on stable.id = nottable.id where nottable.id is null;
                    Время: 119,246 мс
                    # explain select stable.id from stable left join nottable on stable.id = nottable.id where nottable.id is null;
                                                    QUERY PLAN                                
                    --------------------------------------------------------------------------
                     Hash Anti Join  (cost=539.00..6199.00 rows=180000 width=4)
                       Hash Cond: (stable.id = nottable.id)
                       ->  Seq Scan on stable  (cost=0.00..2885.00 rows=200000 width=4)
                       ->  Hash  (cost=289.00..289.00 rows=20000 width=4)
                             ->  Seq Scan on nottable  (cost=0.00..289.00 rows=20000 width=4)
                    Ответить
                    • А если на id индексы навесить?
                      Ответить
                      • Верхние два примера (подзапрос и join) ускорились до 26-29мс, на нижние два примера не повлияло. Видимо постгрес не умеет юзать индекс наоборот, или у меня руки кривые :)
                        Ответить
                        • для запроса ОП оракл как раз индекс заюзал и делал по нему not in
                          потому то я и переспросил, что может дело в мс скл

                          а по твоей ситуации - 1) твои таблицы свежие, может в постгресе тоже, как в оракле, надо сделать analyze table для достоверных тестов?, 2) в таблице особо данных не хранится, вот ты навесь по килобайту данных на строку, чтобы индекс было ещё выгодней юзать
                          Ответить
                          • > вот ты навесь по килобайту данных на строку
                            Навесил по три колонки с char(255), сделал analyze... Планы не поменялись. Не хочет он юзать индексы для разности множеств, и хеширует вторую табличку заново...

                            Кажется понял в чем причина... В первых двух запросах он индексы использует в merge (как я понимаю тупо бежит по обоим индексам и выбирает общее).

                            В запросах на разность множеств же ему нужно проверять отсутствие записи во второй таблице для каждой записи из первой. И, видимо, хеш для этого подходит лучше чем b-tree, поэтому он и хеширует айдишки вместо использования индекса. Так что надо увеличить количество записей в nottable, чтобы затраты на построение хеша перевесили...
                            Ответить
                            • думаю, тут должна быть немного другая постановка вопроса
                              очевидно, что в подзапросе idnot должен быть fk на первую таблицу (иначе какого черта вообще вычитать две несвязанные друг с другом колонки)
                              оракл не делает лишнего индекса для колонки fk, вот, видимо и приходится ему только юзать натуральный индекс (pk) от stable
                              ...сейчас уже лень открывать опять удаленный стол на работу, чтобы вставить план запроса от ОП
                              Ответить
                              • На трех миллионах записей в nottable он наконец-то посчитал индекс целесообразным (160мс):
                                # explain select stable.id from stable left join nottable on stable.id = nottable.id where nottable.id is null;
                                                                         QUERY PLAN                                         
                                --------------------------------------------------------------------------------------------
                                 Merge Anti Join  (cost=0.00..35833.72 rows=1 width=4)
                                   Merge Cond: (stable.id = nottable.id)
                                   ->  Index Scan using stable_id on stable  (cost=0.00..26994.27 rows=200000 width=4)
                                   ->  Index Scan using nottable_id on nottable  (cost=0.00..89351.34 rows=3020007 width=4)
                                Вариант же с select id from stable where id not in (select id from nottable) скатился в говнище (я его не дождался):
                                # explain select id from stable where id not in (select id from nottable);
                                                                   QUERY PLAN                                   
                                --------------------------------------------------------------------------------
                                 Seq Scan on stable  (cost=61067.08..4807394540.08 rows=100000 width=4)
                                   Filter: (NOT (SubPlan 1))
                                   SubPlan 1
                                     ->  Materialize  (cost=61067.08..101590.15 rows=3020007 width=4)
                                           ->  Seq Scan on nottable  (cost=0.00..47724.07 rows=3020007 width=4)
                                Ответить
                              • One more nasty side effect is that using a subplan is the only method PostgreSQL‘s optimizer can use for NOT IN constructs.

                                Since PostgreSQL cannot flush a hashed subplan onto the disk, it will estimate the subquery size, and if it decides that it will not fit into work_mem, it will resort to using a mere subplan which will be either executed for each row from t_left, or materialized and the rows will be searched for in a loop.

                                This can be very bad since the optimizer will just change the plan as t_right will overgrow a certain limit and one day the query will just become very slow without any apparent reason.


                                Печалька ;)

                                Вывод: в постгресе not in работает быстро ровно до тех пор, пока хеш входит в память. А просто in, join, exists/not exists и решение от DBdev с left join + not null планируются и исполняются вполне адекватно даже при 3кк различных id в nottable.
                                Ответить
                          • Добавил пару миллионов записей в nottable. Создал ему hash индекс по полю id. Все равно не хочет скотина ;)
                            Ответить
                  • > explain plan for 
                    select f.id from foo f
                      where f.id in (select foo_id from bar);
                    ------------------------------------------------------------------------------------
                    | Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT   |               |   175 |  1400 |    21   (5)| 00:00:01 |
                    |*  1 |  HASH JOIN SEMI    |               |   175 |  1400 |    21   (5)| 00:00:01 |
                    |   2 |   INDEX FULL SCAN  | SYS_C007581   |   180 |   720 |     1   (0)| 00:00:01 |
                    |   3 |   TABLE ACCESS FULL| BAR           | 16792 | 67168 |    19   (0)| 00:00:01 |
                    ------------------------------------------------------------------------------------
                    > explain plan for
                    select distinct f.id from foo f
                      join bar b on (b.foo_id = f.id)
                    ------------------------------------------------------------------------------------
                    | Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
                    ------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT   |               |   175 |   700 |    21  (10)| 00:00:01 |
                    |   1 |  HASH UNIQUE       |               |   175 |   700 |    21  (10)| 00:00:01 |
                    |   2 |   TABLE ACCESS FULL| BAR           | 16792 | 67168 |    19   (0)| 00:00:01 |
                    ------------------------------------------------------------------------------------
                    Ответить
                    • > explain plan for
                      select distinct foo_id from bar;
                      ------------------------------------------------------------------------------------
                      | Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
                      ------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT   |               |   175 |   700 |    21  (10)| 00:00:01 |
                      |   1 |  HASH UNIQUE       |               |   175 |   700 |    21  (10)| 00:00:01 |
                      |   2 |   TABLE ACCESS FULL| BAR           | 16792 | 67168 |    19   (0)| 00:00:01 |
                      ------------------------------------------------------------------------------------

                      т.е. везде косты получились одинаковые

                      на самом деле всю эту портянку я привёл лишь в качестве примера того, что в нормальной субд должен быть нормальный оптимизатор - выкидывающий подзапросы, если их можно выкинуть, знающий состав индексов и умеющих их к месту применить и т.д.
                      mysql - это явно не нормальная субд
                      Ответить
                      • ой тут уже наспамили, пока я слоупочил...
                        Ответить
                        • Хорошо нарисовал таблицу. Молодец.
                          Ответить
                          • это оракл рисует
                            Ответить
                            • Ох, блядь, до сих пор вспоминаю их шелл. Они даже из 80-х еще не вылезли.
                              Ответить
                              • сказал человек, часом назад жаловавшийся на копеечные 10к строк в подзапросе
                                у меня тут _таблицы_ по 300гб остались в наследство в субд, не вылезшей из 80х, то ли дело MyISAM
                                Ответить
                                • Тебе сейчас приходится работать с СУБД из 80-х?

                                  > копеечные 10к строк в подзапросе
                                  Уже на них стало плохо. И?
                                  Ответить
                                  • > И?
                                    Ну если "субд из 80х" работает быстрее и стабильнее "субд 21го века", и не залипает на жалких 10к строк в подзапросе, то у меня для тебя плохие новости :)
                                    Ответить
                                    • Не знаю, что за питухи это плюсуют, раз джва плюса, то наверно коллеги борманда по шарашке. Если ты имел в виду оракел, то у него оттуда не вылез шелл, про другое не знаю. Шелл действительно уебище еще то. Если ты знаешь какую-нибудь субд из 80-х, которая работает "быстрее и стабильнее "субд 21го века"", может, ты мне ее покажешь?
                                      Ответить
                                      • И тут 2 минуса. Точно коллеги по шараге.
                                        Ответить
                                        • Эти коллеги из шаражки и меня дико минусуют, черт бы их подрал.
                                          Ответить
                                          • а не похуй?
                                            Ответить
                                            • 50%50
                                              как комариный укус в анус.
                                              Ответить
                                            • как ты мол такое сказать! рейтинг - последняя светлая капля в этом мире тьмы
                                              Ответить
                                          • >Эти коллеги из шаражки и меня дико минусуют
                                            Однокласники?
                                            Ответить
                                            • Впопуклассники.

                                              Все, адью, засиделся я с вами. Парсер давно написан. Подрочить и спать. Хотя можно и просто спать, я подумаю.
                                              Ответить
                              • Я оракловый шелл не видел, но все же спрошу. Чем шелл мускуля лучше?
                                Ответить
                                • P.S. В консольке мускуля бесит выход по ctrl-c. Я просто после баша и постгреса привык жать ctrl-c для отмены набранной наполовину и еще не запущенной команды.
                                  Ответить
                                  • на самом деле анонимб прав
                                    зачем пользоваться консольным клиентом, если есть нормальные инструменты
                                    в частности, все что я поднасрал в топик, я сделал через sql developer

                                    однако, sql developer имеет фичу нагло виснуть при открытии 50+ МБ .sql файлов, но это уже совсем другая история
                                    Ответить
                                    • А ещё sql developer имеет свойство намертво зависать и не закрываться при потере соединения. В остальном тула годная.
                                      Ответить
                                    • SQL вумудщзук может выдавать таблицы в ascii графике? o_O
                                      Ответить
                                      • > ascii
                                        dbms_xplan же

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

                                  sql developer нагуглил и пользовался им только ради этого.
                                  Ответить
              • Ты наверное sqlLite или мускул используешь?
                Ответить
                • Тогда сайт юзал мускул, ему было достаточно.
                  Ответить
                  • постгри скл бери
                    Ответить
                    • Ну там проблема была в том, что говнокодер не знал про join. Но в мускуле и так есть свои прелести, та же сортировка за O(n**2) в MyISAM.
                      Ответить
                      • Зато MyISAM умеет делать select count(*) from table без where за O(1). И не забивает голову программиста дурацкими понятиями типа транзакций и ACID.
                        Ответить
                        • Которые в моем случае и не нужны были особо.

                          Все-таки интересно, почему эти полуебки выбрали такую сортировку?
                          Ответить
              • Запросы похожего типа прекрасно жевались DB2, но на mysql по не очень логичной причине таки вешали всё наглухо.
                При этом, на DB2 объёмы были вполне промышленными (ну и сервера не бытовые, конечно), а вот для обрушения mysql хватило десятков тысяч записей.
                Ответить
      • Старый Мускл может и упадет. Это исправили в 5.5 или 5.6
        Не исправили.
        Ответить
        • Ну я условно сказал "падал", я не помню уже, то ли там падало что-то, то ли лимиты срабатывали.
          Ответить
          • Я про корявую оптимизацию IN в MySQL.
            SELECT * FROM tabl WHERE id IN (...)
            Превращается в
            SELECT * FROM tabl WHERE EXIST(...).
            Ответить
            • > Превращается в SELECT * FROM tabl WHERE EXIST(...).
              Что в этом плохого? У нормального оптимизатора что exists, что in одинаково быстры.
              Ответить
    • Знатный срач развели)).
      Исходный запрос был не хай лоад, а примерно такой
      SELECT id FROM stable
      WHERE id NOT IN
      (SELECT idNOT FROM nottable1
      UNION
      SELECT idNOT FROM nottable2
      .....)
      Юнион удалил бы повторяющиеся индексы, и никаких бы проблем не было (даже десятка индексов не выбрал бы).
      Нагруженные системы это другое дело. Да и какая нагрузка будет в MS-эквиваленте sqlite?
      Ответить
    • показать все, что скрытоОТСОСУ У КАВКАЗЦА, ДАМ В ЖОПУ ТАДЖИКУ, ВЫЛИЖУ АНУС УЗБЕКУ vk.com/id29482‎
      Ответить

    Добавить комментарий