Hi, I am hoping some of you advanced optimizers in here can help me with some indexes. I have one table called "WEB_USER" with the following columns: USER_ID Integer PK, REG_DATE Datetime, SEX tinyint, ACTIVE bit, LOCATION tinyint, BIRTHDATE smalldatetime, SEEKS tinyint I have a second table called "SCORE" with the following columns: USER_ID integer PFK (foreign key from Web_User), Score1 int, score2 int Finally I have a view for calculation of score similarity: select a.user_id as user_id1, b.user_id as user_id2, abs(a.score1-b.score1) as Xscore1, abs(a.score2-b.score2) as Xscore2 from SCORE A CROSS JOIN SCORE B WHERE A.USER_ID <> B.USER_ID Next I have a SP who takes a couple of parameters and then searches for the top 100 combined xscores: SELECT TOP 100 ubf.USER_ID2, xscore1, xscore2 FROM userXrefandScore ubf INNER JOIN web_user u ON ubf.USER_ID2 = u.USER_ID AND u.active = 1 AND u.birthdate BETWEEN DATEADD(YEAR, @toAge*-1, GETDATE())+1 and DATEADD(YEAR, @fromAge*-1, GETDATE()) AND u.sex = @wantedSex AND u.seeks IN(3,@mySex) INNER JOIN dbo.iter_INTlist_to_table('@wantedLocationsString') loc ON u.location = loc.number WHERE ubf.USER_ID1 = @userId ORDER BY XSCORE1+XSCORE2 This works fine, but I need some help with indexing the web_user table to filter out most possible results. By the way, if anyone wonders about the dbo.iter_INTlist_to_table funktion this is a funktion that takes a string of separeted ints and turn them into a small temptable. Any help will be greatly appriciated!
Hi, Web_user has already got PK i.e. index on userid so another non-clustered index on columns active, birthdate,sex, seeks could be useful here. Look at the execution plan with statistics before and after this change
Hi, Thanks for your reply! I just tried this without noticing any special differences. I already have a non-clustered index on columns (birthdate, location, sex, seeks, active, user_id). When adding the new index as you suggested the procedure would still use the old index even after recompile. Does that mean the old one is better? Or does SQL server not always choose the best index? Anyway, this procedure is beginning to get slow when there is a lot of users to calculate. If you spot anything you think could be done smoother please let me know!