CODEWITHSUNDEEP
sqlsql-serverindexing
cool breeze
cool breeze

Reputation: 4811

Adding WHERE clause to query slows the query down - even though column is indexed

So the Users TABLE has 40 million rows. The below query runs fast enough (less than 1 second) if I don't have a WHERE clause If I put the WHERE clause in the query, it takes about 10 minutes to return the result set. I added a index on the UserType column but it didn't seem to effect the response time for some reason

  CREATE INDEX idx_users_user_type ON Users (UserType);

The query:

SELECT  TOP 100
        *
FROM Users u
    INNER JOIN Company c ON c.ID = u.CompanyID
    INNER JOIN Location l ON l.ID = u.LocationId
WHERE
    u.UserType = 'manager'

There are only 3 values for UserType:

manager
employee
temp

I don't want to change the column to an INT value if possible because an ETL process imports data into this table that is hard to change now.

Upvotes: 1

Views: 629

Answers (2)

S3S
S3S

Reputation: 25112

First, you should read this article on using TOP without an ORDER BY. The short version is you can't guarantee the results will be the same with each run, unless you have an ORDER BY.

The first query runs fast because SQL Server simply does a TABLE SCAN for the first 100 rows. Easy. Now you add a WHERE clause... SQL Server has to find all the rows where that predicate is true. Sure, you have an index, this can help. SQL Server will (or could) use that index but if it does, it will then have to do a Key Lookup to retrieve the rest of your columns (since you SELECT *). So, it's having to do A LOT more work. It's very likely that the index isn't even being used since SQL Server could decide it is faster to simply do a TABLE SCAN to check that predicate on every row, versus doing the key lookup. This of course means you have to read 40 million rows (check out how large your table is... this needs to come into memory. I bet it's paged to disk,maybe even spilling to TEMPDB).

So, of course it is going to be slower... you are checking a condition against 40 million rows instead of pulling a random TOP 100. If you want further help, we need the execution plans at a minimum, with all DDL (create table, create index) statements.

Other options to explore are:

Upvotes: 6

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

You could add index:

CREATE INDEX idx_users_user_type_company_id_location_id 
          ON Users (CompanyId, LocationId,UserType);

Upvotes: 1

Related Questions