Why does creating non-clustered index slow down data retrieval?

Question

My understanding was that indexing improve data retrieval speed.

Bit confused on this occasion and want to seek some advise. I have a table with millions of records. It takes around 90 seconds to fetch data using the select query and the where clause - example:

SELECT EMPID, FNAME, LNAME, ADDRESS, PHONE
FROM TBLEMPLOYEES
WHERE LNAME='ABBA'

When I create a non clustered index on this column (LNAME), and then run the same select statement, the query takes 300 seconds when executed for the first time. On subsequent runs it takes around 10 seconds. I believe that this improvement upon second execution is due to concept of caching?

When I re-run the same select statement but this time with a different search keyword - example:

SELECT EMPID, FNAME, LNAME, ADDRESS, PHONE
FROM TBLEMPLOYEES
WHERE LNAME='FERNS'

The query again takes around 300 seconds. The next time I execute the same query it takes around 10 seconds.

So is the benefit from indexing guaranteed or are they only applicable upon subsequent execution of the respective queries? Bit confused here as to why only subsequent executions of the query result in quick data retrieval.

Upload your the xml of the actual execution plan to brentozar.com/pastetheplan and add the link to your question. — Dan Guzman
– Dan Guzman, Commented Sep 26, 2018 at 11:10
You might benefit from a covered index (with columns frequently needed in the SELECT). See e.g. Create Indexes with Included Columns. Note that your index will become larger, also row updates may become a bit slower - but querying should become faster because it no longer has to plow through the datapages to gather your SELECT data. — Peter B
– Peter B, Commented Sep 26, 2018 at 11:12
what is the definition of the index you have created? Does it INCLUDE the columns in the select list? — MJH
– MJH, Commented Sep 26, 2018 at 11:12

Mazhar · Accepted Answer · 2018-09-26 11:12:18Z

2

I suspect the index you created is only on LNAME like so

CREATE INDEX IX_LNAME ON TBLEMPLOYEES (LNAME)

If you create an index that includes, or covers, the columns in the SELECT, like so

CREATE INDEX IX_LNAME ON TBLEMPLOYEES (LNAME) INCLUDE (EMPID, FNAME, ADDRESS, PHONE)

answered Sep 26, 2018 at 11:12

Mazhar

3,8431 gold badge14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

variable Over a year ago

Yes, you're right I am indexing only on LNAME (no included columns). So you think covering index will improve the performance. Thanks, I assume a clustered index would also improve the performance. Right?

IVNSTN Over a year ago

Indexes are no magic. Playing with nonclustered and especially clustered indexes may degrade performance drastically. As well as improve one. Stop guessing and take a look at the docs.

Collectives™ on Stack Overflow

Why does creating non-clustered index slow down data retrieval?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related