SQL Server Performance Forum – Threads Archive

join issue

i have two tables:
Listing
Listing_Status I have a search sp that has a parameter: @ListingStatusID. Due to 99% of the records in Listing table having ListingStatusID = 1, when the sp joins the above two tables on Listing.ListingStatusD and Listing_Status.ListingStatusID, the query optimizer does a clustered index scan on the Listing table and returns all the records in the Listing table to do the join. How can I avoid this situation?

---

Can you post the code, table structure and indices? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

---

part of the sp: FROM
dbo.Listing L with (nolock)
INNER JOIN #List with (nolock) ON L.ListingID = #List.ListingID
INNER JOIN dbo.Property P with (nolock) ON P.PropertyID=L.ListingID
INNER JOIN Listing_Status LST on L.listingStatusID = LST.listingStatusID and LST.Cultureid = @CultureID Listing table just has a CI on ListingID.

---

sorry,
the CI is ListingID, ListingStatusID no NCI’s on the Listing table.

---

I assume the filtering on @ListingStatusID is in the WHERE clause. What does it look like?

---

]]>