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
Execution plan
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