Index suggestions for temp table joins to real tables

  • I have a proc that has several temp tables the join to my 'real' table.

    For example:

    UPDATE #events SET includeBackPlant = 1 FROM #events e JOIN eventInformation_rob i ON i.logID = e.logID WHERE i.infoName = 'IncludeBackPlant'

    I have a non clustered index on logid & infoname and one on logid on the temp side.

    These joins always seem slow, is there a better/faster way?

  • Please post table definitions, index definitions and the execution plan (saved as a .sqlplan file, zipped and attached)

    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
  • Okay here ya go...

    Here's the table & the indexes..

    CREATE TABLE [dbo].[EventItems_rob](

    [LogId] [int] NOT NULL,

    [Company] [varchar](50) NOT NULL,

    [CompanyId] [int] NOT NULL,

    [Department] [varchar](50) NOT NULL,

    [DepartmentId] [int] NOT NULL,

    [CompanyCRN] [varchar](50) NOT NULL,

    [DepartmentCRN] [varchar](50) NOT NULL,

    [TitleUnit] [varchar](50) NOT NULL,

    [TitleUnitId] [int] NOT NULL,

    [State] [varchar](50) NOT NULL,

    [County] [varchar](100) NOT NULL,

    [OrderNo] [varchar](128) NOT NULL,

    [OrderId] [int] NOT NULL,

    [UserCompany] [varchar](50) NOT NULL,

    [UserCompanyId] [int] NOT NULL,

    [UserName] [varchar](50) NOT NULL,

    [UserId] [int] NOT NULL,

    [Source] [varchar](50) NULL,

    [Event] [varchar](256) NULL,

    [EventTime] [datetime] NOT NULL,

    [EventValue] [varchar](150) NULL,

    [PI2Status] [varchar](256) NULL,

    [PI2LineCount] [varchar](256) NULL,

    [PI2RequestTrigger] [varchar](256) NULL,

    [PI2PrimaryRequestValue] [varchar](256) NULL,

    [Comment] [varchar](4096) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    CREATE NONCLUSTERED INDEX [_dta_index_EventItems_rob_7_699149536__K1_K20_2_3_4_5_6_7_8_10_11] ON [dbo].[EventItems_rob]

    (

    [LogId] ASC,

    [EventTime] ASC

    )

    INCLUDE ( [Company],

    [CompanyId],

    [Department],

    [DepartmentId],

    [CompanyCRN],

    [DepartmentCRN],

    [TitleUnit],

    [State],

    [County]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [_dta_index_EventItems_rob_7_699149536__K13_K21_K20] ON [dbo].[EventItems_rob]

    (

    [OrderId] ASC,

    [EventValue] ASC,

    [EventTime] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [_dta_index_EventItems_rob_7_699149536__K19_K13_K12_K18_1_20_21] ON [dbo].[EventItems_rob]

    (

    [Event] ASC,

    [OrderId] ASC,

    [OrderNo] ASC,

    [Source] ASC

    )

    INCLUDE ( [LogId],

    [EventTime],

    [EventValue]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [CX_Combo_r] ON [dbo].[EventItems_rob]

    (

    [CompanyCRN] ASC,

    [DepartmentCRN] ASC,

    [OrderNo] ASC,

    [OrderId] ASC,

    [EventTime] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [CX_Event] ON [dbo].[EventItems_rob]

    (

    [Event] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [CX_Logid] ON [dbo].[EventItems_rob]

    (

    [LogId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [CX_LogID_Company_Dept_TU_Event] ON [dbo].[EventItems_rob]

    (

    [LogId] ASC,

    [Company] ASC,

    [Department] ASC,

    [TitleUnit] ASC,

    [Event] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [CX_orderno_event_eventtime_eventvalue] ON [dbo].[EventItems_rob]

    (

    [OrderNo] ASC,

    [Event] ASC,

    [EventTime] ASC,

    [EventValue] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [CX_Select] ON [dbo].[EventItems_rob]

    (

    [EventTime] ASC,

    [CompanyCRN] ASC,

    [DepartmentCRN] ASC,

    [PI2Status] ASC,

    [Event] ASC,

    [Source] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [CX_user_eventime_r] ON [dbo].[EventItems_rob]

    (

    [UserId] ASC,

    [EventTime] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [IX_EventTime] ON [dbo].[EventItems_rob]

    (

    [EventTime] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    I attached the execution plan.

  • First, a question, you're moving 1.8 million rows out of the EventInformation_rob table. How many rows total are in the table? If you're moving most of the table, you're going to be living with scans, regardless of the indexes you use.

    You don't have a clustered index on the table. That's the first thing I'd fix. Find the most frequently access path (LogId if that's the most frequently referenced value, something else, I don't know the system well enough, you should be able to spot it) and use that as the clustered index.

    Indexes you don't need. IX_EventTime and CX_Select have the same leading edge (first column). You don't need the IX_EventTime index if you do need and use CX_Select because any query that only needs EventTime can use the CX_Select index. Same with [_dta_index_EventItems_rob_7_699149536__K19_K13_K12_K18_1_20_21 (I'd fix that name, just for clarity) and CX_Event. Same for CX_Logid and CX_LogID_Company_Dept_TU_Event.

    The Index Scan is on IX_LogId_InfoName. I dont' see that in the object definition you provided.

    If you were going to try to improve the performance, and I'm not sure indexing is the issue, #Events could use an index on LogId and IncludeBackPlant. But test that.

    If you can make IX_LogId_InfoName covering (assuming it isn't already) and provide a covering index on #events, you might get a Merge Join instead of the Hash Match, which could improve performance. However, I'm assuming that you're dealing with 50 million rows so that selecting 1.8 million of them results in nice clean index seeks. If you're dealing with 2 million rows then, when selecting 1.8 million, you're unlikely to see any major performance improvements strictly through indexing.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

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