Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
nandan316
Starting Member
1 Post |
Posted - 2013-11-04 : 16:55:57
|
Hi,I am trying to update a table with around 500 million rows. The column i am updating is Cost field. Please see the below listed query. The user defined function runs in less than a minute, so its not the issue. But the update statement is taking around 17 -n 18 hours to complete. I have created a index on BuyID,PageID which are the columns used in the where clause of update statement. Can some one please tell me what i need to do to optimize the query?ThanksQuery:--------declare @year int,@month int,@StartDate DateTime,@EndDate DateTime,@Threshold intSET @year=2013SET @month=8SET @StartDate= (Select dateadd(month, @Month - 1, dateadd(year, @Year - 1900, 0)))SET @EndDate= (Select dateadd(month, @Month,dateadd(year, @Year - 1900, -1)))select EffectiveCPMRate,DFACampaignId,DFAPlacementId into #CPMfrom dbo.udf_GadgetPlacementEffectiveCPMRateForDateRange(@StartDate, @EndDate)update CTset CT.[Cost] = ISNULL(((ISNULL(CP.[EffectiveCPMRate],0)/1000)*CT.[Imps]),0)from Kraft.MonthlySummaryData CT Inner join #CPM CPon CT.BuyID = CP.DFACampaignId and CT.[PageID] = CP.DFAPlacementId |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-04 : 17:00:21
|
How many rows are there in #CPM? And how many rows match those rows in Kraft.MonthlySummaryData? What does the execution plan show? Is it blocked at times? Have you tried adding an index to #CPM for DFACampaignId,DFAPlacementId?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|