0

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.

variable
  • 8,262
  • 9
  • 95
  • 215
  • 1
    Can you post execution plans for both cases? – Dean Savović Sep 26 '18 at 10:52
  • Why are you comparing speed of different filters? – Ivan Starostin Sep 26 '18 at 11:00
  • 1
    Upload your the xml of the actual execution plan to https://www.brentozar.com/pastetheplan/ and add the link to your question. – Dan Guzman Sep 26 '18 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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/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 Sep 26 '18 at 11:12
  • what is the definition of the index you have created? Does it INCLUDE the columns in the select list? – MJH Sep 26 '18 at 11:12

1 Answers1

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)
Mazhar
  • 3,797
  • 1
  • 12
  • 29
  • 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? – variable Sep 26 '18 at 11:17
  • 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. – Ivan Starostin Sep 26 '18 at 11:30