asked
Creating index on heavyly used table
Hi, There are two tables, in the database that are heavily used, when the queries were running slowily, the first thing that came to mind was the, index. the structure of the two tables is bit similar, like having a master and detail relationship. Like create dbo.Parent (parentid int identity not null primary key, daterecieved date, transactiondate date) create dbo.table1 (id int not null identity primary key, parentID int, attribute1 varchar(50), attribute 2 varchar(50), etc) Alter table table1 add constraint fk_table1_parent foriegn key [parentID] references [dbo].[parent] (parentid) It shows that the reads are happening very frequently on to the table1 and are based off of the key parentid and every other second reading 7k records. Can this key parentid in the table1, be a right candidate to become a non clustered index, to improve preformance? Also if, the database calls are made based on the date, would a date be a right candidate for being an index? Thanks Thanks,
If the ParentID is created as you've defined it, then yes, it's possibly a very good candidate for a nonclustered index. I'd also be sure that it has a foreign key constraint becuase they can help performance too. Without seeing the queries and the query plans, it's very difficult to say precisely what will improve your performance. If your queries are searching on one of the date values, then yes, it's likely that they could help performance, but, again, that's speculation without knowing enough about what's going on. With the non-clustered indexes, keep an eye out for key lookup operations in the execution plan. They could performance too.
