CODEWITHSUNDEEP
sql-serverindexingdatabase-performance
embarus
embarus

Reputation: 815

SqlServer 2008 Need suggestion for optimize my query and which filed to index field in table

I have this query

select count(distinct user1_.Id) as col_0_0_ from [FriendCloseness] friendclos0_ inner join [User] user1_ on friendclos0_.User_id=user1_.Id where dateadd(d, 0, datediff(d, 0, friendclos0_.LastUpdate))=@p0 and friendclos0_.IsInvited=1 and user1_.IsDeleted=0 and user1_.Sex=@p1

table User join with FriendCloseness query with FriendClossness field LastUpdate, IsInvited, User field IsDeleted, Sex where

table User structure involving with query

User Id,(Primary key) IsDeleted, Sex,

FriendClossness Id,(Primary key, will DBMS set index to primary key automatically ?) LastUpdate (datetimeoffset), IsInvited, User_id (foreign key)

I wounder which field do I need to index and I want to remove date function by creating new field "InviteDate" to store only date

Upvotes: 0

Views: 81

Answers (1)

Devart
Devart

Reputation: 122032

Try this one -

SELECT col_0_0_ = COUNT(DISTINCT u.Id)
FROM (
    SELECT u.Id
    FROM dbo.[User] u
    WHERE u.IsDeleted = 0 
        AND u.Sex = @p1
    --GROUP BY u.Id
) u
JOIN (
    SELECT 
          f.[user_id]
        , LastUpdate = DATEADD(DAY, 0, DATEDIFF(DAY, 0, f.LastUpdate))
    FROM dbo.[FriendCloseness] f
    WHERE f.IsInvited = 1 
) f ON f.[user_id] = u.Id
WHERE f.LastUpdate = @p0

Upvotes: 1

Related Questions