December 11, 2012 at 2:25 pm
Hi,
I have a query that needs to be optimized. I have inserted a Tally table to untilize its indexes but it does not appear that I have been successful. The database engine is SQL 2000. I am using SSMS 2008. I thought I would be able to save the Execution plan but I cannot figure out how to save it. Since I could not save it, I have attached a screen shot.
Here are some of the details.
OEHdrHst_SQL
Indexed on Inv_Dt and Ord_Type, OrdNo, Inv_No, 250,00 records
OELinHstP_SQL
Indexed on Item_no and Ord_Type, OrdNo, Inv_No, 1,000,000 records
IMInvLoc_SQL
Indexed on Item_No, Loc, 30,000 records
IMItmIdx_SQL
Indexed on Item_No, 6000 records
Any help would be apprecxiated.
Thanks,
pat
December 11, 2012 at 2:38 pm
You seem to have a fairly decent plan, given that you have a range type query(date based). I also see that it was a sub second response for 8 rows returned - what are you looking for ?
December 11, 2012 at 2:57 pm
It is pretty much impossible to provide any assistance from just a query and a screenshot of an execution plan. Take a look at this article from Gail. It explains what would be required to help with a performance issue.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 11, 2012 at 6:29 pm
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]
December 13, 2012 at 2:49 pm
I'm not as much of an expert as some of the other contributors on this forum are, w.r.t. interpreting execution plans, but I would say that your culprit is the SUM(CASE...) piece of the query. A bookmark lookup operator is used when the query needs values from columns in the table that are not covered in an index. For more, please read this :
http://blogs.msdn.com/b/craigfr/archive/2006/06/30/652639.aspx
If there was a way to prestore the year / month as separate columns of the table(or using a derived table.... maybe ?) , it would help by summing / grouping on those. As for the table scan, it looks like that table is very small, and SQL Server will often use a table scan even if there was an index in such cases. The fact that the plan tells you that the cost of that scan is zero relative to the rest of the query should be proof enough that its not the problem.
DDL of all tables / indexes with some sample data would help. If it is possible, data can be BCP'ed out and zipped up and posted here. I dont think a large sample of data is needed - even a few hundred rows would do, I think.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply