SQL Server Performance

Index help

Discussion in 'T-SQL Performance Tuning for Developers' started by daheri, Dec 5, 2007.

  1. daheri New Member

    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!
  2. ranjitjain New Member

    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
  3. daheri New Member

    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!

Share This Page