none
inner join and Index

    Dotaz

  • hi My Freinds.

    i have problem on Using Index with Inner join Query( on Merge Table) , i have putting Suitable indexs but still bad:

    i want to Fetch Last top 10 Post Only For People In MyFavouriteContact( By Given FavouriteContactID) as follow:

    FavouriteContact(FavouriteContactID,UserId)    

    Users(UserId,Name,dateofBirth,Tel)

    Merge_Users_FavouriteContact(FavouriteContactID,UserId,Merge_ID)

    Post(PostID,PostDate,PostBody,PostDate,UserId)

    ==========================================================================================

    select Top 10 PostID from Post inner join Merge_Users_FavouriteContact

    on Post.UserId=Merge_Users_FavouriteContact.UserId

    where Merge_Users_FavouriteContact.FavouriteContactID=@FavouriteContactID

    this Query Fetching Post from Users , and Only For Users Added in My Contact (Merge_Users_Contact)

    ===========================================================================================

     I have setting non-Clustered Index on:

    1- IX_Merge_Users_FavouriteContact_1(FavouriteContactID)

    2- IX_Merge_Users_FavouriteContact_2(UserId)

    3-IX_Post(UserId)

    all other My Scenario Like this scenario , if its solved all other will be solved.

    thanks again.


    • Upravený greaso 23. února 2012 19:00
    23. února 2012 18:54

Odpovědi

  • You neglected to mention whether or not you have or might have an ORDER BY clause on the query; this is important because the ORDER BY clause can have a huge impact on the performance of this particular query.  Also, please run these two queries and get back to us with the results:

    select count(*)
    from post
    inner join merge_Users_FavouriteContact
      on post.UserId = merge_Users_FavouriteContact.userId
    where merge_Users_FavouriteContact.favouriteContact_ID
        = @favouriteContactId;
    
    select count(distinct favouriteContactId)
    from merge_Users_FavouriteContact;

    • Označen jako odpověď greaso 24. února 2012 20:56
    23. února 2012 19:51
    Moderátor
  • hi thank u about answer, i have tried ur solution but the problem still exists.

    im using  Order By Post.PostID Desc

    i think the problem on Index. any help?
    • Upravený greaso 23. února 2012 20:25
    • Označen jako odpověď greaso 24. února 2012 20:55
    23. února 2012 20:23
  • hi thank u about answer, i have tried ur solution but the problem still exists.

    im using  Order By Post.PostID Desc

    i think the problem on Index. any help?

    Interesting; I wasn't offering my request for diagnostic information as a solution to your problem.  What I am interested in are the two cardinalities that might be returned from the two queries that I provided.  If you could run those two queries and provide the results, that might be helpful.
    • Označen jako odpověď greaso 24. února 2012 20:55
    23. února 2012 20:51
    Moderátor

Všechny reakce

  • You neglected to mention whether or not you have or might have an ORDER BY clause on the query; this is important because the ORDER BY clause can have a huge impact on the performance of this particular query.  Also, please run these two queries and get back to us with the results:

    select count(*)
    from post
    inner join merge_Users_FavouriteContact
      on post.UserId = merge_Users_FavouriteContact.userId
    where merge_Users_FavouriteContact.favouriteContact_ID
        = @favouriteContactId;
    
    select count(distinct favouriteContactId)
    from merge_Users_FavouriteContact;

    • Označen jako odpověď greaso 24. února 2012 20:56
    23. února 2012 19:51
    Moderátor
  • hi thank u about answer, i have tried ur solution but the problem still exists.

    im using  Order By Post.PostID Desc

    i think the problem on Index. any help?
    • Upravený greaso 23. února 2012 20:25
    • Označen jako odpověď greaso 24. února 2012 20:55
    23. února 2012 20:23
  • hi thank u about answer, i have tried ur solution but the problem still exists.

    im using  Order By Post.PostID Desc

    i think the problem on Index. any help?

    Interesting; I wasn't offering my request for diagnostic information as a solution to your problem.  What I am interested in are the two cardinalities that might be returned from the two queries that I provided.  If you could run those two queries and provide the results, that might be helpful.
    • Označen jako odpověď greaso 24. února 2012 20:55
    23. února 2012 20:51
    Moderátor