mardi 23 décembre 2014

use an Index correctly to optimize queries


I have an SQL database with millions of records and when I'm querying the data like



select * from ActCosts where ScenarioID= 456


The tables has 1,323,718 rows and it gives me like 50,000+ rows which is surprisingly taking more than 3 minutes. So I am now thinking of how I could improve this performance. I find one way is to create an index on the column "SomeID". I have created this index but the query takes the same time


enter image description here


Execution plan enter image description here


Index Script



CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost]
(
[ScenarioID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


ALTER TABLE [dbo].[ActCost] ADD CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED
(
[ActCostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO




Aucun commentaire:

Enregistrer un commentaire