Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need assitance interpreting Execuation plan Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 2:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:49 AM
Points: 106, Visits: 288
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


  Post Attachments 
Tally query.JPG (14 views, 212.94 KB)
Post #1395326
Posted Tuesday, December 11, 2012 2:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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 ?
Post #1395329
Posted Tuesday, December 11, 2012 2:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 11,963, Visits: 10,990
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1395337
Posted Tuesday, December 11, 2012 6:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:49 AM
Points: 106, Visits: 288
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]



Post #1395381
Posted Thursday, December 13, 2012 2:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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.
Post #1396411
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse