0

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.

closed as off-topic by Jon Seigel, RolandoMySQLDBA, Mark Storey-Smith, Marian, Max Vernon Oct 21 '13 at 18:49

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jon Seigel, Mark Storey-Smith, Marian, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    How much time does the following query take? DECLARE @ID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50);SELECT @ID=ID, @FirstName=FirstName, @LastName=LastName FROM Customers this will take the overhead of transferring and processing 69K rows out of the equation. – Martin Smith Oct 20 '13 at 11:22
  • 3
    I don't think you can find an index better than this. The query has to either scan the whole table or this index. Assuming that the table has more columns, the index should be (slightly or heavily) the best. But you haven't provided any info on the workload of the table (or the database or the SQL-Server instance) or the machine characteristics (memory, disks). The problem probably lies elsewhere and not in the index. – ypercubeᵀᴹ Oct 20 '13 at 11:22
  • @MartinSmith: yes, it took ~0.065 seconds. What can be done to improve it? – Salman A Oct 21 '13 at 5:18
  • @ypercube: you are right. I have added hardware details but I am not sure where to look for improvement. – Salman A Oct 21 '13 at 5:28
  • 5
    So the difference between the two timings is largely down to the length of time it takes to transfer the 69,000 rows to the client and for the client to process them. – Martin Smith Oct 21 '13 at 12:01
2

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
  • Yes, that is what it was... Transferring few MBs worth of data from server to client. – Salman A Apr 24 '18 at 17:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.