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.
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.
I get almost similar timings (~1 second) even if I run the query on the server itself using named pipes/shared memory/TCP connections.
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