January 20, 2009 at 2:27 pm
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?
January 20, 2009 at 2:34 pm
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
January 21, 2009 at 7:29 am
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.
January 21, 2009 at 8:11 am
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