March 23, 2012 at 4:23 pm
Hello All,
I need a quick help on the sp that i m working on. I m a DBA and am not much into the development stuff. The issue is, I have a sp when executed it is taking about 3mins, i have been asked to tune it so that it would take less than a minute to fetch the records.
This is the stored procedure.
Thanks a ton for ur time and help!! Please let me know if i need to provide any information.
USE [database name]
GO
/****** Object: StoredProcedure [dbo].[StoreProcedureName] Script Date: 03/23/2012 15:10:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[StoreProcedureName]
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
--EXEC StoreProcedureName '12/01/2011','02/27/2012'
IF @StartDate IS NULL BEGIN
SET @StartDate = DATEADD(day, -30, GETDATE()) -- GETDATE() includes the time
END
IF @EndDate IS NULL BEGIN
SET @EndDate = GETDATE()
END
-- Create our temp tables
CREATE TABLE #DimPolicyResults
(
PolicyID INT NOT NULL,
PolicyNum VARCHAR(100),
InsuredName1 VARCHAR(100),
InsuredName2 VARCHAR(100),
UWName VARCHAR(100),
UWTeam VARCHAR(100),
AgentID INT,
AgentNumber VARCHAR(100),
StateID INT,
[State] VARCHAR(100),
ReceivedDate DATETIME,
BoundPremium INT,
PolicyEffectiveDate DATETIME
)
CREATE TABLE #VQuotesResults
(
QuoteID INT NOT NULL,
QuoteNum INT,
QuoteStatus VARCHAR(100),
ApplicantFirstName VARCHAR(100),
ApplicantLastName VARCHAR(100),
ApplicantDBA VARCHAR(100),
BusinessName VARCHAR(100),
AgentID INT,
StateID INT,
ReceivedDate DATETIME, -- Just for testing.. for now
Checked BIT DEFAULT(0) -- This indicates if this one has been searched for in the dim_policy results temp table
)
DECLARE @ResultTable TABLE
(
QuoteID INT NOT NULL,
ApplicantFirstName VARCHAR(64),
ApplicantLastName VARCHAR(64),
ApplicantDBA VARCHAR(64),
BusinessName VARCHAR(64),
QuoteNum INT,
QuoteStatus VARCHAR(32),
AgentNumber VARCHAR(16),
[State] VARCHAR(16),
PolicyID INT,
PolicyNumber VARCHAR(16),
InsuredName1 VARCHAR(64),
InsuredName2 VARCHAR(64),
UWName VARCHAR(32),
UWTeam VARCHAR(16),
ReceivedDate DATETIME,
BoundPremium INT,
DimPolAgentID INT,
DimPolStateID INT
)
DECLARE
-- These are temp from the #VQuotesResults so we can check into the #DimPolicyResults table
@TempVQQuoteID INT,
@TempVQAppFName VARCHAR(64),
@TempVQAppLName VARCHAR(64),
@TempVQAppDba VARCHAR(64),
@TempVQBusName VARCHAR(64),
@TempVQAgentID INT,
@TempVQStateID INT,
@TempVQQuoteStatus VARCHAR(32),
@TempVQQuoteNum INT
INSERT INTO #DimPolicyResults (PolicyID, PolicyNum, InsuredName1, InsuredName2, UWName, UWTeam, AgentID, AgentNumber, StateID,
[State], ReceivedDate, BoundPremium, PolicyEffectiveDate)
SELECT dp.policy_id, RTRIM(LTRIM(dp.policy_number)), ISNULL(RTRIM(LTRIM(dp.insured_name_1)), ''), ISNULL(RTRIM(LTRIM(dp.insured_name_2)), ''),
da.uwfname + ' ' + da.uwlname, dp.UWTeam, dp.agent_id, CAST(da.agent_number AS VARCHAR(8)) + '-' + CAST(da.sub_agent_number AS VARCHAR(8)),
ts.tax_state_id, dp.[state], dp.received_date, dp.written_prop + dp.written_liab, dp.pol_eff_date
FROM aip.dbo.dim_policy dp
JOIN [AIP].[dbo].[dim_taxstate] ts ON dp.[state] = ts.[state]
JOIN [AIP].[dbo].[dim_agent] da ON dp.agent_id = da.agent_id
WHERE dp.received_date > CONVERT(CHAR(10), DATEADD(mm, -1, GETDATE()) - DAY(GETDATE()), 101) -- Always go back one month for policies
AND (dp.prior_policy IS NULL OR dp.prior_policy = '00000000')
-- AND RTRIM(LTRIM(dp.policy_status)) <> 'EXPIRED'
ORDER BY dp.policy_id
--SELECT * FROM #DimPolicyResults
INSERT INTO #VQuotesResults (QuoteID, QuoteNum, QuoteStatus, ApplicantFirstName, ApplicantLastName, ApplicantDBA, BusinessName,
AgentID, StateID, ReceivedDate)
SELECT vq.QuoteID, vq.quote_number, s.[Status], ISNULL(RTRIM(LTRIM(vq.applicant_fname)), ''), ISNULL(RTRIM(LTRIM(vq.applicant_lname)), ''),
ISNULL(RTRIM(LTRIM(vq.applicant_dba)), ''), ISNULL(RTRIM(LTRIM(vq.BusinessName)), ''), vq.AgentID, vq.StateID, vq.received_date
FROM v_quotes vq
JOIN [Status] s ON vq.StatusID = s.StatusID
WHERE vq.received_date BETWEEN @StartDate AND @EndDate
AND vq.StatusID <> 3 -- Don't want bound ones
AND vq.IsActive = 1
ORDER BY vq.QuoteID
--SELECT * FROM #VQuotesResults
-- Go through the quotes we found and check against the policies from dim_policy
WHILE EXISTS(SELECT * FROM #VQuotesResults WHERE Checked = 0) BEGIN
SELECT TOP 1 @TempVQQuoteID = QuoteID, @TempVQAppFName = ApplicantFirstName, @TempVQAppLName = ApplicantLastName,
@TempVQAppDba = ApplicantDBA, @TempVQBusName = BusinessName, @TempVQAgentID = AgentID, @TempVQStateID = StateID,
@TempVQQuoteStatus = QuoteStatus, @TempVQQuoteNum = QuoteNum
FROM #VQuotesResults WHERE Checked = 0 ORDER BY QuoteID
INSERT INTO @ResultTable (QuoteID, ApplicantFirstName, ApplicantLastName, ApplicantDBA, BusinessName, QuoteNum, QuoteStatus, AgentNumber, [State],
PolicyID, PolicyNumber, InsuredName1, InsuredName2, UWName, UWTeam, ReceivedDate, BoundPremium, DimPolAgentID, DimPolStateID)
SELECT @TempVQQuoteID, @TempVQAppFName, @TempVQAppLName, @TempVQAppDba, @TempVQBusName, @TempVQQuoteNum, @TempVQQuoteStatus, dpr.AgentNumber, dpr.[State],
dpr.PolicyID, dpr.PolicyNum, dpr.InsuredName1, dpr.InsuredName2, dpr.UWName, dpr.UWTeam, dpr.ReceivedDate, dpr.BoundPremium, dpr.AgentID, dpr.StateID
FROM #DimPolicyResults AS dpr
/*
WHERE dpr.AgentID = @TempVQAgentID AND dpr.StateID = @TempVQStateID -- Can't have a match if these don't match
AND (((@TempVQAppFName <> '' AND @TempVQAppFName IS NOT NULL AND @TempVQAppLName <> '' AND @TempVQAppLName IS NOT NULL)
AND CHARINDEX(@TempVQAppFName + ' ' + @TempVQAppLName, dpr.InsuredName1) != 0 OR CHARINDEX(@TempVQAppFName + ' ' + @TempVQAppLName, dpr.InsuredName2) != 0)
OR (@TempVQAppFName <> '' AND @TempVQAppFName IS NOT NULL AND (CHARINDEX(@TempVQAppFName, dpr.InsuredName1) != 0 OR CHARINDEX(@TempVQAppFName, dpr.InsuredName2) != 0))
OR (@TempVQAppLName <> '' AND @TempVQAppLName IS NOT NULL AND (CHARINDEX(@TempVQAppLName, dpr.InsuredName1) != 0 OR CHARINDEX(@TempVQAppLName, dpr.InsuredName2) != 0))
OR (@TempVQAppDba <> '' AND @TempVQAppDba IS NOT NULL AND (CHARINDEX(@TempVQAppDba, dpr.InsuredName1) != 0 OR CHARINDEX(@TempVQAppDba, dpr.InsuredName2) != 0))
OR (@TempVQBusName <> '' AND @TempVQBusName IS NOT NULL AND (CHARINDEX(@TempVQBusName, dpr.InsuredName1) != 0 OR CHARINDEX(@TempVQBusName, dpr.InsuredName2) != 0)))
-- 19652 matches
*/
WHERE dpr.AgentID = @TempVQAgentID AND dpr.StateID = @TempVQStateID
AND (((@TempVQAppFName <> '' AND @TempVQAppFName IS NOT NULL AND @TempVQAppLName <> '' AND @TempVQAppLName IS NOT NULL)
AND dpr.InsuredName1 LIKE '%' + @TempVQAppFName + ' ' + @TempVQAppLName + '%' OR dpr.InsuredName2 LIKE '%' + @TempVQAppFName + ' ' + @TempVQAppLName)
OR (@TempVQAppFName <> '' AND @TempVQAppFName IS NOT NULL AND (dpr.InsuredName1 LIKE '%' + @TempVQAppFName + '%' OR dpr.InsuredName2 LIKE '%' + @TempVQAppFName + '%'))
OR (@TempVQAppLName <> '' AND @TempVQAppLName IS NOT NULL AND (dpr.InsuredName1 LIKE '%' + @TempVQAppLName + '%' OR dpr.InsuredName2 LIKE '%' + @TempVQAppLName + '%'))
OR (@TempVQAppDba <> '' AND @TempVQAppDba IS NOT NULL AND (dpr.InsuredName1 LIKE '%' + @TempVQAppDba + '%' OR dpr.InsuredName2 LIKE '%' + @TempVQAppDba + '%'))
OR (@TempVQBusName <> '' AND @TempVQBusName IS NOT NULL AND (dpr.InsuredName1 LIKE '%' + @TempVQBusName + '%' OR dpr.InsuredName2 LIKE '%' + @TempVQBusName + '%')))
-- 280 matches (3 months of quotes) 645 (6 months of quotes)
UPDATE #VQuotesResults SET Checked = 1 WHERE QuoteID = @TempVQQuoteID
END
DROP TABLE #DimPolicyResults
DROP TABLE #VQuotesResults
SELECT * FROM @ResultTable
END
GO
March 23, 2012 at 4:32 pm
We will need the DDL (CREATE TABLE statement(s), including INDEX definitions) for any source table(s), some sample data for the source table(s) (a series of INSERT INTO statements) (not a lot, just enough to represent the problem domain and NOT REAL DATA), expected results based on the sample data.
Not too sure what can be done, though, because a quick look at the code shows a lot of <>, not null, and like % + some string value + % in the WHERE clauses that may cause table/clustered index scans.
March 23, 2012 at 4:42 pm
One of the source tables have Clusterd index and other table has 1 Unique,Non-clustered and 12 Non-Unique, Clustered indexes. I tried using the estimated execution plan which suggested me for some missing indexes and i have created them and also i have added indexes on the temp tables. I could able to being it down to only 2 mins.
March 23, 2012 at 4:43 pm
SS999 (3/23/2012)
One of the source tables have Clusterd index and other table has 1 Unique,Non-clustered and 12 Non-Unique, Clustered indexes. I tried using the estimated execution plan which suggested me for some missing indexes and i have created them and also i have added indexes on the temp tables. I could able to being it down to only 2 mins.
We will need the DDL (CREATE TABLE statement(s), including INDEX definitions) for any source table(s), some sample data for the source table(s) (a series of INSERT INTO statements) (not a lot, just enough to represent the problem domain and NOT REAL DATA), expected results based on the sample data.
March 23, 2012 at 4:46 pm
And the current execution plan please, The actual plan
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2012 at 4:59 pm
when i m trying to execute with actual execution plan it is taking more than 8 mins and getting hung :(. I have no clue what is going on.
March 23, 2012 at 5:26 pm
i have just shortened the startdate and enddate for just 10days with the actual execution plan. when i execute without selecting it it took be about 58secs to complete. When i m executing with selecting the actual plan, it still executing and so far it been 7 and a half mins.
Any clue??
March 25, 2012 at 3:33 pm
Full Version number of your release?
Might need a CU or SP?
March 25, 2012 at 7:33 pm
SS999 (3/23/2012)
i have just shortened the startdate and enddate for just 10days with the actual execution plan. when i execute without selecting it it took be about 58secs to complete. When i m executing with selecting the actual plan, it still executing and so far it been 7 and a half mins.Any clue??
Yes... stop trying. The code has a WHILE Loop in it and it's going to try to generate multiple execution plans for each iteration. It'll get to 100 plans and then start printing error messages for each and every "step" in each and every interation of the loop.
--Jeff Moden
March 25, 2012 at 8:02 pm
I may have to do this in several steps. Here's my "first blush" look at it.
The very first thing to do is to remove the ORDER BY from the inserts to the temp tables. They serve absolutely no logical purpose and do nothing but waste precious time and clock cycles.
Second, there's not much we can do to "tune" this code because it's actually the equivalent of a huge cross join cleverly disguised by a loop. The code builds tables "A" and "B" and then for every row in table "B", it searches through all the rows in table "A" just like any many-to-many join would. The code needs to be rewritten to do a join between the two temp tables and then table "C" (the table variable) can be totally eliminated. Actually, it's worse than a mere cross-join because the code also goes back and updates table "B" and we know there're no indexes to help that along so it's going to make for an extra table scan for each iteration there, as well.
In the WHERE clause, you don't need to check to see if something is either NULL or BLANK. If you use SomeColumn > '' only, then it can't possibly be NULL because if it's greater than a BLANK, it can't possibly be NULL.
The only places where you might be able to "tune" this code is in the population of table "A" and "B" temp tables. The correct indexes could help that code quite a bit. If you'd manually select and execute just the SELECT portions of those two pieces of code, you could quickly get a single execution plan for each of those pieces of code where folks could make a recommendation for you.
I'd have to sit down and figure out the precise logic to figure out the actual join between table "A" and "B" (it looks like "simple" "fuzzy" name checking) but that WHILE loop has to go. It's the cause of the accidental worse-than-a-cross-join problem.
--Jeff Moden
March 25, 2012 at 8:07 pm
Here's the next piece. Drop the CREATE TABLE code for the two temp tables and use SELECT/INTO instead. You're pushing data to temp tables which is in the simple recovery mode. Since that's true, SELECT/INTO will use minimal logging which will give you a pretty good boost in performance.
--Jeff Moden
March 25, 2012 at 8:11 pm
Ok... this just isn't right. The @StartDate variable is already set to 30 days ago. It won't help much with performance but why are we recalculating it in the following WHERE clause???
WHERE dp.received_date > CONVERT(CHAR(10), DATEADD(mm, -1, GETDATE()) - DAY(GETDATE()), 101) -- Always go back one month for policies
--Jeff Moden
March 25, 2012 at 8:13 pm
Jeez... someone was crazy about order. The code that populates the variables has another ORDER BY in it. Lose the ORDER BY in the following code.
SELECT TOP 1 @TempVQQuoteID = QuoteID, @TempVQAppFName = ApplicantFirstName, @TempVQAppLName = ApplicantLastName,
@TempVQAppDba = ApplicantDBA, @TempVQBusName = BusinessName, @TempVQAgentID = AgentID, @TempVQStateID = StateID,
@TempVQQuoteStatus = QuoteStatus, @TempVQQuoteNum = QuoteNum
FROM #VQuotesResults WHERE Checked = 0 ORDER BY QuoteID
--Jeff Moden
March 25, 2012 at 8:24 pm
Hooooo boooyyy. I'll bet credits to navy beens that the following is actually a view. We need to see the code for that...
FROM v_quotes vq
Also, I'm assuming that the Status table is quite bit smaller than that view. In the following code...
FROM v_quotes vq
JOIN [Status] s ON vq.StatusID = s.StatusID
WHERE vq.received_date BETWEEN @StartDate AND @EndDate
AND vq.StatusID <> 3 -- Don't want bound ones
AND vq.IsActive = 1
If you change the check for vq.StatusID <> 3 to s.StatusID <> 3, you should have a better chance of using indexes on the table that underly the view. There could be a significant performance increase there but don't know for sure because I can't see what's going on from here. If there's not already one there, the underlying table(s) responsible for the StatusID column in the view would certainly benefit from an index. So would the Received_Date column and the IsActive column. I recommend a composite index for the task of indexing the 3 if they're all on the same table.
You might also want to dig a little deeper and find out if the view is really necessary. The view could have joins to a bunch of tables that aren't necessary for this query.
--Jeff Moden
March 25, 2012 at 8:36 pm
I guess that's about it. Most of the changes I've recommended can be done without getting rid of the WHILE loop. However, that's a large part of the problem as it forms not one but two accidental cross-joins because of the table or index scans that I think may be happening. It REALLY needs to be replaced by a join between the two temp tables. I suppose we could help you do that but now would be a good time to get some developers from your company involved. It shouldn't be your job to rewrite really bad code.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply