sqlGDBA,
I am looking for ways to improve the response time of the query. This query takes about 30 seconds. However, this is only one of several dozen similar queries. The overall time required by all these queries can exceed 30 minutes. I was looking for ways to shorten the response time.
I see there is a table scan and I thought that was a bad thing. I was hoping it would use the indexes on that table. Can I determine why it is not using an index and doing a full table scan instead? And could you please explain what a bookmark lookup is?
Overall, I was hoping to get an interpretation of the actual execution plan that would include explanation of those pieces of the plan that might yield performance gains.
Sean,
Thank you for the link. I have posted similar code with data when I felt it was appropriate. Here though, I felt the quantity of data is so large that I couldn’t post enough to make my sample simulate the actual data I am working with. Also, the ERP table contain many fields not relevant to this query (OEHdrHst_Sql has 110 fields and OELinHst_SQL has 88 fields. Would I need to create a table containing all those fields and then populate those fields with data? ).
If you feel strongly that posting create table/indexes statements and sample data would aid in this instance, please give me a little guidance (number of fields, how many data rows per table, etc) as to what you would like to see, and I will try to get that together.
What I was hoping to get from my post was not a different query but insightful questions to where to look for performance gains. E.g if someone were to question there is Table Scan of JIRTempItemNoTally rather than a search using its indexes, I would post the index structure for review.
In fact to be proactive, here is the create table statement and the Indexes for JIRTempItemNoTally
CREATE TABLE [dbo].[JIRTempItemNoTally](
[ItemNo] [varchar](15) NULL,
[DtCreated] [datetime] NULL,
[Pos1] [char](1) NULL,
[Pos2] [char](1) NULL,
[Pos3] [char](1) NULL,
[Pos4] [char](1) NULL,
[Pos5] [char](1) NULL,
[Pos6] [char](1) NULL,
[Pos7] [char](1) NULL,
[Pos8] [char](1) NULL,
[Pos9] [char](1) NULL,
[Pos10] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [IX_JIRTempItemNoTally] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally] ON [dbo].[JIRTempItemNoTally]
(
[ItemNo] 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
/****** Object: Index [IX_JIRTempItemNoTally_1] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_1] ON [dbo].[JIRTempItemNoTally]
(
[Pos1] ASC,
[Pos2] ASC,
[Pos3] ASC,
[Pos4] ASC,
[Pos5] ASC,
[Pos6] 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
/****** Object: Index [IX_JIRTempItemNoTally_10] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_10] ON [dbo].[JIRTempItemNoTally]
(
[Pos3] 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
/****** Object: Index [IX_JIRTempItemNoTally_2] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_2] ON [dbo].[JIRTempItemNoTally]
(
[Pos2] ASC,
[Pos3] 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
/****** Object: Index [IX_JIRTempItemNoTally_3] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_3] ON [dbo].[JIRTempItemNoTally]
(
[Pos4] ASC,
[Pos5] ASC,
[Pos6] 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
/****** Object: Index [IX_JIRTempItemNoTally_4] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_4] ON [dbo].[JIRTempItemNoTally]
(
[Pos5] ASC,
[Pos6] 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
/****** Object: Index [IX_JIRTempItemNoTally_5] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_5] ON [dbo].[JIRTempItemNoTally]
(
[Pos6] 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
/****** Object: Index [IX_JIRTempItemNoTally_6] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_6] ON [dbo].[JIRTempItemNoTally]
(
[Pos7] ASC,
[Pos8] ASC,
[Pos9] ASC,
[Pos10] 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
/****** Object: Index [IX_JIRTempItemNoTally_7] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_7] ON [dbo].[JIRTempItemNoTally]
(
[Pos8] ASC,
[Pos9] ASC,
[Pos10] 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
/****** Object: Index [IX_JIRTempItemNoTally_8] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_8] ON [dbo].[JIRTempItemNoTally]
(
[Pos9] ASC,
[Pos10] 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
/****** Object: Index [IX_JIRTempItemNoTally_9] Script Date: 12/11/2012 20:24:16 ******/
CREATE NONCLUSTERED INDEX [IX_JIRTempItemNoTally_9] ON [dbo].[JIRTempItemNoTally]
(
[Pos10] 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]