September 21, 2010 at 4:54 pm
I have 4 tables to use that have the following format and row counts
PersExpByQuarter Has 71920 records
PersExpByQuarterID PK
EmployeeID FK
EmployeeHistory Has 9048 records
EmployeeHistory PK
JobCodeID FK
JobGroupID FK
EmployeeID FK
JobGroups Has 14 records
JobCodeID PK
EmployeeGroups Has 9 records
EmployeeGroupID PK
These are all INT data types and the only columns that I am using in my query. I first pull all the data from the PersExpByQuarter table and join it to the EmployeeHistory table through the employeeID. I then join the results to either the EmployeeGroups table or JobGroups table. I then GROUP BY the employeeID. Problem is when I run identical queries one takes a super long time to run, about 2:30 and the other one takes less than a second. What gives? I have all my foriegn keys set up correctly. I can't figure out why these should take any difference in time.
Here are the two queries I am using which are structurally identical.
Query 1 the fast one:
SELECT
EmployeeHistory.EmployeeID
FROM
PersExpByQuarter
JOIN EmployeeHistory
ON PersExpByQuarter.EmployeeID = EmployeeHistory.EmployeeID
JOIN EmployeeGroups
ON EmployeeHistory.EmployeeGroupID = EmployeeGroups.EmployeeGroupID
GROUP BY
EmployeeHistory.EmployeeID
Query 2 the slow one:
SELECT
EmployeeHistory.EmployeeID
FROM
PersExpByQuarter
JOIN EmployeeHistory
ON PersExpByQuarter.EmployeeID = EmployeeHistory.EmployeeID
JOIN JobGroups
ON EmployeeHistory.JobCodeID = JobGroups.JobCodeID
GROUP BY
EmployeeHistory.EmployeeID
Query 2 involves the JOIN to the table JobCodes and it takes wayyy too long to execute. I looked at the execution plan and it says that the actual number of rows returned for the Clustered Index Scan on the PersExpByQuarter table is 650732150! Where as it is only 71920 for the faster query, as should be expected!
Attached below are the slow and fast execution plans. Could someone please take a look at them and help me as I am completely lost, these queries are far too simple to be taking this long.
September 21, 2010 at 8:29 pm
what other fields are in these tables (anything that would create BLOBs)?
Have you checked the indexing and fragmentation levels?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 3:22 am
first of all JOIN sequence
in fast : persexprByquarter table with employeeHistory ( 4502 records) and then EmployeeGroups table
in Slow : employeeHistory with EmployeeGroups (9048 records) then persexprByquarter table.
oprtimizer changed sequence/prefernce in both cases which resultant in all records fetched from "persexprByquarter " table in case of "slow".
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2010 at 7:00 am
Can you post the table structures, including indexes?
It looks like the issue with the "slow" plan is because it is doing a NESTED LOOPS join.
I definitely think indexes can fix this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2010 at 11:43 am
CirquedeSQLeil (9/21/2010)
what other fields are in these tables (anything that would create BLOBs)?Have you checked the indexing and fragmentation levels?
1.) Don't know what BLOBs are, 2.) Don't know how to check index fragmentation, maintenance has it set up so Indexs are rebuilt on a weekly basis, but it does this on our production server and on our clients machine.
September 22, 2010 at 11:57 am
Here are the table structures. Note I had called the JobGroups table the JobCode table in my example. It is really called the JobGroups table.
PersExpByQuarter
USE [WebRad]
GO
/****** Object: Table [WebRad].[PersExpByQuarter] Script Date: 09/22/2010 11:45:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [WebRad].[PersExpByQuarter](
[PersExpByQuarterID] [int] IDENTITY(1,1) NOT NULL,
[CreatedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EditedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Created] [datetime] NULL,
[Edited] [datetime] NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeID] [int] NULL,
[Exposure] [float] NULL,
[Dose] [float] NULL,
[Posted] [bit] NULL,
[RegulatoryLimitID] [int] NULL,
[Postable] [bit] NOT NULL,
[CalcNull] [int] NULL,
[DosComponentID] [int] NULL,
[ExpQuarter] [int] NULL,
[ExpYear] [int] NULL,
CONSTRAINT [PK_PersExpByQuarter] PRIMARY KEY CLUSTERED
(
[PersExpByQuarterID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [WebRad].[PersExpByQuarter] WITH CHECK ADD CONSTRAINT [FK_PersExpByQuarter_DosComponents] FOREIGN KEY([DosComponentID])
REFERENCES [WebRad].[DosComponents] ([DosComponentID])
GO
ALTER TABLE [WebRad].[PersExpByQuarter] CHECK CONSTRAINT [FK_PersExpByQuarter_DosComponents]
GO
ALTER TABLE [WebRad].[PersExpByQuarter] WITH CHECK ADD CONSTRAINT [FK_PersExpByQuarter_EmployeesNew] FOREIGN KEY([EmployeeID])
REFERENCES [WebRad].[EmployeesNew] ([EmployeeID])
GO
ALTER TABLE [WebRad].[PersExpByQuarter] CHECK CONSTRAINT [FK_PersExpByQuarter_EmployeesNew]
EmployeeHistory
USE [WebRad]
GO
/****** Object: Table [WebRad].[EmployeeHistory] Script Date: 09/22/2010 11:54:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [WebRad].[EmployeeHistory](
[EmployeeHistoryID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[CreatedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EditedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Created] [datetime] NULL,
[Edited] [datetime] NULL,
[EmployeeNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeGroupID] [int] NULL,
[EmployerID] [int] NULL,
[HireDate] [datetime] NULL,
[TerminationDate] [datetime] NULL,
[NEWVal] [bit] NOT NULL,
[JobCodeID] [int] NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShiftID] [int] NULL,
[ParticipationCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[currentEntry] [bit] NOT NULL,
[JobTitleID] [int] NULL,
CONSTRAINT [PK__EmployeeHistory__00AA174D] PRIMARY KEY CLUSTERED
(
[EmployeeHistoryID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [WebRad].[EmployeeHistory] WITH CHECK ADD CONSTRAINT [FK__EmployeeH__Emplo__019E3B86] FOREIGN KEY([EmployeeID])
REFERENCES [WebRad].[EmployeesNew] ([EmployeeID])
ON DELETE CASCADE
GO
ALTER TABLE [WebRad].[EmployeeHistory] CHECK CONSTRAINT [FK__EmployeeH__Emplo__019E3B86]
GO
ALTER TABLE [WebRad].[EmployeeHistory] WITH CHECK ADD CONSTRAINT [FK__EmployeeH__Emplo__02925FBF] FOREIGN KEY([EmployeeGroupID])
REFERENCES [WebRad].[EmployeeGroups] ([EmployeeGroupID])
GO
ALTER TABLE [WebRad].[EmployeeHistory] CHECK CONSTRAINT [FK__EmployeeH__Emplo__02925FBF]
GO
ALTER TABLE [WebRad].[EmployeeHistory] WITH CHECK ADD CONSTRAINT [FK__EmployeeH__Emplo__038683F8] FOREIGN KEY([EmployerID])
REFERENCES [WebRad].[Employers] ([EmployerID])
GO
ALTER TABLE [WebRad].[EmployeeHistory] CHECK CONSTRAINT [FK__EmployeeH__Emplo__038683F8]
GO
ALTER TABLE [WebRad].[EmployeeHistory] WITH CHECK ADD CONSTRAINT [FK__EmployeeH__JobCo__0FEC5ADD] FOREIGN KEY([JobCodeID])
REFERENCES [WebRad].[JobGroups] ([JobCodeID])
GO
ALTER TABLE [WebRad].[EmployeeHistory] CHECK CONSTRAINT [FK__EmployeeH__JobCo__0FEC5ADD]
GO
ALTER TABLE [WebRad].[EmployeeHistory] WITH CHECK ADD CONSTRAINT [FK__EmployeeH__Shift__2D7CBDC4] FOREIGN KEY([ShiftID])
REFERENCES [WebRad].[ShiftType] ([ShiftID])
GO
ALTER TABLE [WebRad].[EmployeeHistory] CHECK CONSTRAINT [FK__EmployeeH__Shift__2D7CBDC4]
GO
ALTER TABLE [WebRad].[EmployeeHistory] WITH CHECK ADD CONSTRAINT [FK_EmployeeHistory_JobTitle] FOREIGN KEY([JobTitleID])
REFERENCES [WebRad].[JobTitle] ([JobTitleID])
GO
ALTER TABLE [WebRad].[EmployeeHistory] CHECK CONSTRAINT [FK_EmployeeHistory_JobTitle]
JobGroups
USE [WebRad]
GO
/****** Object: Table [WebRad].[JobGroups] Script Date: 09/22/2010 11:56:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [WebRad].[JobGroups](
[JobCodeID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EditedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Created] [datetime] NULL,
[Edited] [datetime] NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Active] [bit] NULL,
[JobCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK__JobGroups__0EF836A4] PRIMARY KEY CLUSTERED
(
[JobCodeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
EmployeeGroups
USE [WebRad]
GO
/****** Object: Table [WebRad].[EmployeeGroups] Script Date: 09/22/2010 11:56:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [WebRad].[EmployeeGroups](
[EmployeeGroupID] [int] IDENTITY(1,1) NOT NULL,
[CreatedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EditedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Created] [datetime] NULL,
[Edited] [datetime] NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmployeeGroup] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OperationID] [int] NULL,
[OneYearDoseLimit] [float] NULL,
[Active] [bit] NULL,
CONSTRAINT [PK_EmployeeGroups] PRIMARY KEY CLUSTERED
(
[EmployeeGroupID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [WebRad].[EmployeeGroups] WITH CHECK ADD CONSTRAINT [FK_EmployeeGroups_Operations] FOREIGN KEY([OperationID])
REFERENCES [WebRad].[Operations] ([OperationID])
GO
ALTER TABLE [WebRad].[EmployeeGroups] CHECK CONSTRAINT [FK_EmployeeGroups_Operations]
Thanks for the help so far!
September 22, 2010 at 1:12 pm
The first thing I notice is that the only indexes on the tables are the Primary Key's. For the queries you have shown us I'd think you'd want at least an Index on EmployeeHistory.EmployeeID.
The reason is shows so many rows returned for the PersExpByQuarter in the slow query is because there are no indexes so it has to scan the clustered index in each loop. The statistics are accurate as it says 71920 estimated rows, but because it is a NESTED LOOP join it is scanning those rows 9048 time (# of rows on the outer side of the Nested Loop) and 71920 * 9048 = 650732160. An index on PersExpByQuarter.EmployeeID would reduce that to 9048 since it would be an index seek.
Now, I don't know your entire system, so I'm not saying that you should create those indexes, but that you should look at the queries against your system to determine what indexes would work best. For these 2 queries the 2 indexes I suggest would help, but for overall system performance you might need different indexes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2010 at 1:30 pm
I would assume SQL is smart enough to use the FK and PK relations would it not? Or is it mainly because the PK for the employee ID not in any of the tables used in the query?
Also, why would it work on the one querys so fast and not on the other? I can't see them being different at all really... the only thing that is different is the last table joined. If I take out the join to the JobGroups table it runs fine. So that would make me think the JobGroups table is the culpret somehow?
Note: This database does not have a single index defined on it at the moment and has a list of over 50 tables. The most records in any one table is 2 million. I remember reading an article on SqlServerCentral that gave you statistics on which columns could benefit from using an index by running a select statement. Perhaps I should have to look that over and define some indexs. I just dont want to bloat the database because I don't entirely know what I am doing when it comes to indexes.
September 22, 2010 at 1:39 pm
The index would need to be used from the table that is returning the data. It is also beneficial to have indexes on fks especially for the join operations.
Placing an index on the EmployeeHistory.JobCodeID should result in better performance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 1:50 pm
Foreign Keys are not indexes and are used to enforce Referential Integrity not performance. In reality a foreign key hurts performance (minimally) on inserts/updates/deletes. Set Statistics IO ON and insert a row into a table with FK'd defined. SQL Server accesses all the tables to validate the FK.
Indexing is as much an art as it is a science. Foreign Key columns are normally good candidates to be indexed either on their own or as part of a composite index.
I'd recommend reading the last set of TSQL2Day blog posts as the subject was indexing. You can find a list of the posts here. Also check out Gail Shaw's (GilaMonster here on SSC), Kimberly Tripp's[/url], and Kalen Delaney's[/url] blogs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2010 at 10:04 pm
Jack Corbett (9/22/2010)
Foreign Keys are not indexes
but i think, by default it gives non-clustered index.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 23, 2010 at 4:53 am
Bhuvnesh (9/22/2010)
Jack Corbett (9/22/2010)
Foreign Keys are not indexesbut i think, by default it gives non-clustered index.
Nope. A foreign key does NOT create an index of any kind.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2010 at 5:12 am
Jack Corbett (9/23/2010)
Bhuvnesh (9/22/2010)
Jack Corbett (9/22/2010)
Foreign Keys are not indexesbut i think, by default it gives non-clustered index.
Nope. A foreign key does NOT create an index of any kind.
hmm....you are right ...i think i overlooked somewhere....anyways thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 23, 2010 at 8:13 am
Bhuvnesh (9/23/2010)
Jack Corbett (9/23/2010)
Bhuvnesh (9/22/2010)
Jack Corbett (9/22/2010)
Foreign Keys are not indexesbut i think, by default it gives non-clustered index.
Nope. A foreign key does NOT create an index of any kind.
hmm....you are right ...i think i overlooked somewhere....anyways thanks
There are articles and scripts here on SSC that discuss the topic and also help to determine the FKs in need of an index. it would be well worth the time spent to find those.
I also have a script to help find FKs without indexes on my blog
http://jasonbrimhall.info/2010/03/04/index-your-fk/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 23, 2010 at 9:35 am
Ha, well I don't think I need a script as all of my FK are without indexes.
Thanks for all the information from everyone. This has been a very informative post, hence why I enjoy these forums so much.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply