Post reply

SQL query performance issue.

  • January 12, 2012 at 3:39 pm

    #1432930

    Can you post your table definition (create table, indexes, ???).

    Thanks

  • January 12, 2012 at 3:41 pm

    #1432931

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 12, 2012 at 3:46 pm

    #1432933

    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

    #1432936

    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.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 12, 2012 at 3:56 pm

    #1432938

    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 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply