TSQL not using indexes

Question

This is a test senario, made with temporary tables to illustrate the problem. Pretend table @userdata has index on userid and table @users has index on id

Why is the first select unable to use index, I assumed it would perform better in 1 subselect than in 2 subselects?

Version - Microsoft SQL Server 2008 R2 (RTM) Compatibility level - SQL Server 2000.

-- test tables
DECLARE @userdata TABLE(info VARCHAR(50), userid INT)
DECLARE @users    TABLE(id INT, username VARCHAR(20), superuser BIT)

-- test data
INSERT @users    VALUES(1, 'superuser', 1)
INSERT @users    VALUES(2, 'testuser1', 0)
INSERT @users    VALUES(3, 'testuser2', 0)
INSERT @userdata VALUES('secret information', 1)
INSERT @userdata VALUES('testuser1''s data', 2)
INSERT @userdata VALUES('testuser2''s data', 3)
INSERT @userdata VALUES('testuser2''s data',3)

DECLARE @username VARCHAR(50)
SET @username = 'superuser'
--SET @username = 'testuser1'


--The superuser can read all data
--The testusers can only read own data

-- This sql can't use indexes and is very slow
SELECT *
FROM @userdata d
WHERE EXISTS 
(SELECT 1 FROM @users u
WHERE u.username = @username AND u.superuser = 1 OR 
u.id = d.userid AND u.username = @username)

-- This sql uses indexes and performs well
SELECT *
FROM @userdata d
WHERE EXISTS 
(SELECT 1 FROM @users u
WHERE u.username = @username AND u.superuser = 1)
OR EXISTS (SELECT 1 FROM @users u
WHERE u.ID = d.userid 
AND u.username = @username)

How many rows are in the tables (and rows per user), and how does `SELECT * FROM @userdata d WHERE EXISTS (SELECT * FROM @users u WHERE u.username = @username AND (u.superuser = 1 OR u.id = d.userid))` perform relative to the other two queries? — Damien_The_Unbeliever, Jul 12 '11 at 13:23
The select in your first WHERE EXISTS looks off. Shouldn't there be some extra parens in there, maybe: WHERE (u.username = @username AND u.superuser = 1) OR (u.id = d.userid AND u.username = @username)? — rsbarro, Jul 12 '11 at 13:24
@rsbarro - `AND` has higher precedence than `OR` - the parenthetical you've shown is how the server interprets it anyway. — Damien_The_Unbeliever, Jul 12 '11 at 13:25
@Damien_The_Unbeliever OK, my mistake. Thanks. Still think those parens make it easier to see what's going on though... =] — rsbarro, Jul 12 '11 at 13:27
I have around 40 k users and around 1 million userdata. Putting in more parenteses makes no difference (I tried). As Damien mentioned AND takes percendence. — t-clausen.dk, Jul 12 '11 at 13:29

score 1 · Accepted Answer · answered Jul 12 '11 at 13:38

1

I think that or can cause some trouble for the query analyzer to come up with a good query plan. This is not really an answer to your question but an alternative way of doing this query. Apart from the index you already have I would suggest an index on @users.username.

if exists(select * from @users where username = @username and superuser = 1)
begin
  select *
  from @userdata
end
else
begin
  select d.*
  from @userdata as d
    inner join @users as u
      on d.userid = u.id
  where u.username = @username
end

answered Jul 12 '11 at 13:38

Mikael Eriksson

136,425
22
210
281

the last sql takes less than 1 second, first sql takes around 5 seconds. So adding index to username isnt nessasary, at least not yet. The company that sold us the program/database will revoke all support if I make the slightest change. – t-clausen.dk Jul 12 '11 at 13:49

score 1 · Answer 2 · answered Jul 15 '11 at 15:32

SQL Server won't always produce the optimal plan when you use local variables (@username in your case).

See the following link for an example where SQL Server do not use an index because a local variable is used: http://www.sqlbadpractices.com/using-local-variables-in-t-sql-queries/ .

score 0 · Answer 3 · answered Jul 12 '11 at 13:26

0

Its possible that the problem is SQL wont use the indexes, not that it can't. Their are a variety of reasons for this.

You can try to force it to use an index. You might find the query is slower that way.

You can try ALTER INDEX ixFoo REBUILD to rebuild the index. The index might not be used since it is excessively fragmented.

You can also try UPDATE STATISTICS.

answered Jul 12 '11 at 13:26

Justin Dearing

14,270
22
88
161

I don't think the index is the issue. As i mentioned, when i split up the "exists" the sql uses the indexes just fine. – t-clausen.dk Jul 12 '11 at 13:35

TSQL not using indexes

3 Answers3

Linked