March 26, 2012 at 9:48 am
Hello Jeff,
Thank u so much for the replies. I would definetely consider ur suggestions and ask my developer to re-write whatever it takes to tune-up this code with ur suggesstions.
Thanks bunch to u and all for the feedback.
Thanks!!!
March 26, 2012 at 9:43 pm
You're welcome but you still might want to post the code for the view.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2012 at 4:10 am
Jeff Moden (3/25/2012)
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.
Doesn't that code need the ORDER BY to ensure the SELECT TOP 1 gets the right item?
March 27, 2012 at 5:06 am
paul.knibbs (3/27/2012)
Jeff Moden (3/25/2012)
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.Doesn't that code need the ORDER BY to ensure the SELECT TOP 1 gets the right item?
It would be a good idea. Top without an order doesn't guarantee which row is fetched, especially if that can/does use a nonclustered index on something other than QuoteID
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 27, 2012 at 7:15 am
There are too many tables involved in your selects. execution plans is a must; AND index definitions.
March 27, 2012 at 8:57 am
Hi Friends,
I am having kinda same problem...
I have more than 7 million records in a table.. i was wondering to see how much time to execute and :ermm: it takes more than 15 minutes and it was keep on going...
and the more saddest part is i have to join with another table for three times which has 4.5 million records.....
I have no idea how much time it is going to take for execution....
I hope i can't do much with this...but give me some ideas friends...i tried the above said script on production server and it took more than 12 hours and did not completed the execution...
Friends, Give me your idea to increase the performance of the query? it frustrates me...
let me know if anything needed from me...
Thanks,
Charmer
March 27, 2012 at 9:30 am
Charmer (3/27/2012)
Hi Friends,I am having kinda same problem...
I have more than 7 million records in a table.. i was wondering to see how much time to execute and :ermm: it takes more than 15 minutes and it was keep on going...
and the more saddest part is i have to join with another table for three times which has 4.5 million records.....
I have no idea how much time it is going to take for execution....
I hope i can't do much with this...but give me some ideas friends...i tried the above said script on production server and it took more than 12 hours and did not completed the execution...
Friends, Give me your idea to increase the performance of the query? it frustrates me...
let me know if anything needed from me...
You are running the exact same query? Do you work for the same company?
Jared
CE - Microsoft
March 27, 2012 at 9:47 am
Charmer (3/27/2012)
Hi Friends,I am having kinda same problem...
I have more than 7 million records in a table.. i was wondering to see how much time to execute and :ermm: it takes more than 15 minutes and it was keep on going...
and the more saddest part is i have to join with another table for three times which has 4.5 million records.....
I have no idea how much time it is going to take for execution....
I hope i can't do much with this...but give me some ideas friends...i tried the above said script on production server and it took more than 12 hours and did not completed the execution...
Friends, Give me your idea to increase the performance of the query? it frustrates me...
let me know if anything needed from me...
Start a new thread and post the DDL (CREATE TABLE statements) for the tables including indexes defined, sample data for the tables (as a series of INSERT INTO statements) for each of the tables, the expected results based on the sample data (not a lot of data, just enough to represent the problem domain), the actual execution plan for the query, and the query itself.
March 27, 2012 at 9:55 am
SQLKnowItAll (3/27/2012)
Charmer (3/27/2012)
Hi Friends,I am having kinda same problem...
I have more than 7 million records in a table.. i was wondering to see how much time to execute and :ermm: it takes more than 15 minutes and it was keep on going...
and the more saddest part is i have to join with another table for three times which has 4.5 million records.....
I have no idea how much time it is going to take for execution....
I hope i can't do much with this...but give me some ideas friends...i tried the above said script on production server and it took more than 12 hours and did not completed the execution...
Friends, Give me your idea to increase the performance of the query? it frustrates me...
let me know if anything needed from me...
You are running the exact same query? Do you work for the same company?
Yes i run the same query....since it is having 7 million records , i am not able to generate scripts for sample DML's to post here...The problem is with the size of the table i hope...
Thanks,
Charmer
March 27, 2012 at 10:00 am
http://www.sqlservercentral.com/Forums/Topic1273640-391-1.aspx
Please post your suggestion over here...i started a separate thread...
Thanks,
Charmer
March 27, 2012 at 10:45 am
To Original OP ;-):
I'm not sure why I've done so, but here we are...
Please note the code formatting: it makes it much more readable therefore better maintainable...
CREATE PROCEDURE [dbo].[StoreProcedureName] (@StartDate DATETIME
,@EndDate DATETIME)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OneMonthBackDate DATETIME
SET @OneMonthBackDate = CAST(DATEADD(mm, -1, GETDATE()) - DAY(GETDATE()) AS DATE)
-- default input params
SELECT @StartDate = ISNULL(@StartDate, DATEADD(day, -30, GETDATE())) -- GETDATE() includes the time
,@EndDate = ISNULL(@EndDate, GETDATE())
-- create and populate #DimPolicyResults
SELECT dp.policy_id AS PolicyID
,RTRIM(LTRIM(dp.policy_number)) AS PolicyNum
,ISNULL(RTRIM(LTRIM(dp.insured_name_1)), '') AS InsuredName1
,ISNULL(RTRIM(LTRIM(dp.insured_name_2)), '') AS InsuredName2
,da.uwfname + ' ' + da.uwlname AS UWName
,dp.UWTeam AS UWTeam
,dp.agent_id AS AgentID
,CAST(da.agent_number AS VARCHAR(8)) + '-'
+ CAST(da.sub_agent_number AS VARCHAR(8)) AS AgentNumber
,ts.tax_state_id AS StateID
,dp.[state] AS [State]
,dp.received_date AS ReceivedDate
,dp.written_prop + dp.written_liab AS BoundPremium
,dp.pol_eff_date AS PolicyEffectiveDate
INTO #DimPolicyResults
FROM aip.dbo.dim_policy AS dp
JOIN [AIP].[dbo].[dim_taxstate] AS ts
ON dp.[state] = ts.[state]
JOIN [AIP].[dbo].[dim_agent] AS da
ON dp.agent_id = da.agent_id
WHERE dp.received_date > @OneMonthBackDate -- Always go back one month for policies
AND ( dp.prior_policy IS NULL
OR dp.prior_policy = '00000000')
-- select * from #DimPolicyResults
-- looks like the following index may help
CREATE CLUSTERED INDEX ixc_#DimPolicyResults_2 ON #DimPolicyResults(AgentID, StateID)
-- create and populate #VQuotesResults
SELECT vq.QuoteID AS QuoteID
,vq.quote_number AS QuoteNum
,s.[Status] AS QuoteStatus
,ISNULL(RTRIM(LTRIM(vq.applicant_fname)), '') AS ApplicantFirstName
,ISNULL(RTRIM(LTRIM(vq.applicant_lname)), '') AS ApplicantLastName
,RTRIM(LTRIM(
ISNULL(RTRIM(vq.applicant_fname), '')
+ ' '
+ ISNULL(LTRIM(vq.applicant_lname), '')
)) AS ApplicantFullName -- less to do in further qry
,ISNULL(RTRIM(LTRIM(vq.applicant_dba)), '') AS ApplicantDBA
,ISNULL(RTRIM(LTRIM(vq.BusinessName)), '') AS BusinessName
,vq.AgentID AS AgentID
,vq.StateID AS StateID
-- Just for testing.. for now:
,vq.received_date AS ReceivedDate
-- you don't need any more as no loop used: ,CAST(0 AS BIT) AS Checked
INTO #VQuotesResults
FROM v_quotes AS vq
JOIN [Status] AS 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
-- looks like the following index will help
CREATE CLUSTERED INDEX ix_#VQuotesResults ON #VQuotesResults(AgentID, StateID)
-- GET RID OFF LOOP !!!!!!
-- create and populate ResultTable
-- actually, looks like your sp just need to output this recordset
-- so, you dont need to put it into #table!
SELECT vq.QuoteID AS QuoteID
,vq.ApplicantFirstName AS ApplicantFirstName
,vq.ApplicantLastName AS ApplicantLastName
,vq.ApplicantDBA AS ApplicantDBA
,vq.BusinessName AS BusinessName
,vq.QuoteNum AS QuoteNum
,vq.QuoteStatus AS QuoteStatus
,dpr.AgentNumber AS AgentNumber
,dpr.[State] AS [State]
,dpr.PolicyID AS PolicyID
,dpr.PolicyNum AS PolicyNum
,dpr.InsuredName1 AS InsuredName1
,dpr.InsuredName2 AS InsuredName2
,dpr.UWName AS UWName
,dpr.UWTeam AS UWTeam
,dpr.ReceivedDate AS ReceivedDate
,dpr.BoundPremium AS BoundPremium
,dpr.AgentID AS DimPolAgentID
,dpr.StateID AS DimPolStateID
FROM #DimPolicyResults AS dpr
JOIN #VQuotesResults AS vq
ON
dpr.AgentID = vq.AgentID
AND dpr.StateID = vq.StateID
AND (
(
vq.ApplicantFullName <> ''
AND ( -- your original query is missing these brackets!
dpr.InsuredName1 LIKE '%' + vq.ApplicantFullName + '%'
OR dpr.InsuredName2 LIKE '%' + vq.ApplicantFullName + '%'
) -- your original query is missing these brackets!
)
-- you now don' need to check First and Last names separetely as combined name covers it!
OR
( vq.ApplicantDBA <> ''
AND ( dpr.InsuredName1 LIKE '%' + vq.ApplicantDBA + '%'
OR dpr.InsuredName2 LIKE '%' + vq.ApplicantDBA + '%'
)
)
OR
( vq.BusinessName <> ''
AND ( dpr.InsuredName1 LIKE '%' + vq.BusinessName + '%'
OR dpr.InsuredName2 LIKE '%' + vq.BusinessName + '%'
)
)
)
END
GO
March 27, 2012 at 11:45 am
Hi Elutin,
I m trying to execute ur code to test and i get this error.
Msg 243, Level 16, State 1, Procedure Select_QuotestoBind_TestSample, Line 9
Type DATE is not a defined system type.
March 27, 2012 at 11:48 am
SS999 (3/27/2012)
Hi Elutin,I m trying to execute ur code to test and i get this error.
Msg 243, Level 16, State 1, Procedure Select_QuotestoBind_TestSample, Line 9
Type DATE is not a defined system type.
So you are not using SQL Server 2008 or better?
Jared
CE - Microsoft
March 27, 2012 at 11:53 am
Currently its on sql server 2005.
March 27, 2012 at 12:01 pm
SS999 (3/27/2012)
Currently its on sql server 2005.
Replace
SET @OneMonthBackDate = CAST(DATEADD(mm, -1, GETDATE()) - DAY(GETDATE()) AS DATE)
with this:
SET @OneMonthBackDate = CAST(DATEDIFF(D,0,DATEADD(mm, -1, GETDATE()) - DAY(GETDATE())) AS DATETIME)
Jared
CE - Microsoft
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply