Блог

У меня есть запрос, который примет отдельный список значений и получит первое совпадение на основе ранжированной сортировки, которая хорошо работает для мелкомасштабной работы. Проблема в том, что когда я применяю это к крупномасштабной работе (35 000 уникальных записей против 40000 записей), запрос просто синхронизируется и зависает.

В качестве эксперимента я выполнил только первую половину запроса, и это заняло около 3-4 минут на 40 тыс. строк (см. Ниже, что я имею в виду). Я уже изменил левые соединения на внутренние соединения с небольшим повышением производительности, но у меня заканчиваются идеи о том, что еще я могу сделать.

Обратите внимание, что, если это вообще возможно, я хочу максимально использовать SQL, основанный на непатентованных стандартах, поскольку в ближайшем будущем я, вероятно, перейду с MS SQL Server на MySQL или Oracle, и я не хочу делать ненужные перезаписи.

Запрос

 SELECT Q.*
FROM (
SELECT 
    A.name
  , B.id
  , B.status
  , B.rank

FROM TestA A
LEFT JOIN TestB B
ON B.name = A.name
WHERE 
  B.rank = (
            SELECT MIN(B2.rank) 
            FROM TestB B2 
            WHERE B.name = B2.name
           )
) Q
-- by first half I mean what is above this line here
WHERE
    Q.id = (
        SELECT MIN(Q2.id) 
        FROM (
            SELECT 
                A.name
              , B.id
              , B.status
              , B.rank

            FROM TestA A
            LEFT JOIN TestB B
            ON B.name = A.name
            WHERE 
              B.rank = (
                        SELECT MIN(B2.rank) 
                        FROM TestB B2 
                        WHERE B.name = B2.name
                       )
        ) Q2 
        WHERE Q.name = Q2.name
       )
;
 

Схема: тестовых данных

 CREATE TABLE TestA
(`name` varchar(4))
;

INSERT INTO TestA
(`name`)
VALUES
('bob'),
('john'),
('will')
;

CREATE TABLE TestB
(`name` varchar(4), `id` int, `status` varchar(18), `rank` int)
;

INSERT INTO TestB
(`name`, `id`, `status`, `rank`)
VALUES
('bob', 11, 'happy', 1),
('bob', 12, 'active', 1),
('bob', 93, 'inactive', 2),
('bob', 94, 'canceled', 2),
('bob', 95, 'pending deletion', 3),
('john', 32, 'pending activation', 10),
('john', 24, 'inactive', 4),
('will', 555, 'vacation', 5),
('will', 511, 'vacation', 5),
('will', 661, 'on hold', 9)
;
 

Вот скрипка, которую я сделал из схемы и кода

http://sqlfiddle.com /#!2/e91a8/3/0

Первое, что я хотел бы упомянуть, это то, что вы присоединяетесь к name, и я предполагаю, что вы не добавили никакого индекса в этот столбец (в схеме не определен какой-либо индекс).). Если вы можете использовать идентификатор автоматического увеличения в TableA и использовать его в качестве внешнего ключа в TableB, использование join с ID значительно ускорит ваш запрос.

Я бы также рекомендовал вам запустить профилировщик в среде SQL Server Management Studio (SSMS) во время выполнения запроса, если у вас есть права администратора. Вы также можете проверить sqlsentry, который утверждает, что он лучше, чем SSMS для профилирования. http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view (Я еще не пробовал, но выглядит многообещающе).

Дайте мне знать, помогают ли индексы или нет.

Присоединение к строке, как уже упоминалось, плохо … тем не менее, этот запрос должен быть немного быстрее и совместим с SQL Server и MySQL (не уверен в Oracle).

 SELECT A.name, B.id, B.status, B.rank
FROM TestA A
INNER JOIN TestB B 
   ON A.name = B.name
   INNER JOIN (SELECT MIN(id) AS id FROM TestB GROUP BY name) AS MinID
      ON B.id = MinID.id
 

Скрипка

 SELECT 
    A.name
  , B.id
  , B.status
  , B.rank
FROM TestA A
LEFT JOIN (
    SELECT *
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY B.id ORDER BY B.rank) rownum
        FROM B
    ) b1
    WHERE rownum = 1
) B
ON B.name = A.name
 

Попробуйте что-то вроде этого…..

 SELECT A.Name
      ,B.ID
      ,B.[Status]
      ,B.[Rank]
FROM TestA A 
INNER JOIN 
          (
           SELECT name, id, status, rank
              ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY [Rank] ASC) rn
           FROM TestB
          ) B
ON A.Name = B.Name 
WHERE B.RN = 1
 

РАБОЧАЯ СКРИПКА SQL

Вот версия, которая работает как в SQL Server, так и в MySQL. Вероятно, немного медленнее, чем при использовании row_number() .

 select A.Name,
       B1.id,
       B1.status,
       B1.rank
from TestB as B1
  inner join (
             select B3.name,
                    B3.rank,
                    min(B3.id) as id 
             from (
                    select B4.name,
                           B4.id,
                           B4.rank
                    from TestB as B4
                      inner join (
                                 select min(B6.rank) as rank,
                                        B6.name
                                 from TestB as B6
                                 group by B6.name
                                 ) as B5
                        on B4.name = B5.name and
                           B4.rank = B5.rank
                  ) as B3
             group by B3.Name, 
                      B3.rank
             ) as B2
    on B1.name = B2.Name and
       B1.rank = B2.rank and
       B1.id = B2.id
  inner join TestA as A
    on B1.name = A.name