Оптимизация медленного SQL-запроса: производные таблицы и два разных соединения на основе типа строки

Я использую Firebird 2.1. Я хотел бы оптимизировать следующий запрос, поскольку он выполняется очень медленно (почти 2 секунды) даже для небольшого набора данных (около 500 записей):

TABLE Client хранит людей и компании/группы компаний. (ClientType=0 человек, ClientType=1 компания) Первичный ключ: ClientID

TABLE ClientContacts хранит, какие лица связаны с какими компаниями: ClientID — это идентификатор компании, ContactClientID — это идентификатор человека, связанного с компанией. Первичные ключи: ClientID, ContactClientID

TABLE CompanyGroups хранит, какие компании связаны с какими группами компаний: ParentClientID — это идентификатор группы компаний, ClientID — это идентификатор компании, прикрепленный к группе компаний. Первичные ключи: ParentClientID, ClientID

Таким образом, лицо может принадлежать более чем одной компании, а компания может принадлежать более чем одной группе компаний.

Я хотел бы перечислить всех лиц и компаний. Для лиц я хочу показать, к каким компаниям они принадлежат, а для компаний я хочу показать, к каким группам компаний принадлежит компания.

В довершение всего мне нужно искать по названию компаний/групп компаний, к которым принадлежит человек/компания. В Firebird из-за агрегатной функции «СПИСОК» я могу добиться этого с помощью производных таблиц.

Вот запрос:

SELECT C.ClientID,
  C.ClientType,
  C.ClientName,
  IIF(C.ClientType = 0, PCN.PCompanyNames, CCN.CCompanyNames),
FROM Clients C
  LEFT JOIN (SELECT CC.ContactClientID, LIST(CL.ClientName, ', ') AS PCompanyNames
   FROM ClientContacts CC LEFT JOIN Clients CL ON CL.ClientID = CC.ClientID WHERE 
    CL.AccessRights = 0 OR CL.UserID = :UserID OR (CL.AccessRights = 2 AND 
     CL.ClientID IN (SELECT ClientID FROM ClientRights WHERE UserID = :UserID))
      GROUP BY CC.ContactClientID) PCN ON PCN.ContactClientID = C.ClientID AND C.ClientType = 0
  LEFT JOIN (SELECT CG.ClientID, LIST(CL.ClientName, ', ') AS CCompanyNames
   FROM CompanyGroups CG LEFT JOIN Clients CL ON CL.ClientID = CG.ParentClientID WHERE 
    CL.AccessRights = 0 OR CL.UserID = :UserID OR (CL.AccessRights = 2 AND
     CL.ClientID IN (SELECT ClientID FROM ClientRights WHERE UserID = :UserID))
      GROUP BY CG.ClientID) CCN ON CCN.ClientID = C.ClientID AND C.ClientType = 1
WHERE (C.AccessRights = 0
    OR C.UserID = :UserID
      OR (C.AccessRights = 2 AND C.ClientID IN (SELECT ClientID FROM ClientRights WHERE UserID = :UserID)))
  AND (:SearchStr IS NULL
    OR (PCN.PCompanyNames COLLATE UNICODE_CI LIKE '%' || :SearchStr || '%'
    OR CCN.CCompanyNames COLLATE UNICODE_CI LIKE '%' || :SearchStr || '%'))

Обновить план запроса, сначала приведенный выше запрос, затем приведенный выше запрос без предложений WHERE везде (без IN SELECT)

Field #01: CLIENTS.CLIENTID Alias:CLIENTID Type:INTEGER
Field #02: CLIENTS.CLIENTTYPE Alias:CLIENTTYPE Type:INTEGER
Field #03: CLIENTS.CLIENTNAME Alias:CLIENTNAME Type:STRING(1000)
Field #04: .CASE Alias:CASE Type:BLOB SUB_TYPE 1
PLAN (PCN CLIENTRIGHTS INDEX (RDB$PRIMARY46))
PLAN (CCN CLIENTRIGHTS INDEX (RDB$PRIMARY46))
PLAN (CLIENTRIGHTS INDEX (RDB$PRIMARY46))
PLAN JOIN (JOIN (C INDEX (IDX_CLIENTS_ACCESSRIGHTS, IDX_CLIENTS_USERID, IDX_CLIENTS_ACCESSRIGHTS), SORT (JOIN (PCN CC NATURAL, PCN CL INDEX (RDB$PRIMARY12)))), SORT (JOIN (CCN CG NATURAL, CCN CL INDEX (RDB$PRIMARY12))))

119643 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 19977 index, 19629 seq.
Delta memory: 321686664 bytes.
Total execution time: 1.531s


Field #01: CLIENTS.CLIENTID Alias:CLIENTID Type:INTEGER
Field #02: CLIENTS.CLIENTTYPE Alias:CLIENTTYPE Type:INTEGER
Field #03: CLIENTS.CLIENTNAME Alias:CLIENTNAME Type:STRING(1000)
Field #04: .CASE Alias:CASE Type:BLOB SUB_TYPE 1
PLAN JOIN (JOIN (C NATURAL, SORT (JOIN (PCN CC NATURAL, PCN CL INDEX (RDB$PRIMARY12)))), SORT (JOIN (CCN CG NATURAL, CCN CL INDEX (RDB$PRIMARY12))))

119289 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 19646 index, 19832 seq.
Delta memory: 321690896 bytes.
Total execution time: 1.406s

sql query-optimization firebird firebird2.1
person Steve    schedule 01.09.2016    source источник
comment
Пожалуйста, покажите DDL задействованных таблиц, включая все файлы index. Также опубликуйте текущий план запроса. Одна из вещей, которая сразу бросается в глаза, — это использование запроса IN (select ...), замена которого запросом exists обычно работает лучше. Но убийцей производительности, вероятно, является использование LIKE для поиска внутри текстовых полей.   -  person Mark Rotteveel    schedule 01.09.2016
comment
Отметьте, удаление IN (SELECT) и LIKE не улучшает производительность (поскольку я тестировал его с SearchStr = NULL, а AccessRights был равен 0 почти везде в клиентах). Я опубликую больше.   -  person Steve    schedule 01.09.2016
comment
Итак, я добавил план запроса, а также первичные ключи, других используемых индексов нет.   -  person Steve    schedule 01.09.2016
comment
Я предлагаю вам также задать этот вопрос в списке рассылки поддержки firebird. Может получиться лучший ответ.   -  person Mark Rotteveel    schedule 03.09.2016

Ответы (1)


arrow_upward
3
arrow_downward

К сожалению, я не могу оставить комментарий (не нужна репутация). Без структуры обойтись сложно, но я стараюсь.

So:

1) Вам нужно разделить запрос ровно на две части: ClientType = 0 и ClientType = 1;

2) Вам не нужно левое соединение внутри PCN и CCN, потому что это не имеет смысла;

3) Вы часто использовали запрос:

select ClientID
from Client
where c.AccessRight = 0 or
      c.UserId = :UserId or
      (c.AccessRight = 2 and
       c.ClientId in (
          select r.ClientId
          from ClientRights r
          where r.UserId = :UserId))

Я думаю, вы должны сделать что-то вроде этого:

with
cl as (
   select c.ClientId, c.ClientName, c.ClientType
   from Client c
   where c.AccessRight = 0 or
         c.UserId = :UserId or
         (c.AccessRight = 2 and c.ClientId in (select r.ClientId from ClientRights r where r.UserId = :UserId))),
q2 as (
    select cc.ContactClientId, List (cl.ClientName, ', ') as PCompanyNames
    from ClientContacts cc
         join cl on (cc.ClientId = cl.ClientId)
    group by cc.ContactClientId),
q3 as (
    select cg.ClientId, List (cl.ClientName, ', ') as CCompanyNames
    from CompanyGroups cg
         join cl on (cg.ParentClientId = cl.ClientId)
    group by cg.ClientId)
select cl.ClientId, cl.ClientType, cl.ClientName, q2.PCompanyNames
from cl
     left join q2 on (cl.ClientId = q2.ContactClientId)
where cl.ClientType = 0
  and (q2.PCompanyNames like '%' || Coalesce (:SearchStr, '') || '%' or Coalesce(:SearchStr, '') = '')
union all
select cl.ClientId, cl.ClientType, cl.ClientName, q3.CCompanyNames
from cl
     left join q3 on (cl.ClientId = q3.ClientId)
where cl.ClientType = 1
  and (q3.CCompanyNames like '%' || Coalesce (:SearchStr, '') || '%' or Coalesce(:SearchStr, '') = '')
person jurden    schedule 07.09.2016
comment
Спасибо, ваш запрос очень быстрый, выполняется менее чем за 0,2 секунды! Единственное, мне нужно заказать результат, установленный Clientname. Помещение предложения order by в ваш запрос замедляет время выполнения до 4 секунд. С моим запросом время выполнения такое же, если вы укажете порядок. Любые идеи о том, как оптимизировать ваш запрос с предложением order by? - person Steve; 07.09.2016
comment
Можете ли вы создать индекс по ClientName? И напишите здесь сроки. - person jurden; 07.09.2016
comment
С индексом это не быстрее, примерно 4 секунды. Любые другие идеи? - person Steve; 07.09.2016