#sql #sql-server #performance #stored-procedures #sql-server-2019-express
#sql #sql-сервер #Производительность #хранимые процедуры #sql-server-2019-экспресс
Вопрос:
Я новичок в оптимизации производительности в SQL Server. Но у меня проблема с хранимой процедурой, выполнение которой занимает почти 2 часа — я думаю, что это долго. Это около 100000 записей, которые он должен перебирать, и я использовал вычисляемые столбцы (вычисляемые столбцы имеют префикс Upper_ ).
По словам моего босса, мы не можем использовать полнотекстовый поиск, поэтому я действительно надеюсь, что есть возможности для повышения производительности без использования полнотекстового поиска.
Я потратил много времени на поиск в Интернете без каких-либо дальнейших успехов.
Может быть, я смогу правильно использовать индексы, но я тоже не знаю, как их правильно создавать.
Моя хранимая процедура выглядит следующим образом:
ALTER PROCEDURE [dbo].[DuplicateLevel4]
@UserID INT
AS
SET NOCOUNT ON
DECLARE @dubletNo INT,
@subDubletNo INT,
@Vennenr INT,
@fornavn VARCHAR(max),
@shortenfornavn VARCHAR(MAX),
@efternavn VARCHAR(max),
@adresse VARCHAR(max),
@postnr VARCHAR(max),
@telefon VARCHAR(max),
@tlf1 VARCHAR(MAX),
@email VARCHAR(MAX),
@done BIT,
@oldTime DATETIME
SET @dubletNo = 1
SET @oldtime = current_Timestamp
IF (SELECT COUNT(ID) FROM DuplicateSetup) > 0
UPDATE DuplicateSetup
SET IsOrdered = 1, OrderDateTime = @oldTime, CurrentLevel = 4
ELSE
INSERT INTO DuplicateSetup (IsOrdered, OrderDateTime, CurrentLevel)
VALUES (1, @oldTime, 4)
DELETE FROM DuplicateList;
CREATE TABLE #tempTable
(
[DubletNo] [int] NULL,
[SubDubletNo] [int] NULL,
[UseOrganisation] [bit] NULL,
[UseFornavn] [bit] NULL,
[UseEfternavn] [bit] NULL,
[UseAdresse] [bit] NULL,
[UsePostCode] [bit] NULL,
[UseTlf1] [bit] NULL,
[UseTlf2] [bit] NULL,
[UseEmail] [bit] NULL,
[Choice] [smallint] NULL,
[AKeep] [bit] NULL,
[FriendID] [int] NULL,
[UseBornDate] [bit] NULL
)
CREATE CLUSTERED INDEX idxTempTableFriendID ON #tempTable (FriendID)
CREATE NONCLUSTERED INDEX idxTempTableDubletNo ON #tempTable (DubletNo)
DECLARE Friend_Cursor CURSOR FOR
SELECT
vennenr,
CASE
WHEN CHARINDEX(' ', ISNULL(Fornavn, '')) > 0
THEN UPPER(RTRIM(SUBSTRING(ISNULL(Fornavn, ''), 1, CHARINDEX(' ', ISNULL(Fornavn, '')))))
WHEN CHARINDEX(' ', ISNULL(Fornavn, '')) = 0
THEN UPPER(RTRIM(SUBSTRING(ISNULL(Fornavn, ''), 1, LEN(ISNULL(Fornavn, '')))))
END AS ShortenFornavn,
UPPER(Fornavn),
UPPER(Efternavn),
UPPER(adresse),
UPPER((Postnr)),
REPLACE(Telefon, ' ', ''),
REPLACE(Tlf1, ' ', ''),
UPPER([E-mail])
FROM
Medlemsdata
LEFT OUTER JOIN
Postnumre
RIGHT OUTER JOIN
MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr
ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE
vennenr > 0
AND vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends
WHERE ALevelNo = 4)
AND ((Fornavn IS NOT NULL
AND dbo.getToSpace(Fornavn) NOT IN ('')
AND Efternavn IS NOT NULL
AND Efternavn NOT IN ('')
AND Adresse IS NOT NULL
AND Adresse NOT IN ('')
AND Telefon IS NOT NULL
AND Telefon NOT IN ('')
AND Postnr IS NOT NULL
AND Postnr NOT IN ('')) OR
(Fornavn IS NOT NULL AND Fornavn NOT IN ('')
AND Efternavn IS NOT NULL AND Efternavn NOT IN ('')
AND Adresse IS NOT NULL AND Adresse NOT IN ('')
AND Postnr IS NOT NULL AND Postnr NOT IN ('')) OR
(Fornavn IS NOT NULL AND dbo.getToSpace(Fornavn) NOT IN ('')
AND Efternavn IS NOT NULL AND Efternavn NOT IN ('')
AND Adresse IS NOT NULL AND Adresse NOT IN ('')
AND Tlf1 IS NOT NULL AND Tlf1 NOT IN ('')
AND Postnr IS NOT NULL AND Postnr NOT IN ('')) OR
(Fornavn IS NOT NULL AND dbo.getToSpace(Fornavn) NOT IN ('')
AND Efternavn IS NOT NULL AND Efternavn NOT IN ('')
AND Adresse IS NOT NULL AND Adresse NOT IN ('')
AND [E-mail] IS NOT NULL AND [E-mail] NOT IN ('')
AND Postnr IS NOT NULL AND Postnr NOT IN ('')) OR
(Fornavn IS NOT NULL AND dbo.getToSpace(Fornavn) NOT IN ('')
AND Efternavn IS NOT NULL AND Efternavn NOT IN ('')
AND Telefon IS NOT NULL AND Telefon NOT IN ('')
AND [E-mail] IS NOT NULL AND [E-mail] NOT IN ('')))
ORDER BY
Vennenr
OPEN Friend_Cursor
FETCH NEXT FROM Friend_Cursor INTO @Vennenr, @shortenfornavn, @fornavn, @efternavn, @adresse, @postnr, @telefon, @tlf1, @email
--loop på alle venner > 0
WHILE @@FETCH_STATUS = 0
BEGIN
-- Check om venner er håndteret før
IF (SELECT COUNT(FriendID) FROM #tempTable WHERE FriendID = @Vennenr) = 0
BEGIN
-- initializing
SET @done = 0;
-- Indsæt første dublet
INSERT INTO #tempTable (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate])
VALUES (@Vennenr, @dubletNo, 1, 1,1,1,1,1,1,1,1,0,0,1)
SET @subDubletNo = 1;
-- Første check
IF @done = 0
BEGIN
IF EXISTS (SELECT Vennenr
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr
ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@Adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
(ISNULL(telefon,'') LIKE '%' @telefon '%') AND (LEN(@telefon) > 7) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
)
BEGIN
INSERT INTO #tempTable (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate])
SELECT Vennenr, @dubletNo, ROW_NUMBER() OVER (ORDER BY Vennenr) @subDubletNo, 0,0,0,0,0,0,0,0,0,0,0
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@Adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
(ISNULL(telefon,'') LIKE '%' @telefon '%') AND (LEN(@telefon) > 7) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
SET @subDubletNo = (SELECT COUNT(FriendID) FROM #tempTable WHERE DubletNo = @dubletNo)
SET @done = 1
END
END
-- Andet check
IF @done = 0 BEGIN
IF EXISTS (
SELECT Vennenr
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn IN (@fornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@Adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
)
BEGIN
INSERT INTO #tempTable (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate])
SELECT Vennenr, @dubletNo, ROW_NUMBER() OVER (ORDER BY Vennenr) @subDubletNo, 0,0,0,0,0,0,0,0,0,0,0
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn IN (@fornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@Adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
SET @subDubletNo = (SELECT COUNT(FriendID) FROM #tempTable WHERE DubletNo = @dubletNo)
SET @done = 1
END
END
-- Tredje check
IF @done = 0 BEGIN
IF EXISTS (
SELECT Vennenr
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@Adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
(ISNULL(Tlf1,'') LIKE '%' @tlf1 '%') AND (LEN(@tlf1) > 7) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
)
BEGIN
INSERT INTO #tempTable (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate])
SELECT Vennenr, @dubletNo, ROW_NUMBER() OVER (ORDER BY Vennenr) @subDubletNo, 0,0,0,0,0,0,0,0,0,0,0
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@Adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
(ISNULL(Tlf1,'') LIKE '%' @tlf1 '%') AND (LEN(@tlf1) > 7) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
SET @subDubletNo = (SELECT COUNT(FriendID) FROM #tempTable WHERE DubletNo = @dubletNo)
SET @done = 1
END
END
-- Fjerde check
IF @done = 0 BEGIN
IF EXISTS (
SELECT Vennenr
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
(Upper_Email IN (@email)) AND (Upper_Email NOT IN ('')) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
)
BEGIN
INSERT INTO #tempTable (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate])
SELECT Vennenr, @dubletNo, ROW_NUMBER() OVER (ORDER BY Vennenr) @subDubletNo, 0,0,0,0,0,0,0,0,0,0,0
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(Upper_Adresse IN (@adresse)) AND (Upper_Adresse NOT IN ('')) AND
(Upper_Postnr IN (@postnr)) AND (Upper_postnr NOT IN ('')) AND
(Upper_Email IN (@email)) AND (Upper_Email NOT IN ('')) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
SET @subDubletNo = (SELECT COUNT(FriendID) FROM #tempTable WHERE DubletNo = @dubletNo)
SET @done = 1
END
END
-- Femte check
IF @done = 0 BEGIN
IF EXISTS (
SELECT Vennenr
FROM Medlemsdata
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(ISNULL(Telefon,'') LIKE '%' @telefon '%') AND (LEN(@telefon) > 7) AND
(Upper_Email IN (@email)) AND (Upper_Email NOT IN ('')) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
)
BEGIN
INSERT INTO #tempTable (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate])
SELECT Vennenr, @dubletNo, ROW_NUMBER() OVER (ORDER BY Vennenr) @subDubletNo, 0,0,0,0,0,0,0,0,0,0,0
FROM Medlemsdata
WHERE (Vennenr <> @Vennenr) AND (Upper_Fornavn_FirstPart IN (@shortenfornavn)) AND (Upper_Fornavn NOT IN ('')) AND
(Upper_Efternavn IN (@efternavn)) AND (Upper_Efternavn NOT IN ('')) AND
(ISNULL(Telefon,'') LIKE '%' @telefon '%') AND (LEN(@telefon) > 7) AND
(Upper_Email IN (@email)) AND (Upper_Email NOT IN ('')) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4) AND
vennenr NOT IN (SELECT FriendID FROM #tempTable)
SET @subDubletNo = (SELECT COUNT(FriendID) FROM #tempTable WHERE DubletNo = @dubletNo)
SET @done = 1
END
END
-- Hvis der ikke var noget match, så ...
IF @subDubletNo = 1 BEGIN
DELETE FROM #tempTable WHERE FriendID = @Vennenr
END
ELSE BEGIN
SET @dubletNo = @dubletNo 1
END
END
FETCH NEXT FROM Friend_Cursor INTO @Vennenr, @shortenfornavn, @fornavn, @efternavn, @adresse, @postnr, @telefon, @tlf1, @email
END
INSERT INTO DuplicateList (FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate])
SELECT FriendID, DubletNo, SubDubletNo, [UseOrganisation], [UseFornavn], [UseEfternavn], [UseAdresse], [UsePostCode], [UseTlf1], [UseTlf2], [UseEmail], [Choice], [AKeep], [UseBornDate]
FROM #tempTable
CLOSE Friend_Cursor
DEALLOCATE Friend_Cursor
UPDATE DuplicateSetup SET IsOrdered = 0, OrderDateTime = null, UserID = @UserID, Updated = CURRENT_TIMESTAMP
INSERT INTO DuplicateTimeConsumption (ALevel, TimeConsumption) VALUES (4, current_Timestamp - @oldtime)
SET NOCOUNT OFF
Один из моих вычисляемых столбцов выглядит следующим образом:
ALTER TABLE Medlemsdata ADD Upper_Fornavn_FirstPart AS
CASE
WHEN CHARINDEX(' ', Fornavn) = 0 THEN UPPER(Fornavn)
WHEN CHARINDEX(' ', Fornavn) > 0 THEN UPPER(RTRIM(LEFT(Fornavn,CHARINDEX(' ', Fornavn))))
END
PERSISTED
Я действительно надеюсь, что есть / есть основы для оптимизации производительности и что кто-нибудь может дать мне представление о том, на что обратить внимание.
Протестированный код здесь:
SELECT Vennenr
FROM Medlemsdata
LEFT OUTER JOIN Postnumre
RIGHT OUTER JOIN MedlemsAdresse ON Postnumre.Postnummer = MedlemsAdresse.Postnr ON Medlemsdata.FK_AdrID = MedlemsAdresse.AdrID
WHERE (Vennenr <> 1) AND Upper_Fornavn_FirstPart = CASE WHEN Upper_Fornavn_FirstPart IS NOT NULL THEN 'MICHAEL' ELSE '' END AND
Upper_Efternavn = CASE WHEN Upper_Efternavn IS NOT NULL THEN 'ERIKSEN' ELSE '' END AND
Upper_Adresse = CASE WHEN Upper_Adresse IS NOT NULL THEN 'HELIOSVEJ 23' ELSE '' END AND
Upper_Postnr = CASE WHEN Upper_Postnr IS NOT NULL THEN '7100' ELSE '' END AND (ISNULL(telefon,'') LIKE '%' '23747585' '%') AND (LEN('23747585') > 7) AND
vennenr > 0 AND
vennenr NOT IN (SELECT FriendID FROM DuplicateForgetFriends WHERE ALevelNo = 4)
Заранее спасибо,
Майкл
Комментарии:
1. «но у меня проблема с хранимой процедурой, выполнение которой занимает почти 2 часа» избавление от этого
CURSORбудет огромным шагом. Честно говоря, похоже, что вы чрезмерно усложнили проблему. Я подозреваю, что вам было бы гораздо лучше с самого начала объяснить, какова здесь цель. Кроме того, почему у вас есть вычисляемые столбцы дляUPPERзначений? Является ли# вашей базой данных с учетом регистра?2. Спасибо за письмо, если мне нужно взглянуть на проблему с курсором — какие у меня есть возможности избежать использования курсора? Верхние столбцы используются, потому что мы выполняем поиск строк независимо от регистра. Цель состоит в поиске повторяющихся записей
3. «Верхние столбцы используются, потому что мы выполняем поиск строк независимо от регистра». Это двусмысленный ответ «да» на мой вопрос, используете ли вы сопоставление с учетом регистра? Если параметры сортировки не учитывают регистр,
UPPERвычисляемые столбцы не служат никакой цели, поскольку'all in lowercase' = 'ALL IN LOWERCASE'будут возвращатьсяTrue.4. @MichaelEriksen перепишите все как запросы UPDATE, INSERT, DELETE или SELECT. Все операторы изменения данных запроса могут работать с данными, поступающими из таблиц или запросов, например
INSERT .. SELECT FROM ... WHERE.5. Подробно? У меня нет времени. В общем, вместо того, чтобы думать об обработке одной строки, подумайте о том, как выполнить набор операций. Большая часть того, что вы делаете, похоже, фильтрует данные. Выясните, как фильтровать эти данные как группу, а не по строке. SQL — это язык, основанный на множестве. Это работает лучше всего, если вы используете его таким образом.