December 6, 2006 at 9:31 am
Hi
i have to do some updates to the temp table which is below,The tables from where iam doing updates
has more then 6377738 rows in each of the two tables which are
GE_Claim
GE_Reserve
Update #tmp
Set ClaimNumber = c.ClaimNumber,LastName = c.LastName,FirstName = c.FirstName,LossDate = c.LossDate,InClaimFile = 1
From GE_Claim c Inner Join GE_Reserve r
On c.PolicyNumber = r.PolicyNumber And c.TableCode = r.TableCode And c.SeriesCode = r.SeriesCode
And c.IssueAge = r.IssueAge And c.Gender = r.Gender
And c.EffectiveDate = r.EffectiveDate
Where c.LossDate in (Select Max(LossDate) from GE_Claim
Where PolicyNumber = c.PolicyNumber And TableCode = c.TableCode And SeriesCode = c.SeriesCode
Group By PolicyNumber, TableCode, SeriesCode)
what happens is when i run this query it takes a very long time to execute,is there any solution to this ?
December 6, 2006 at 9:47 am
Make sure you have indexes properly defined on the two tables you are selecting from. Best candidates are those columns used in joins and WHERE clauses. Maybe a covering index on PolicyNumber, TableCode, SeriesCode, IssueAge, Gender and EffectiveDate on both tables would be a good idea. You have to balance index creation with the overhead of maintaining them: if you won't be running this query very often then it may be better to take the hit of the long running time rather than create indexes that may only be seldom used elsewhere.
Also, you may want to experiment with rewriting the last part ("Where c.LossDate...") as a join with the derived table. I don't know whether this will have any effect, but it's worth trying.
Is auto update statistics turned on? Your problem may be that the query optimizer is choosing the wrong execution plan because of out-of-date statistics.
Compare execution plans before you run the queries if you suspect they're going to take a long time... and don't forget to run DBCC FREEPROCCACHE each time first.
Good luck!
John
December 6, 2006 at 9:51 am
any other option i have tried all these but no luck any other suggestion?
December 6, 2006 at 9:54 am
The syntax is incorrect, you aren't correlating the table being updated:
Update T
Set ClaimNumber = c.ClaimNumber,LastName = c.LastName,FirstName = c.FirstName,LossDate = c.LossDate,InClaimFile = 1
From #Tmp As T
Inner Join GE_Claim As c
<Join the #Tmp key columns here>
December 6, 2006 at 10:06 am
i have to make a join between table GE_Claim and GE_Reserve.
can you explain a little more and show me what are you saying ,may be i did not understood it
Inner Join GE_Claim As c
<Join the #Tmp key columns here>
December 6, 2006 at 10:11 am
You have 3 tables involved in your UPDATE query.
1 is the target of the update (#Tmp)
2 are the source of the data to be updated (GE_Claim and GE_Reserve).
All three must be joined together in the FROM. Failure to join #Tmp (via it's alias of "T" in my example) results in every row in #Tmp being updated for every row in the other 2. Produces the wrong result and creates a cartesian product that generates many millions of rows and runs for hours or days.
You need to join #Tmp in the FROM.
December 6, 2006 at 10:24 am
can you show me the syntax how to do it.
Thanks
December 6, 2006 at 10:26 am
Insufficient info.
What are the column or columns that uniquelty identify each #Tmp record and join it to the resultset of GE_Claim and GE_Reserve.
December 6, 2006 at 10:37 am
PolicyNumber, TableCode, SeriesCode,---exists both in GE_Claim and GE_Reserve
LossDate,InClaimFile----------------exists in GE_Claim
WaiverofPremium------------------exists in GE_Reserve
i have created a temp table as follows
create table #tmp
(PolicyNumber varchar(10)
, TableCode varchar(3)
, SeriesCode varchar(1)
, IssueAge tinyint
, Gender char
, EffectiveDate smalldatetime
, WaiverOfPremium decimal
, ClaimNumber varchar(7)
, LastName varchar(20)
, FirstName varchar(15)
, LossDate datetime
, InClaimFile bit
)
December 7, 2006 at 6:29 am
Update #tmp
Set ClaimNumber = c.ClaimNumber,LastName = c.LastName,FirstName = c.FirstName,LossDate = c.LossDate,InClaimFile = 1
From GE_Claim c Inner Join GE_Reserve r
On c.PolicyNumber = r.PolicyNumber And c.TableCode = r.TableCode And c.SeriesCode = r.SeriesCode
And c.IssueAge = r.IssueAge And c.Gender = r.Gender
And c.EffectiveDate = r.EffectiveDate
Where c.LossDate in (Select Max(LossDate) from GE_Claim
Where PolicyNumber = c.PolicyNumber And TableCode = c.TableCode And SeriesCode = c.SeriesCode
Group By PolicyNumber, TableCode, SeriesCode)
Perhaps you should create a temporary table with the latest "LossDate" for each PolicyNumber/TableCode/SerisCode combination, and then do an inner join to that temporary table instead of doing the expensive sub-select...
December 7, 2006 at 5:04 pm
Select Max(LossDate) as LossDate
INTO #GE_Claim_LossDate
from GE_Claim
Group By PolicyNumber, TableCode, SeriesCode
GO
Update T
Set ClaimNumber = c.ClaimNumber,LastName = c.LastName,FirstName = c.FirstName,LossDate = c.LossDate,InClaimFile = 1
From #tmp T
Inner join GE_Claim c
On T.PolicyNumber = c.PolicyNumber And T.TableCode = c.TableCode And T.SeriesCode = c.SeriesCode
And T.IssueAge = c.IssueAge And T.Gender = c.Gender
And T.EffectiveDate = c.EffectiveDate
Inner Join GE_Reserve r
On c.PolicyNumber = r.PolicyNumber And c.TableCode = r.TableCode And c.SeriesCode = r.SeriesCode
And c.IssueAge = r.IssueAge And c.Gender = r.Gender
And c.EffectiveDate = r.EffectiveDate
Inner Join #GE_Claim_LossDate L
on c.Lossdate = L.Lossdate
/*
This is assuming you already have data in #tmp. You're question might be how to INSERT, not UPDATE as it was in another thread in this forum.
*/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply