January 12, 2012 at 3:16 pm
Hi,
I have a table where we will store the details of when a call made to customer. it has a column which needs to be updated with the time difference between recent call date and the last call made to the customer. This table has 5 million records and every day 10k new records will be inserted. after inserting 10k records i need to update the time differences, for which I use the below update query.
UPDATE dbo.[FactCalls]
SET TIME_DIFFERENCE = MAINSUB.TIME_DIFFERENCE
FROM dbo.[FactCalls] SRCMAIN INNER JOIN
(
SELECT Main.RowId, CASE WHEN Main.LAST_CONTACT IS NULL THEN 0
ELSE DATEDIFF(mi,Main.LAST_CONTACT,Main.createddateTS) END AS TIME_DIFFERENCE
FROM
(
SELECT RowId, DimCustomerKey, createddateTS,
(
SELECT MAX(createddateTS) FROM
dbo.[FactCalls] SRCa
Where SRCa.DimCustomerKey = SRC.DimCustomerKey AND
SRCa.createddateTS < SRC.createddateTS AND
SRCa.Type=SRC.Type
GROUP BY DimCustomerKey
) AS LAST_CONTACT
FROM dbo.[FactCalls] SRC
WHERE createddateTS >= CAST(LEFT(GETDATE()-1,11) AS DATETIME)
) Main
) MainSub ON MainSub.RowId = SRCMAIN.RowId
But this query is running for hours. Is there any other efficient way to do this update? Please help.
Thanks in advance.
January 12, 2012 at 3:39 pm
Can you post your table definition (create table, indexes, ???).
Thanks
January 12, 2012 at 3:41 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
January 12, 2012 at 3:46 pm
below is my table definition
CREATE TABLE [dbo].[Factcalls]
(
[RowId] [varchar](15) NOT NULL,
[DimCustomerKey] [bigint] NOT NULL,
[CreatedDateKey] [bigint] NOT NULL,
[DimManpowerKey] [bigint] NOT NULL,
[ActivityKey] [bigint] NOT NULL,
[Time_Difference] [int] NOT NULL,
[Type] [int] NOT NULL,
[CreatedDateTS] [datetime2](7) NOT NULL,
[SourceExtractedTS] [datetime2](7) NOT NULL,
[BatchInstanceId] [uniqueidentifier] NOT NULL
)
CREATE NONCLUSTERED INDEX [IX_FactCalls_Customerkey] ON [dbo].[FactCalls]
(
[DimCustomerKey] ASC
)
January 12, 2012 at 3:50 pm
I'm not sure if this would return the same result as your original query (or even run at all in the first place) since I have nothing to test against and the nesting level of the original query might have sent me on the wrong track, but here's my approach:
UPDATE
dbo.[FactCalls]
SET
TIME_DIFFERENCE = ISNULL(DATEDIFF(mi, MAINSUB.LAST_CONTACT, SRCMAIN.createddateTS) ,0)
FROM
dbo.[FactCalls] SRCMAIN
CROSS APPLY
(
SELECT TOP 1 createddateTS
FROM dbo.[FactCalls] SRCa
WHERE
SRCa.DimCustomerKey = SRCMAIN.DimCustomerKey
AND SRCa.createddateTS < SRCMAIN.createddateTS
AND SRCa.Type = SRCMAIN.Type
SRCa.createddateTS >= CAST(LEFT(GETDATE() - 1, 11) AS DATETIME)
ORDER BY createddateTS DESC
)MAINSUB
This is the first step to improve the query: to simplify it. The next steps (e.g. index optimization) can only be done based on the data already asked for.
January 12, 2012 at 3:56 pm
Indexation has to be improved :
- No primary key on your table
- An index has to be added on createdDateTS + [DimCustomerKey] + Type (maybe it is your primary key as well).
Can you post your execution plan as requested by Gail.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply