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

    −50

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    use [oktell]
    select 
    	(case when SUBSTRING([Call_status_note],1,1)='1' 
    	then 'Расписание специалиста, '	else ''	end)
    	+(case when SUBSTRING([Call_status_note],2,1)='1' 
    	then 'Стоимость услуг, ' else '' end)
    	+(case when SUBSTRING([Call_status_note],3,1)='1' 
    	then 'Адрес, ' 	else '' end)
    	+(case when SUBSTRING([Call_status_note],4,1)='1' 
    	then 'Вакцина, 'else '' end)
    	+(case when SUBSTRING([Call_status_note],5,1)='1' 
    	then 'Эл.почта, 'else '' end)
    	+(case when SUBSTRING([Call_status_note],6,1)='1' 
    	then 'Перевод звонка в офис и др., 'else '' end)
    	+(case when SUBSTRING([Call_status_note],7,1)='1' 
    	then 'Стоматология, 'else '' end)
    	+(case when SUBSTRING([Call_status_note],8,1)='1' 
    	then 'Неверная информация, 'else '' end)
    	+(case when SUBSTRING([Call_status_note],9,1)='1' 
    	then 'Ведение беременности, 'else '' end)
    	+(case when SUBSTRING([Call_status_note],10,1)='1' 
    	then 'Услуги, операции, анализы, 'else '' end)
    	+(case when SUBSTRING([Call_status_note],11,1)='1' 
    	then 'Вопросы скорой помощи, 'else '' end)
    	+(case when SUBSTRING([Call_status_note],12,1)='1' 
    	then 'Звонок сорвался, 'else '' end) [Заметка]
    from [dbo].[AbonentsMedPark]

    Потребовали максимально быстро реализовать функциолнал. Есть текстовое поле вида "111111000000", надо по флагам вывести текст.
    Конкретно с самим MSSQL работал мало. Я предлагал использовать функцию, мне отказали. Возможно ли реализовать этот запрос более оптимально?

    Запостил: Rijen, 27 Января 2016

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

    • Можно теми же сабстрингами заполнить временную таблицу, и потом на нее джоин. Возможно короче будет.
      Ответить
      • зачем временную?
        нормальный справочник сделать
        id, (class/type/tag?), bit_pos, title, (sort_order?)
        индекс по class
        и джойниться с ней по (bar.class = 'abonentsmedpark' and substring(foo.my_bit_array, bar.bit_pos, 1) = '1'), потом клеить результат джойна (наверняка в m$$ql должен быть аналог listagg, на крайняк рекурсивный запрос сделать)
        и короче, и проще, и нет хардкода
        Ответить
        • >и джойниться с ней по (bar.class = 'abonentsmedpark' and substring(foo.my_bit_array, bar.bit_pos, 1) = '1'),
          не лучший вариант.
          select *
          from tbl
          where field = '100500'

          select *
          from tbl
          where substring(field,1,6) = '100500'

          если в первом варианте cost составляет 0,003 то для второго запроса 8,35.
          для Sql Server очень тяжело даются вычисляемые предикаты.

          я бы сделал как defacate-plusplus советует, только сделал бы отдельную таблицу, где будет храниться id атрибута, запись id записи из таблицы, которая будет предрассчитана, и будет склеиваться через stuff((select ', ' + value from tbl where blablabla for xml path ('')),1,1,'')
          Ответить
          • > для Sql Server очень тяжело даются вычисляемые предикаты.
            лол, серьезно?
            если тут есть проблемы с производительностью или требуется перемолотить миллион записей, то нахера вообще страдать ерундой и хранить в битовой маске, когда можно хранить отдельными сраными полями или (кому вообще в голову может прийти такая бредовая мысль!) в отдельной сущности my_entity_traits (entity_id, trait_id, value)
            Ответить
            • конечно серьезно, их же вычислять надо

              select *
              from tbl1 as w
              join tbl2 as a
              on w.id = a.id

              select *
              from tbl1 as w
              join tbl2 as a
              on substring(w.id,1,12) = substring(a.id,1,12)

              фактически, cost 22% для первого запроса , а для второго 78%

              в первом случае он сканирует два ключа, и через nested loops inner join (1% cost от запроса) соединяет их
              во втором же случае он параллелит на 8 потоков чтение ключа из таблицы, вычисляет значение, после этого соединяет результаты потоков, и уже через hash match inner join (63% cost от запроса) в потоках так же матчит их, после этого объединяет результат потоков..... короче, лучше так не делать.
              мне кажется, это скорее особенности СУБД. как показывает практика, в oracle запросы типа

              select *
              from tbl
              where id in ( select id from tbl2)

              работают хуже чем

              select *
              from tbl t1
              where exists( select 1 from tbl2 t2 where t1.id = t2.id)

              хотя по сути, оптимизитор должен понимать, что запросы делают одно и то же
              Ответить
              • вышел разговор глухого с немым

                как можно косты мерять процентами?
                как можно мерять запросы, где джойнятся первичные ключи?

                я перечитал твой первый ответ и понял что ты хотел сказать
                тот же ответ я и сам предложил в следующем посте

                сравнение
                cross join attrs r 
                where substring(l.some_short_text, r.pos, 1) = 1
                и
                join attrs on (l.attr_id = r.id)
                я предлагаю оставить в качестве домашней работы (ты не забывай, что индекса ни по some_short_text и по attr_id у тебя нет)

                полагаю, разница должна быть даже не в разы
                а битовую маску же они неспроста заебенили в БД (есть немного причин на это, но они есть)
                Ответить
                • да очень легко, sql server может 100500 select стейтментов выполнять в одном запросе, и возвращать несколько рекордсетов. если в оракле это процедура, и онанизм с курсорами, то там это проще. и когда ты смотришь план запроса, где у тебя 2 select стейтмента, то он выводит план запроса по каждому, и оценивает, соотношение их костов в запросах. например http://4.bp.blogspot.com/-xIKczZAQXeY/T52EZPBqhPI/AAAAAAAAB7g/JyVUzt17sQk/s640/i1.png
                  как видно в примере на скриншоте, like field 'a%' работает быстрее в разы чем substring(field,1,1) = 'a'

                  на всякий случай, вот дескрипшены https://technet.microsoft.com/en-us/library/ms175913(v=sql.105).aspx
                  Ответить
                  • > like field 'a%' работает быстрее в разы чем substring(field,1,1) = 'a'
                    только ты говоришь field, а показываешь план где field у тебя индекс!!
                    сделай уже по честному
                    кроме того, раз уж ты любишь ходить по индексу вместо таблицы, попробуй сделать не
                    id like 'a%' vs substr(id, 1, 1) = 'a'
                    а
                    id like '_a%' vs substr(id, 2, 1) = 'a'
                    - стало ли иначе? а третий символ?
                    Ответить
                    • да одно и то же по сути получается, индекс он не использует, запросы будут различаться только на вычисление значений, которое меньше 1% коста
                      Ответить
                • в общем
                  сделал я домашнюю работу за вас
                  http://pastebin.com/3Vva1ey5
                  под mssql мне было лень адаптировать, оставлю это заинтересованным читателям

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

                  да, получаем ограничение на число атрибутов, да, надо разумно подходить к такому инструменту

                  кроме того, я уверен, что и под m$$ql результаты совпадут - магии не бывает, cpu операция над уже прочитанной из блока строкой ничем не хуже, чем хеш джойн, или нестед лупс
                  Ответить
                  • Эмм. Сэкономьте вы уже ресурсы, используйте бинарную маску, а не substring.

                    Тем временем, я пока не понял что там происходит, но, честно, усердно стараюсь понять.
                    Ответить
                    • Я думаю над каким-нибудь вариантом вроде
                      Создать таблицу типо:
                      2 Вариант раз
                      4 Вариант 2
                      8 Вариант 3
                      ...etc

                      А потом сделать так:

                      list((select status from statuses where id in(mask ans 2,mask and 4,mask and 8...etc)),', ')
                      Ну, опять таки добавится ещё coalesce, или как оно в MSSQL называется.

                      Немногим оптимальнее, пока не придумал как не использовать кучу mask and X
                      Ответить
                      • А, ну да, просто join-им таблицу с аттрибутами.. Там много буков написали, что совсем запутали..

                        *Ушел гуглить книжки про данные, почему они могут быть ненормальными, и что с ними вообще можно делать*
                        Ответить
                      • > не использовать кучу mask and X
                        ровно та же система, что и с substr в моем примере
                        вместо substring(data.value, attr.bitpos, 1) = '1' надо data.value & attr.mask > 0

                        но для начала ответь себе на вопрос - нахуя тебе это уплотнение? действительно ли тебе нужна денормализация на ровном месте? экономишь ли ты тут гигабайты детальной статистики?
                        Ответить
                    • > Сэкономьте вы уже ресурсы, используйте бинарную маску, а не substring.
                      в это сложно поверить, но это так - операции несложного воздействия на уже извлеченные из БД данные занимают ничтожную долю времени по сравнению с их поиском, ожиданием блокировок, чтением с диска, помещением в кеш, слежением за изоляцией транзакции и многими другими вещами, по сравнению с которыми обращение к символу по индексу - это 0.0%

                      наиболее популярный джойн двух множеств - hash join. Посчитать хеш уже на порядок более трудоемкая операция, чем сраный substring, но никто не плачется об этом

                      так что экономия на спичках, но попробовать стоит
                      (я уж не говорю, что в каждой субд могут быть свои "инновационные" идеи по организации бинарного &, т.к. это ну очень редкая для sql операция)
                      Ответить
                      • Ммм. Сомневаюсь что бинарные операции редки в SQL.
                        Тот же вешеуказанный хэш-джойн. Насколько я знаю все хэши, это не что иное как результат бинарных операций.

                        В общем-то да, экономия на спичках, тут вы правы. Как бы там не было, разница для максимального адекватного количества данных в этой базе составляет меньше секунды.

                        А ведь я умею писать запросы которые работают по несколько минут - вот над ними надо думать.
                        Ответить
                        • > бинарные операции
                          быстро нагуглишь операцию and в оракле? эффективно ли она работает?
                          а операцию or? а битовый сдвиг? удобно ли лезть в пакет, заниматься преобразованием в raw и обратно?
                          Ответить
    • Тут гуляло НЛО
      Ответить

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