TSQL does not use indexes
This is a test case created with temporary tables to illustrate the problem. The @userdata table view has an index on the userid and the @users table has an index on id
Why can't the first select use the index, I assumed it would perform better in 1 subquery than in 2 subqueries?
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' data', 2)
INSERT @userdata VALUES('testuser2' data', 3)
INSERT @userdata VALUES('testuser2' 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)
source to share
I think it or
might cause some problems for the query parser to come up with a good query plan. This is not an answer to your question, but an alternative way to make this request. Apart from the index you already have, I would suggest an index at @ 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
source to share
SQL Server will not always generate an optimal plan when using local variables (username in your case).
See the following link for an example where SQL Server does not use an index because a local variable is used: http://www.sqlbadpractices.com/using-local-variables-in-t-sql-queries/ .
source to share
Maybe the problem is that the SQL wont problem is using indexes, not something it can't. There are many reasons for this.
You can try forcing it to use the index . You may find that the request is slower.
You can try ALTER INDEX ixFoo REBUILD
rebuilding the index. The index may not be used because it is overly fragmented.
You can also try UPDATE STATISTICS
.
source to share