September 27, 2018 at 6:43 am
Hello,
I have this stored proc I am trying to determine if it can be optimized for better performance. Currently it takes 15 minutes to INSERT 28,000 records into [Email] table on a VM with multiple CPU and 16GB RAM on SQL Server 2014. If anyone could advise it would be appreciated.
USE [cool_mail]
GO
/****** Object: StoredProcedure [dbo].[procInsertBatchEmailList] Script Date: 9/26/2018 8:55:16 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procInsertBatchEmailList]
-- Params
@batchID int,
@responseCode int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @rowcount int,
@agentID int,
@agentInfoID int,
@emailStatusID int,
@totalRowCount int,
@sendEmailToCustomers bit,
@error int,
@isAgentType bit,
@repID int
BEGIN TRY
BEGIN TRANSACTION tranInsertBatchEmailList
IF OBJECT_ID('#TempAgentRep') IS NOT NULL
DROP TABLE #TempAgentRep
CREATE TABLE #TempAgentRep(
guid uniqueidentifier NOT NULL,
repID int NOT NULL,
firstName varchar(50) NOT NULL,
lastName varchar(50) NOT NULL,
phone varchar(20) NULL,
emailAddress varchar(100) NOT NULL,
hasPhoto bit NOT NULL,
keycode varchar(30) NULL)
IF OBJECT_ID('#TempEmail') IS NOT NULL
DROP TABLE #TempEmail
CREATE TABLE #TempEmail(
securityCode uniqueidentifier NOT NULL,
batchId int NOT NULL,
emailStatusId int NOT NULL,
clientName varchar(255) NOT NULL,
clientEmail varchar(255) NULL,
keycode varchar(30) NULL,
agentID int NULL)
SET @totalRowCount = 0
SELECT @emailStatusID = emailStatusID
FROM EmailStatus WHERE description = 'Initialized' -- initial email status
SELECT @isAgentType = isAgentType FROM BatchType
INNER JOIN Batch ON Batch.batchTypeID = BatchType.batchTypeiD
WHERE batchID = @batchID
-- if batch is of agent type
IF @isAgentType = 1
BEGIN
-- Insert agent data into email
PRINT 'Insert agent data into email'
INSERT INTO Email (securityCode, batchID, emailStatusID, clientName, clientEmail)
SELECT NEWID(), @batchID, @emailStatusID, AgentName, AgentEMail
FROM cqo1.dbo.AgentInfo
LEFT JOIN cqo1.dbo.AgentMessage On AgentInfo.ID = AgentMessage.AgentInfoID
AND AgentMessage.isActive = 1
WHERE AgentInfo.ID IN (SELECT agentInfoID FROM BatchAgentInfo WHERE batchID = @batchID)
END
ELSE
BEGIN
DECLARE agent_cursor CURSOR FOR
SELECT agentInfoID FROM BatchAgentInfo WHERE batchID = @batchID
OPEN agent_cursor
--PreFetch
FETCH NEXT FROM agent_cursor
INTO @agentInfoID
--Start processing
PRINT 'Start processing...'
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Agent (agentInfoID, name, emailAddress, phone, logofile, hasBinaryLogo, message, QRCode, QRCodeUrl, isActive)
SELECT AgentInfo.ID, AgentName, AgentEMail, ContactPhone, LogoName, CASE WHEN AgentInfo.logoImage IS NULL THEN 0 ELSE 1 END, AgentMessage.description, qrCodeDesc, qrCodeUrl, 1
FROM cqo1.dbo.AgentInfo
LEFT JOIN cqo1.dbo.AgentMessage On AgentInfo.ID = AgentMessage.AgentInfoID
AND AgentMessage.isActive = 1
WHERE AgentInfo.ID = @agentInfoID
SELECT @agentID = @@IDENTITY, @error = @@ERROR
IF @error <> 0 OR @rowCount < 0
BEGIN
PRINT 'Insert into Agent failed'
RAISERROR('Insert into Agent failed', 11, 1)
END
-- delete all data from temp table
TRUNCATE TABLE #TempEmail
PRINT '(TRUNCATE) deleted all data from temp table'
-- insert racode data into email table
INSERT INTO #TempEmail (securityCode, batchID, emailStatusID, clientName, clientEmail, keycode, agentID)
SELECT NEWID(), @batchID, @emailStatusID, RACodes.UserName, RACodes.UserEmail, RACodes.RACode, @agentID
FROM cqo1.dbo.RACodes
WHERE ID IN
(
SELECT MAX(ID)
FROM cqo1.dbo.RaCodes
GROUP BY UserEmail, AgentID
HAVING UserEmail IN (SELECT UserEmail FROM cqo1.dbo.RaCodes
WHERE AgentID = @agentInfoID
AND UserEmail IS NOT NULL
AND UserEmail <> ''
AND emailOptOut = 0
AND cancelled = 0))
AND AgentID = @agentInfoID
ORDER BY RACodes.AgentID, RACodes.USerEmail DESC
PRINT 'inserted racode data into temp table'
INSERT INTO Email (securityCode, batchID, emailStatusID, clientName, clientEmail, keycode, agentID)
SELECT securityCode, batchID, emailStatusID, clientName, clientEmail, keycode, agentID FROM #TempEmail
WHERE keycode NOT IN ( SELECT e.keycode from Email e
INNER JOIN Batch b on e.batchID = b.batchID where b.welcomeID IS NOT NULL
AND e.emailStatusID = 1 AND b.batchStatusID = 1 AND keyCode is NOT NULL )
PRINT 'inserted Email data into temp table'
SELECT @error = @@ERROR, @rowCount = @@ROWCOUNT
IF @error <> 0 OR @rowCount < 0
BEGIN
PRINT 'Insert into Email failed'
RAISERROR('Insert into Email failed', 11, 1)
END
-- SET @totalRowCount = @totalRowCount + @rowCount
FETCH NEXT FROM agent_cursor
INTO @agentInfoID
END
--Close cursor
CLOSE agent_cursor
DEALLOCATE agent_cursor
-- INSERT AgentRep data
INSERT INTO #TempAgentRep(guid, repID, firstName, lastName, phone, emailAddress, hasPhoto, keyCode)
SELECT DISTINCT NEWID(), asi.id, asi.agentFirstName, asi.agentLastName, asi.agentPhone, asi.agentEmail, CASE WHEN asi.photograph IS NULL THEN 0 ELSE 1 END , asic.raCode
FROM cqo1.dbo.AgentSubInfo asi
INNER JOIN cqo1.dbo.AgentSubInfoCodes asic
ON asic.agentSubInfoID = asi.id
AND asic.AgentSubInfoId <> -1
WHERE asic.raCode IN (SELECT keyCode FROM dbo.Email WHERE batchID = @batchID)
INSERT INTO AgentRep (agentIndentifier, repID, firstName, lastName, phone, emailAddress, hasPhoto)
SELECT guid, repID, firstName, lastName, phone, emailAddress, hasPhoto FROM #TempAgentRep
SELECT @error = @@ERROR
IF @error <> 0
RAISERROR('Insert into AgentRep failed', 11, 1)
-- Update Email with agentRepID
UPDATE Email
SET agentRepID = ar.agentRepID
FROM Email
INNER JOIN #TempAgentRep asic
ON Email.keyCode = asic.keycode
INNER JOIN AgentRep ar
ON asic.repID = ar.repID
AND ar.agentIndentifier = asic.guid
--INNER JOIN #TempAgentRep tar
--ON ar.agentIndentifier = tar.guid
--WHERE asic.keycode IN (SELECT keyCode FROM dbo.Email WHERE batchID = @batchID)
--AND asic.agentSubInfoId <> -1
WHERE Email.batchID = @batchID
SELECT @error = @@ERROR
IF @error <> 0
BEGIN
PRINT 'Update of Email failed'
RAISERROR('Update of Email failed', 11, 1)
END
END
COMMIT TRANSACTION tranInsertBatchEmailList
SET @responseCode = 1
RETURN @totalRowCount
END TRY
BEGIN CATCH
DECLARE @errorMessage nvarchar(4000),
@errorSeverity int,
@errorState int,
@errorLine int
--Close cursor
CLOSE agent_cursor
DEALLOCATE agent_cursor
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION tranInsertBatchEmailList
SELECT
@errorMessage = ERROR_MESSAGE(),
@errorSeverity = ERROR_SEVERITY(),
@errorState = ERROR_STATE(),
@errorLine = ERROR_LINE();
PRINT 'Unable to save Batch data for ' + CAST(ISNULL(@batchID, '') AS VARCHAR(10)) +
';' + @errorMessage + ' line number: ' + CAST(@errorLine AS VARCHAR(10))
SELECT @errorMessage = 'Unable to save Batch data for ' + CAST(ISNULL(@batchID, '') AS VARCHAR(10)) +
';' + @errorMessage + ' line number: ' + CAST(@errorLine AS VARCHAR(10))
RAISERROR (@errorMessage, @errorSeverity, @errorState);
RETURN -1
END CATCH
END
September 27, 2018 at 10:00 am
I would start with the cursor. Have you narrowed down which part is taking the longest? You can probably remove the cursor and do a set based operation and output certain values into a temp table.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 28, 2018 at 6:46 am
All those SELECT statements used to put the data together could potentially be tuned. I'd check each of the execution plans to see what's happening. Also this:WHERE asic.raCode IN (SELECT keyCode FROM dbo.Email WHERE batchID = @batchID)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply