Creating index on heavyly used table


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),
     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?



more ▼

asked Aug 24, 2011 at 04:25 AM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Aug 24, 2011 at 04:44 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 24, 2011 at 04:25 AM

Seen: 980 times

Last Updated: Aug 24, 2011 at 04:45 AM

Copyright 2018 Redgate Software. Privacy Policy