Query can not find indexes | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query can not find indexes

Why will this query not find any indexs? select
(select Points from scoring s1 WHERE s1.userID=s.userid AND s1.weekNum=1),
s.rank
From scoring s
where s.groupid=3392 and s.weeknum=8 and rank>0 and rank<=40 order by rank I want the rank column of the returned rows to be from week 8 and the points column to be from week 1.
This query is not fining any indexes! Is there a better way to do this?
What indexes are on your scoring table and how do you know the query is finding them? Have you looked at the excecution plan? What were the results? DOes the index tuning wizard provide any assistance? Be more specific and maybe there is something someone can spot to help.
I have individual indexes on userid, groupid, and weeknum. I have multi-column, non-clustered indexes on (groupid and weeknum) and (groupid, weeknum and rank) A table scan is occurring. If I modify the query to look like this, the table scans go away and get replaced by index seek: select
(select Points from scoring s1 WHERE sl.groupid=3392 and s1.userID=s.userid AND s1.weekNum=1),
s.rank
From scoring s
where s.groupid=3392 and s.weeknum=8 and rank>0 and rank<=40 order by rank Adding s1.groupid=3392 basically minimizes the search result set of the sub-query, but I thought that the s1.userID=s.userid would be sufficient for that.
Any ideas about this?
How many rows?. Some time SQL execution plan prefer to use scan instead index.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
The scoring table has hundreds of thousands of rows but the groupid limits that amount and rank limits the nubmer of return rows to 40. There are 21 rows per userid.
to make this query hum you’d need an index on
scoring( userid, weeknum, points ) this will cover the subselect. You currently don’t really have any useful indexes for the subselect… the index on userid I’m guessing is not selective enough, so that coupled with having to do a bookmark lookup for each row, SQL has figured that it is more efficient to just scan the table Cheers
Twan
]]>