Нужен менее дорогостоящий запрос

У меня три таблицы, люди, электронная почта и персональная почта. Personemail в основном имеет внешний ключ для человека и электронную почту, поэтому один человек может быть связан с несколькими адресами электронной почты. Также в таблице электронной почты есть поле с именем primaryemail. Это поле равно 1 или 0. Основной электронный флаг используется для вытягивания электронных писем в отчеты/счета-фактуры и т.д.

В пользовательском интерфейсе был логический недостаток, который позволил пользователям не устанавливать первичные адреса электронной почты для клиентов. Я закрыл логический недостаток, но мне нужен сценарий, чтобы заставить основной адрес электронной почты для любого клиента, у которого нет одного набора. Было принято решение установить основной адрес электронной почты для наименьшего значения для электронной почты (первичный ключ в таблице электронной почты). Ниже приведен сценарий, который был написан, и он работает, но он очень дорог для запуска и может вызвать блокировки для конечных пользователей во время работы. Программное обеспечение развертывается в нескольких часовых поясах, поэтому, даже если мы запускаем его в течение самого низкого времени использования, нам нужно, чтобы он работал как можно быстрее.

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

CREATE TABLE #TEMP(PERSONID INT, PRIMARYEMAIL INT,FLAG INT)
CREATE INDEX IDX_TEMP_PERSONID ON #TEMP(PERSONID)
CREATE TABLE #TEMP2(PERSONID INT,PRIMARYEMAIL INT)
CREATE INDEX IDX_TEMP2_PERSONID ON #TEMP2(PERSONID)
--Grab all the person id that have at least one email addresses that is not primary in the db, also set a flag for the while loop
INSERT INTO #TEMP
SELECT PE.PersonID, E.primaryEmail ,0
FROM Account.tbPersonEmail PE WITH (NOLOCK)
LEFT OUTER JOIN Account.tbEmail E ON E.EmailID=PE.EmailID
WHERE E.primaryEmail=0
--Grab all person ID that have at least one email address that is primary.
INSERT INTO #TEMP2
SELECT PE.PersonID, E.primaryEmail
FROM Account.tbPersonEmail PE WITH (NOLOCK)
LEFT OUTER JOIN Account.tbEmail E ON E.EmailID=PE.EmailID
WHERE E.primaryEmail=1
--SELECT * FROM #TEMP2
--Remove any customers that already have a primary email set.
DELETE FROM #TEMP WHERE PERSONID IN (SELECT DISTINCT PERSONID FROM #TEMP2)
--Debug line to see how many customers are affected.
--SELECT * FROM #TEMP
--Perfom a while loop to update the min email ID to primary.
DECLARE @INTFLAG INT
DECLARE @PERSONID INT
SET @INTFLAG = (SELECT COUNT(*) FROM #TEMP)
--SELECT @INTFLAG
WHILE (@INTFLAG > 0)
BEGIN
SET @PERSONID =(SELECT TOP(1) PERSONID FROM #TEMP WHERE FLAG=0)
UPDATE Account.tbEmail SET primaryEmail=1 WHERE EmailID=(SELECT MIN(EMAILID) FROM Account.tbPersonEmail where PersonID=@PERSONID)
--Update the flag on the #temp table to grab the next ID
UPDATE #TEMP SET FLAG=1 WHERE PERSONID=@PERSONID
--Reduce the intflag variable that the loop is running off of.
SET @INTFLAG=@INTFLAG-1
END
DROP TABLE #TEMP
DROP TABLE #TEMP2
4 ответа

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

CREATE TABLE #TEMP(PERSONID INT, PRIMARYEMAIL INT,FLAG INT)
CREATE INDEX IDX_TEMP_PERSONID ON #TEMP(PERSONID)
INSERT INTO #TEMP
SELECT PE.PersonID, E.primaryEmail , 0
FROM Account.tbPersonEmail PE WITH (NOLOCK)
LEFT OUTER JOIN Account.tbEmail E ON E.EmailID=PE.EmailID
WHERE E.primaryEmail=0 and
PE.PersonID not in (SELECT Distinct PE2.PersonID
FROM Account.tbPersonEmail PE2 WITH (NOLOCK)
LEFT OUTER JOIN Account.tbEmail E2 ON E.EmailID=PE2.EmailID
WHERE E2.primaryEmail=1)

А затем запустите цикл while. Это должно немного помочь. Вы можете проверить, что это правильно, если #TEMP соответствует предыдущей версии.

Для дальнейшей оптимизации вам, вероятно, потребуется переписать весь процесс обновления как один запрос. Вы также можете посмотреть на это: Как я могу оптимизировать этот SQL-запрос (используя индексы)?


Один запрос для установки primaryEmail = 1 для первого письма для каждого человека, кроме тех, у кого уже есть первичный адрес электронной почты:

UPDATE Account.tbEmail E SET E.primaryEmail=1
WHERE E.EmailID in ( -- get min email id for each person SELECT min(PE.EmailID) FROM Account.tbPersonEmail PE -- but exclude persons who already have primary email WHERE PE.PersonID NOT IN ( SELECT PE1.PersonID FROM Account.tbPersonEmail PE1 INNER JOIN Account.tbEmail E1 ON E1.EmailID=PE1.EmailID WHERE E1.primaryEmail=1 ) GROUP BY PE.PersonID )


Закончилось с этим.

UPDATE Account.tbEmail set primaryEmail=1 where EmailID in (SELECT P.Emailid from ( SELECT DISTINCT P.PersonID,MIN(P.EmailID)AS EmailID FROM (SELECT PE.PersonID, E.primaryEmail,PE.EmailID FROM Account.tbPersonEmail PE WITH (NOLOCK) LEFT OUTER JOIN Account.tbEmail E ON E.EmailID=PE.EmailID WHERE E.primaryEmail=0 and PE.PersonID not in (SELECT Distinct PE2.PersonID FROM Account.tbPersonEmail PE2 WITH (NOLOCK) LEFT OUTER JOIN Account.tbEmail E2 ON E2.EmailID=PE2.EmailID WHERE E2.primaryEmail=1) )AS P GROUP BY P.PersonID ) as P)


Ваша логика для создания первичной электронной почты не подходит. Более сложное включение функции агрегата или ранговой функции в столбце varchar является более плохим. Мы должны знать и другую колонку.

Мне понравилось предложение @David, но не скрипт. Попробуйте мой сценарий с надлежащим тестированием, прежде чем вы также должны вернуться к резервному копированию.

;With CTE as
( SELECT PE.PersonID, E.primaryEmail ,E.EmailID
,row_number()over(order by PE.EMAILID )rn FROM Account.tbPersonEmail PE WITH (NOLOCK) LEFT OUTER JOIN Account.tbEmail E ON E.EmailID=PE.EmailID
--why left join WHERE E.primaryEmail=0
)
-- IN CTE you get only those which is not updated.
-- row_number()over(order by PE.EMAILID ) is equivalent to min(emailid)
UPDATE Account.tbEmail SET primaryEmail=1
from Account.tbEmail A inner join CTE B on A.EmailID=B.EmailID
WHERE B.rn=1

licensed under cc by-sa 3.0 with attribution.