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

У меня три таблицы, люди, электронная почта и персональная почта. 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.