November 26, 2015 at 5:25 am
I have two tables, Master and detail. Master Id is referenced in Detail table as (MasterId) and I am using following 2 queries.
1- select M.Id from Master m inner join Detail d on m.Id = d.MasterId where d.MasterId = @RowId
2- select M.Id from Master m inner join Detail d on m.Id = d.MasterId where m.Id = @RowId
Questions:
1 - Master.Id is PrimaryKey and it is clustered Index as well but Detail.MasterId is only referencing to Master Tables Id. Please, let me know if there is any difference in writing both of above queries in terms of performance?
In first query above, If I am creating NON Clustered on Detail.MasterId would it be OK with performance?
2- What if I am not referencing to Detail.MasterId, would it be OK by creating NON Clustered index on Detail.MasterId?
3- Let me know the best practice if there is any I may know?
Shamshad
November 26, 2015 at 5:44 am
Looking at those in isolation, you probably want an index on (MasterID, ID) on the detail table. Check the existing indexes, make sure there isn't an existing one on MasterID that you can add a second column to.
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
November 26, 2015 at 9:57 pm
Gila, your answser is not clear to me. From my understanding, are you saying to check existing Index on Detail.Id (should be PK Clustered) and Detail.MasterId (which is referenced from Master's Id-PK). In this case I need to create a non clustered index on Detail.MasterId. Is this correct?
There are several similar Master Detail cases where we make joins in queries. So, I must check those columns which are joining, they should be indexed.
Also the where clause should also be part of index.
Also if there is isActive (bit) column with millions of rows in a table, should be also index that BIT type column?
What you experts are saying about it?
Shamshad
November 27, 2015 at 1:40 am
No, I said if there is no existing index on the Detail table on MasterID, create one on (MasterID, ID). If there is an existing one on MasterID, add the ID column to it.
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
December 2, 2015 at 12:42 am
Detail.Id column is already clustered PK index, Do you want me to add another index non clustered (Detail.Id, Detail.MasterId)?
CREATE TABLE [dbo].[MasterT](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_MasterT] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DetailT](
[Id] [int] IDENTITY(1,1) NOT NULL,
[MasterId] [int] NOT NULL,
[Amount] [int] NOT NULL,
[Dt] [datetime] NOT NULL,
CONSTRAINT [PK_DetailT] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DetailT] ADD CONSTRAINT [DF_DetailT_Dt] DEFAULT (getdate()) FOR [Dt]
GO
ALTER TABLE [dbo].[DetailT] WITH CHECK ADD CONSTRAINT [FK_DetailT_MasterT] FOREIGN KEY([MasterId])
REFERENCES [dbo].[MasterT] ([Id])
GO
ALTER TABLE [dbo].[DetailT] CHECK CONSTRAINT [FK_DetailT_MasterT]
GO
Let me clear on this. I have several questions related to this I need to know this first.
Regards,
Shamshad
December 2, 2015 at 1:59 am
shamshad.ali (12/2/2015)
Do you want me to add another index non clustered (Detail.Id, Detail.MasterId)?
No.
I said if there is no existing index on the Detail table on MasterID, create one on (MasterID, ID).
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
December 2, 2015 at 9:12 am
In my given scenario, do I need any index or not, back to my first question ? As I asked with sample queries.
December 2, 2015 at 9:16 am
shamshad.ali (12/2/2015)
In my given scenario, do I need any index or not, back to my first question ?
Which I have answered. Twice.
GilaMonster (11/26/2015)
you probably want an index on (MasterID, ID) on the detail table.
if there is no existing index on the Detail table on MasterID, create one on (MasterID, ID). If there is an existing one on MasterID, add the ID column to it.
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
December 2, 2015 at 9:55 pm
OK, There is no existing index on MasterId on [DetailT] Table. Only PK clustered Index on Id of [DetailT] table.
so, here I have created an index:
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20151203-094659] ON [dbo].[DetailT]
(
[MasterId] ASC,
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Is this OK now?
One more question:
i) Should I use the sequence first Detail.MasterId then Detail.Id or first Detail.Id then Detail.MasterId?
ii) Developers should use exactly the same sequence in where clause which is mentioned in index [ColumnA] then [ColumnB]
select from table where columnA = @a and columnB = @b-2
or
select from table where columnB = @b-2 and columnA = @a
Regards,
Shamshad Ali
December 3, 2015 at 1:13 am
shamshad.ali (12/2/2015)
i) Should I use the sequence first Detail.MasterId then Detail.Id or first Detail.Id then Detail.MasterId?
As I specified it. (MasterID, ID)
ii) Developers should use exactly the same sequence in where clause which is mentioned in index [ColumnA] then [ColumnB]
select from table where columnA = @a and columnB = @b-2
or
Doesn't matter. Order of predicates in the where clause is irrelevant
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
December 3, 2015 at 4:55 am
Thanks Gila. Further to this one, what about the column sequence matters then while creating indexes?
a) (MasterId, ID) OR (ID, MasterID)
b) (MasterId asc, ID asc) or combination of this with [asc] or [desc] what is the impact and normally we don't care about it. But is there any benefit in specific scenario in which we can fine tune index by taking care of these parameters?
c) How about the select Master.[col1], Master.[col2], Detail.[Col1], Detail.[Col2], does this sequence have any impact if we put them in [INCLUDE] list, again for my understanding do we need take care of column sequence while writing SELECT <Column sequence as mentioned in index>?
Appreciate your comments!
Shamshad Ali
December 3, 2015 at 7:36 am
shamshad.ali (12/3/2015)
a) (MasterId, ID) OR (ID, MasterID)
Um...
you probably want an index on (MasterID, ID)
create one on (MasterID, ID)
As I specified it. (MasterID, ID)
How many times do I have to say it?
b) (MasterId asc, ID asc) or combination of this with [asc] or [desc] what is the impact and normally we don't care about it. But is there any benefit in specific scenario in which we can fine tune index by taking care of these parameters?
Leave the direction as default unless you have a very good reason to do otherwise.
c) How about the select Master.[col1], Master.[col2], Detail.[Col1], Detail.[Col2], does this sequence have any impact if we put them in [INCLUDE] list, again for my understanding do we need take care of column sequence while writing SELECT <Column sequence as mentioned in index>?
Order doesn't matter in either case
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
December 3, 2015 at 8:08 am
Why not have a non-unique clustered key of MasterID on detail?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply