Query that only uses index takes long time to execute [closed]

Posted on

Question :

I have a table with ~69,000 rows. I have this very simple query:

-- SET STATISTICS TIME ON
SELECT ID, FirstName, LastName FROM Customers
-- SQL Server Execution Times: CPU time = 31 ms, elapsed time = 901 ms.

I also have this non clustered index:

CREATE NONCLUSTERED INDEX IX1 ON Customers (LastName ASC, FirstName ASC, ID ASC) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON PRIMARY

The execution plan indicates that this index is being used. Yet the query takes ~1 second to return 69k rows which seems odd to me. I am wondering if 1 second is an acceptable amount of time or is it possible to improve this query.

Edit 1:

Microsoft SQL Server 2012 – 11.0.2100.60 (X64)
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

It runs on a dedicated Dell PowerEdge server with 16GB RAM and two SCSI hard drives.

Edit 2:

I get almost similar timings (~1 second) even if I run the query on the server itself using named pipes/shared memory/TCP connections.

Answer :

It may just be taking a while to return to the client. How long does it take if you run as SELECT INTO, eg

SELECT ID, FirstName, LastName 
INTO #tmp 
FROM Customers

Leave a Reply

Your email address will not be published. Required fields are marked *