Simple select query running very slow, but select count(*) on same table running very fast

Question

I have a simple select statement that is taking a very long time to run, but running count (*) on the same table with the same WHERE clause comes back in less than a second.

This query runs for a VERY long time (1 hour +):

   SELECT col1
        , col2
        , col3
   FROM Table
   WHERE RowInsertDate >= @SomeStartDate
     AND RowInsertDate <  @SomeEndDate

But this query comes back in less than a second:

   SELECT count(*)
   FROM Table
   WHERE RowInsertDate >= @SomeStartDate
     AND RowInsertDate <  @SomeEndDate

The table has 34 million rows, with an ID column and a geographical region (North, South, East, West) column used for the primary key. The column 'RowInsertDate' is the date the row was inserted into the table. The expected results for the queries above are 'no rows,' and '0' respectively.

The clustered index for this table is (ID, geoRegion) ASC. This table also has a non-clustered index on RowInsertDate ASC.

I'm not sure where to go from here. Has anyone run into this before?

This is one that an execution plan will assist you and us with. Also there is no way for anyone to relate select col1, col2 to the indexes you are noting we have no idea if that is correct or incorrect because we don't know what columns are or are not covered by the index. — Matt, Aug 16 '16 at 16:45
Both queries are different and it will lead to different execution plans.Can you provide execution plan for both — TheGameiswar, Aug 16 '16 at 16:45
What is the actual data type of `RowInsertDate`? Is there an INDEX defined on that field? How many rows are effectively returned? — TT., Aug 16 '16 at 16:45
Can you also enable discard result set option and check :http://stackoverflow.com/questions/6386587/how-to-execute-sql-query-without-displaying-results — TheGameiswar, Aug 16 '16 at 16:48
Hey everybody, Sorry for the delay in responding. It looks like there is some kind of issue with the server itself; our log is full of IO issues. We had the DBAs restart the server and the query runs fast now. I'm not sure what the nature of the exact issue was, but it's now resolved. I'll post another question if this same thing comes up again. — user2192320, Aug 18 '16 at 15:37

score 1 · Answer 1 · answered Aug 16 '16 at 16:52

When you run select count(*) SQL Server can just count rows from your index. When you run select col1,col2,col3 then for every row found in your index SQL Server has to get the clustered index key values (ID, geoRegion, stored in the index) and then search all the rows found from the table with that clustered index key values.

SQL Server could also decide to do a clustered index scan (or something else) if it thinks that doing all the lookups for the clustered index will be more work. You can check what happens in the query plan.

To make your query faster, you might want to consider adding the columns you need to select to your RowInsertDate, either as included columns, or normal columns. This of course only makes sense if the number of columns is relatively small (or there won't be many updates to your table).

score 0 · Answer 2 · answered Aug 16 '16 at 16:57

While you can check the Execution Plan, but I am pretty sure this is a typical BOOKMARK LOOKUP issue. It is resource consuming and time consuming. One solution is to create a COVERING INDEX to deal with it.

Those two queries' execution time is different, because they are doing totally different things.

Of course you should also check what data types the columns(col1, col2, col3) are.

Other than that, you can also SET NOCOUNT ON. Hope it helps.

score 0 · Answer 3 · answered Aug 16 '16 at 17:14

If ID is unique (e.g. identity) then I suggest you make that the PK clustered alone. With the extra column is has to carry extra data around for the lookup.

I and I hope geoRegion is a byte. If varchar then you are carrying 10x the volume you need. That is 1/10 the index in the same amount of memory.

If above does not fix it then add include those columns in the index

CREATE NONCLUSTERED INDEX IX_Table_RowInsertDate  
ON Table (RowInsertDate)  
INCLUDE (col1, col2, col3);

score -2 · Answer 4 · answered Aug 16 '16 at 17:14

-2

Since the query is very simple. I think what you are missing is a nolock:

SELECT col1
    , col2
    , col3
FROM Table (nolock)
WHERE RowInsertDate >= @SomeStartDate
 AND RowInsertDate <  @SomeEndDate

answered Aug 16 '16 at 17:14

Anjani Kumar Agrawal

365
1
14

2

That is a dangerous recomendation – paparazzo Aug 16 '16 at 17:15
@Paparazzi: For a select query nolock is dangerous how? – Anjani Kumar Agrawal Aug 16 '16 at 17:17
1

@AnjaniKumarAgrawal http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – S3S Aug 16 '16 at 18:22
Not only is it a bad idea, you omitted the WITH keyword. Omitting that has been deprecated. I fail to understand why nolock would helpful because the query is simple. And other than a select statement when would you use nolock? – Sean Lange Aug 16 '16 at 18:31
Nolock won't be helpful because the query is simple. Nolock would be helpful because from the query provided and the indexes in place. I don't see any other reason for the issue. Nolock without with does work with older versions of sql but I should have added that in my answer. I will read the article @Paparazzi shared and then would be able to reply as to how dangerous nolock is, which shouldn't be in the current context. – Anjani Kumar Agrawal Aug 16 '16 at 20:01

Simple select query running very slow, but select count(*) on same table running very fast

4 Answers4