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