Модераторы: skyboy
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> оптимизация запроса, как лучше исправить? 
:(
    Опции темы
insy
Дата 12.11.2009, 12:10 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 140
Регистрация: 21.9.2008
Где: Saratov

Репутация: нет
Всего: нет
вот запрос, грузиться примерно минуту, подскажите пожалуйста как лучше его оптимизировать!Я понимаю, что вся проблема в JOIN'ах, как от них избавиться?

Прошу прощения за объем.

Код

SELECT
                    SQL_CALC_FOUND_ROWS
                    U.pk_id AS user,
                    GDS.ft_ogrn AS ogrn,
                    GDS.fc_short_lpu_name AS short_lpu_name,
                    MAUF.fn_internal_subordinating_code AS lpu_code_inside_region,
                    DC.fc_name AS dept_name,
                    POST_CODE.fc_post AS post_medic_name,
                    POST_CODE.fc_code AS post_medic_code,
                    POST_SECTION_COUNTER.oms_summ AS omsx,
                    
                    POST_DEP_COUNTER.oms_summ as oms,
                    POST_DEP_COUNTER.state_norm AS state_norm,
                    TAKE_POST_DEP_COUNTER.oms_summ AS oms_summ,
                    TAKE_POST_DEP_COUNTER.oms_summ AS specilists_cnt,
                    
                    POST_SECTION_COUNTER.state_norm AS rate_by_state_norm,
                    TAKE_POST_COUNTER.oms_summ AS omsxi,
                    TAKE_POST_COUNTER.specilists_cnt AS count_pers,
                    TFOMS.fc_code AS tfoms_code,
                    SKFOMS_NAMES.fc_filial_name AS filial_name,
                    SKFOMS_CODES.fc_territory_okato_code AS filial_id
               FROM
                       tusers U
                           LEFT JOIN tmain_and_under_foundings MAUF ON MAUF.fk_user = U.pk_id
                           LEFT JOIN tgeneral_description_section GDS ON GDS.fk_user = U.pk_id
                           LEFT JOIN tprofile_department_section PDS ON PDS.fk_mauf = MAUF.pk_id
                           LEFT JOIN tdic_department_codes DC ON PDS.fk_department = DC.pk_id
                           LEFT JOIN tpost_section PS ON PS.fk_code_department = PDS.pk_id
                        LEFT JOIN tdic_code_post POST_CODE ON PS.fk_code_medic_post = POST_CODE.pk_id
                           LEFT JOIN ttake_post_section_tbl1 TPS1 ON TPS1.fk_department_code = PDS.pk_id
                                                                    AND TPS1.fk_mauf = PS.fk_mauf
                                                                    AND TPS1.`fk_code_medic_post` = PS.`fk_code_medic_post`
                           LEFT JOIN ttake_post_section_tbl2 TPS2 ON TPS2.fk_tabel_number = TPS1.pk_id
                           LEFT JOIN tconnective_lpu_users TFOMS ON TFOMS.fk_user = U.pk_id
                           LEFT JOIN tdic_filial_skfoms_codes SKFOMS_CODES ON GDS.fk_zone_code = SKFOMS_CODES.pk_id
                               LEFT JOIN tdic_filial_skfoms SKFOMS_NAMES ON SKFOMS_CODES.fk_dic_filial_skfoms = SKFOMS_NAMES.pk_id
                       
                   LEFT JOIN (
                       SELECT
                            SUM(__PS.fn_oms) AS oms_summ,
                            SUM(__PS.fn_rate_by_state_norm) AS state_norm,
                            __PS.`pk_id` as id
                       FROM
                           tusers U

                               LEFT JOIN tmain_and_under_foundings MAUF ON MAUF.fk_user = U.pk_id
                               LEFT JOIN tgeneral_description_section GDS ON GDS.fk_user = U.pk_id
                               LEFT JOIN tpost_section __PS ON __PS.fk_mauf = MAUF.pk_id
                               LEFT JOIN tdic_code_post POST_CODE ON __PS.fk_code_medic_post = POST_CODE.pk_id
                           GROUP BY
                            __PS.fk_user,
                               __PS.fk_mauf,
                            __PS.fk_code_department,
                            __PS.`fk_code_medic_post`
                           )POST_DEP_COUNTER ON PS.pk_id = POST_DEP_COUNTER.id
                       
                  LEFT JOIN (
                       SELECT
                            U.pk_id,
                            _TPS1.fk_code_medic_post,
                            _TPS2.pk_id as id,
                             SUM(_TPS2.fn_oms) AS oms_summ,
                             COUNT(_TPS2.pk_id) AS specilists_cnt

                                FROM
                           tusers U
                               LEFT JOIN tmain_and_under_foundings MAUF ON MAUF.fk_user = U.pk_id
                            
                            LEFT JOIN tprofile_department_section PDS ON PDS.fk_mauf = MAUF.pk_id
                            
                            LEFT JOIN tpost_section __PS ON __PS.`fk_code_department` = PDS.`pk_id`
                            
                            LEFT JOIN ttake_post_section_tbl1 _TPS1 ON _TPS1.`fk_department_code` = PDS.pk_id
                                                                    AND _TPS1.fk_mauf = __PS.fk_mauf
                                                                    AND _TPS1.`fk_code_medic_post` = __PS.`fk_code_medic_post`

                               LEFT JOIN ttake_post_section_tbl2 _TPS2 ON _TPS2.fk_tabel_number = _TPS1.pk_id
                            LEFT JOIN tdic_code_post POST_CODE ON _TPS1.fk_code_medic_post = POST_CODE.pk_id
                           GROUP BY
                            U.pk_id
                            ,MAUF.pk_id
                            ,_TPS1.`fk_code_medic_post`

                  ) TAKE_POST_DEP_COUNTER ON TPS2.pk_id = TAKE_POST_DEP_COUNTER.id
                  
                  LEFT JOIN (
                       SELECT
                             SUM(__PS.fn_oms) AS oms_summ,
                             SUM(__PS.fn_rate_by_state_norm) AS state_norm,
                             __PS.fk_code_department
                       FROM
                           tusers U
                               LEFT JOIN tmain_and_under_foundings MAUF ON MAUF.fk_user = U.pk_id
                               LEFT JOIN tgeneral_description_section GDS ON GDS.fk_user = U.pk_id
                               LEFT JOIN tpost_section __PS ON __PS.fk_mauf = MAUF.pk_id
                           GROUP BY
                                 __PS.fk_code_department,
                                 __PS.fk_mauf
                       
                  ) POST_SECTION_COUNTER ON PS.fk_code_department = POST_SECTION_COUNTER.fk_code_department
                  LEFT JOIN (
                       SELECT
                             SUM(_TPS2.fn_oms) AS oms_summ,
                             COUNT(_TPS2.pk_id) AS specilists_cnt,
                             _TPS1.fk_department_code,
                             U.pk_id
                       FROM
                           tusers U
                               LEFT JOIN tmain_and_under_foundings MAUF ON MAUF.fk_user = U.pk_id
                               LEFT JOIN tgeneral_description_section GDS ON GDS.fk_user = U.pk_id
                               LEFT JOIN ttake_post_section_tbl1 _TPS1 ON _TPS1.fk_mauf = MAUF.pk_id
                               LEFT JOIN ttake_post_section_tbl2 _TPS2 ON _TPS2.fk_tabel_number = _TPS1.pk_id

                       WHERE not isnull(_TPS2.pk_id )
                               AND U.pk_id IN (198,204)
                           GROUP BY
                                 U.pk_id,
                              _TPS1.`fk_department_code`
                  ) TAKE_POST_COUNTER ON PS.fk_code_department = TAKE_POST_COUNTER.fk_department_code
      WHERE
           U.pk_id IN ($lpus)
                
PM MAIL   Вверх
MoLeX
Дата 12.11.2009, 12:19 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Местный пингвин
****


Профиль
Группа: Модератор
Сообщений: 4076
Регистрация: 17.5.2007

Репутация: 0
Всего: 140
insy, с этим запросом лучше уж в Базы данных -> Составление SQL-запросов

Добавлено через 11 секунд

M
MoLeX		Модератор: перенес



--------------------
Amazing  smile 
PM MAIL WWW ICQ   Вверх
insy
Дата 12.11.2009, 14:12 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 140
Регистрация: 21.9.2008
Где: Saratov

Репутация: нет
Всего: нет
как можно по максимуму сократить время на выполнение запросов, которые выполняются в join'e?
PM MAIL   Вверх
Zloxa
Дата 12.11.2009, 14:19 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161
Цитата(insy @  12.11.2009,  14:12 Найти цитируемый пост)
как можно по максимуму сократить время на выполнение запросов, которые выполняются в join'e? 

Откуда вы знаете что оно не минимально?


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
insy
Дата 12.11.2009, 14:26 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 140
Регистрация: 21.9.2008
Где: Saratov

Репутация: нет
Всего: нет
Я не знаю, поэтому и спрашиваю, на что обратить внимание и где возможна оптимизация?
PM MAIL   Вверх
Zloxa
Дата 12.11.2009, 14:37 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161
если сделать так:
Код

                   LEFT JOIN (
                       SELECT
                            SUM(__PS.fn_oms) AS oms_summ,
                            SUM(__PS.fn_rate_by_state_norm) AS state_norm,
                            __PS.`pk_id` as id
                       FROM
                           --tusers U
                           --   LEFT JOIN tmain_and_under_foundings MAUF ON MAUF.fk_user = U.pk_id
                          --     LEFT JOIN tgeneral_description_section GDS ON GDS.fk_user = U.pk_id
                           /*    LEFT JOIN */tpost_section __PS-- ON __PS.fk_mauf = MAUF.pk_id
                           --    LEFT JOIN tdic_code_post POST_CODE ON __PS.fk_code_medic_post = POST_CODE.pk_id
                           GROUP BY
                            __PS.fk_user,
                               __PS.fk_mauf,
                            __PS.fk_code_department,
                            __PS.`fk_code_medic_post`
                           )POST_DEP_COUNTER ON PS.pk_id = POST_DEP_COUNTER.id

Как по вашему, что нибудь должно измениться?


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
insy
Дата 12.11.2009, 14:42 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 140
Регистрация: 21.9.2008
Где: Saratov

Репутация: нет
Всего: нет
Я не пойму, я смешной вопрос задал? Так скажите что не так... 
Ваш метод кстати очень качественно оптимизирует запрос...  smile  
PM MAIL   Вверх
Zloxa
Дата 12.11.2009, 14:48 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161
Цитата(insy @  12.11.2009,  14:42 Найти цитируемый пост)
очень качественно оптимизирует запрос

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


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
insy
Дата 12.11.2009, 14:56 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 140
Регистрация: 21.9.2008
Где: Saratov

Репутация: нет
Всего: нет
Это была ирония!
Огород нагорожен умышленно (у меня есть несколько полей, которые формируются именно благодаря этим самым запросам...) и к сожалению без него никак... 
PM MAIL   Вверх
Zloxa
Дата 12.11.2009, 15:04 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161
Цитата(insy @  12.11.2009,  14:56 Найти цитируемый пост)
у меня есть несколько полей, которые формируются именно благодаря этим самым запросам

т.е Вы таки привели упрощенный, - не полный, запрос?



--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
insy
Дата 12.11.2009, 15:07 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 140
Регистрация: 21.9.2008
Где: Saratov

Репутация: нет
Всего: нет
нет, это полный запрос. Поля с количествами которые считаются в этих запросах.
PM MAIL   Вверх
Zloxa
Дата 12.11.2009, 16:06 (ссылка) | (голосов:2) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161
Цитата(insy @  12.11.2009,  15:07 Найти цитируемый пост)
нет, это полный запрос. Поля с количествами которые считаются в этих запросах. 

Поля с количеством в этом подзапросе не считаются -  считаются суммы. Я не понимаю о чем вы говорите.

В этом подзапросе никакие поля кроме полей таблицы __PS не используются. По названию полей, я так подозреваю users и mauf выступают в качестве подстановочных справочников, отсюда и возникает вопрос имеет ли смысл объединение, чего вы этим добиваетесь?  Ваш ответ мне не понятен.

Есть у меня еще мыслишка касательно того что ваш под запрос вообще будет работать не верно. Очень уж меня смущает отсутствие pk_id в секции group by. Не, я понимаю что в маське так можно, только вот есть у меня сомнение что вы получите то, чего пытаетесь добиться.

По хорошему расписали бы струткру данных, объяснили бы отношения, да суть желаемого




--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
insy
Дата 17.11.2009, 12:52 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Шустрый
*


Профиль
Группа: Участник
Сообщений: 140
Регистрация: 21.9.2008
Где: Saratov

Репутация: нет
Всего: нет
EXPLAIN выглядит так...

Код

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    U    index    PRIMARY    PRIMARY    4        221    Using where; Using index; Using temporary; Using filesort
1    PRIMARY    MAUF    ref    fk_user    fk_user    5    skfoms.U.pk_id    3    ""
1    PRIMARY    GDS    ref    fk_user    fk_user    5    skfoms.U.pk_id    1    ""
1    PRIMARY    PDS    ref    fk_mauf    fk_mauf    5    skfoms.MAUF.pk_id    3    ""
1    PRIMARY    DC    eq_ref    PRIMARY    PRIMARY    4    skfoms.PDS.fk_department    1    ""
1    PRIMARY    PS    ALL                    7781    ""
1    PRIMARY    POST_CODE    eq_ref    PRIMARY    PRIMARY    4    skfoms.PS.fk_code_medic_post    1    ""
1    PRIMARY    TPS1    ref    fk_mauf    fk_mauf    5    skfoms.PS.fk_mauf    24    ""
1    PRIMARY    TPS2    ref    fk_tabel_number    fk_tabel_number    4    skfoms.TPS1.pk_id    1    Using index
1    PRIMARY    TFOMS    ref    fk_user    fk_user    4    skfoms.U.pk_id    1    ""
1    PRIMARY    SKFOMS_CODES    eq_ref    PRIMARY    PRIMARY    4    skfoms.GDS.fk_zone_code    1    ""
1    PRIMARY    SKFOMS_NAMES    eq_ref    PRIMARY    PRIMARY    4    skfoms.SKFOMS_CODES.fk_dic_filial_skfoms    1    ""
1    PRIMARY    <derived2>    ALL                    6800    ""
1    PRIMARY    <derived3>    ALL                    2248    ""
1    PRIMARY    <derived4>    ALL                    1645    ""
1    PRIMARY    <derived5>    ALL                    1    ""
5    DERIVED    U    range    PRIMARY    PRIMARY    4        2    Using where; Using index; Using temporary; Using filesort
5    DERIVED    GDS    ref    fk_user    fk_user    5    skfoms.U.pk_id    1    Using index
5    DERIVED    MAUF    ref    PRIMARY,fk_user    fk_user    5    skfoms.U.pk_id    3    Using where; Using index
5    DERIVED    _TPS1    ref    PRIMARY,fk_mauf    fk_mauf    5    skfoms.MAUF.pk_id    24    Using where
5    DERIVED    _TPS2    ref    PRIMARY,fk_tabel_number    fk_tabel_number    4    skfoms._TPS1.pk_id    1    Using where
4    DERIVED    U    index        PRIMARY    4        221    Using index; Using temporary; Using filesort
4    DERIVED    MAUF    ref    fk_user    fk_user    5    skfoms.U.pk_id    3    Using index
4    DERIVED    GDS    ref    fk_user    fk_user    5    skfoms.U.pk_id    1    Using index
4    DERIVED    __PS    ref    fk_mauf    fk_mauf    5    skfoms.MAUF.pk_id    10    ""
3    DERIVED    U    index        PRIMARY    4        221    Using index; Using temporary; Using filesort
3    DERIVED    MAUF    ref    fk_user    fk_user    5    skfoms.U.pk_id    3    Using index
3    DERIVED    PDS    ref    fk_mauf    fk_mauf    5    skfoms.MAUF.pk_id    3    Using index
3    DERIVED    __PS    ALL                    7781    ""
3    DERIVED    _TPS1    ref    fk_mauf    fk_mauf    5    skfoms.__PS.fk_mauf    24    ""
3    DERIVED    _TPS2    ref    fk_tabel_number    fk_tabel_number    4    skfoms._TPS1.pk_id    1    ""
3    DERIVED    POST_CODE    eq_ref    PRIMARY    PRIMARY    4    skfoms._TPS1.fk_code_medic_post    1    Using index
2    DERIVED    U    index        PRIMARY    4        221    Using index; Using temporary; Using filesort
2    DERIVED    MAUF    ref    fk_user    fk_user    5    skfoms.U.pk_id    3    Using index
2    DERIVED    GDS    ref    fk_user    fk_user    5    skfoms.U.pk_id    1    Using index
2    DERIVED    __PS    ref    fk_mauf    fk_mauf    5    skfoms.MAUF.pk_id    10    ""
2    DERIVED    POST_CODE    eq_ref    PRIMARY    PRIMARY    4    skfoms.__PS.fk_code_medic_post    1    Using index


Что еще необходимо для продолжения рассмотрения вопроса?

Добавлено через 10 минут и 19 секунд
или лучше изображением, а то как-то совсем криво?
PM MAIL   Вверх
Zloxa
Дата 17.11.2009, 13:07 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161
Цитата(insy @  17.11.2009,  12:52 Найти цитируемый пост)
Что еще необходимо для продолжения рассмотрения вопроса?


Цитата(Zloxa @  12.11.2009,  16:06 Найти цитируемый пост)
 расписали бы струткру данных, объяснили бы отношения, да суть желаемого




--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
  
 Ответ в темуСоздание новой темы Создание опроса
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | Составление SQL-запросов | Следующая тема »

Подписаться на тему | Подписка на этот форум | Скачать/Распечатать тему


 




[ Время генерации скрипта: 0.0526 ]   [ Использовано запросов: 22 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
 По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.