August 29, 2008 at 3:55 am
Hello,
I'm a bit stuck here cause i find an answer in any book witch tells me i guess thats a bad practice to create an index also on a foreign key
Question is:
We develop an application with "windev" witch is fine as such but the application engine seems to have a problem when we create a foreign key relation between 2 tables
The development engine of windev only sees the actual relation also when we create an index on the foreign key
Code:
CREATE TABLE [dbo].[DVK](
[DVK_ID] [int] NOT NULL,
[HVK_ID] [int] NOT NULL,
[DVK_REMOVED] [bit] NOT NULL CONSTRAINT [DF_DVK_DVK_REMOVED] DEFAULT ((0)),
CONSTRAINT [PK_DVK] PRIMARY KEY CLUSTERED
(
[DVK_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DVK] WITH CHECK ADD CONSTRAINT [FK_DVK_HVK_OP_HVK_ID_MET_CD] FOREIGN KEY([HVK_ID])
REFERENCES [dbo].[HVK] ([HVK_ID])
ON DELETE CASCADE
And then we have to create the following statement:
CREATE NONCLUSTERED INDEX [IX_DVK-on-HVK_ID] ON [dbo].[DVK]
(
[HVK_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
My question now is: from the point of view from sqlserver2005 : is this a bad idea or a good idea
with a total of +200 tables this surely would ment that i have a lot of extra indexes in my database,
Tnx for any advice.
Eddy
August 29, 2008 at 8:25 am
It's a standard, recommended practice to have indexes on foreign keys. It's almost always a good idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 29, 2008 at 8:27 am
It's usually a good idea to create indexes on foreign key columns, however you should check and make sure that SQL is actually using them. You can do that by running the queries and checking the execution plans.
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
August 29, 2008 at 1:37 pm
Tnx for the replys,
Really appreciate hints from people who know what there doing.,
So i can safely create those extra indexes and check each one of them by running a query ( set showplan on)
Just a little question though:
for a table (logging table : witch can insert thousands and thousands of records each day, but is rarely consulted (only to check who has done what at witch time when problems arise.)
Would you also propose to create indexes here (consulting ( again:rarely) would search on userid (int), date(datetime) screen(varchar50))
Wkr
Eddy
August 29, 2008 at 1:59 pm
Generally, on a logging table, I don't create any indexes, constraints, defaults, etc. The whole idea is to make inserts into it as fast and efficient as possible. A clustered index on a log ID or inserted datetime can be good, but nothing more than that.
If you need to query it, set up a process for a nightly/weekly/whatever dump from the log table to a log archive table. Put indexes, constraints, etc., on the archive, so you can query that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 29, 2008 at 2:26 pm
eddy (8/29/2008)
So i can safely create those extra indexes and check each one of them by running a query ( set showplan on)
Not just any query, the queries that your application will be using against those tables.
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
August 29, 2008 at 2:38 pm
GSquared,
Thats what i have proposed also,
I have set up a night job that will search for all records older then 7 day's and move them to an archive table,
That way the actual logging table remains relatively small and we can query it without indexes, but was just to get sure.
still have to create the indexes on the archive table (not yet queried it, and possibly will never need to neither, most errors need checking will rise surface in 1-2 days.
Gilamonster: that was my plan, but have impressed myself wrong,,still tnx for the remark though
wkr,
Eddy
August 30, 2008 at 2:34 am
one should provide index for FKs by default.
Except when it hurts your databases performance.
Keep in mind the in SQLServer 2005 you can disable an index and only build it if you actualy need it. ( one would put it disabled for documentation reasons or just to have them at hand without having to figure out what index the monthly job benefits )
Also keep in mind, most databases start being used by a small - usualy atypical - application set. After your projects pilot is over and the second wave is rolled out, you may see your indexes getting used or other indexes may be needed.
You can always generate the create statements using this script :
http://www.sqlservercentral.com/scripts/Indexing/61391/ 😉
If your logging table still cannot handle the load (without the fk-indexes) you may even want to drop the DRI for that log-table if you are sure of its feeds and only have the fks declared for your archiving table.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply