CODEWITHSUNDEEP
sqlperformance
Zoinky
Zoinky

Reputation: 5019

Inner join with distinct slow

I know my query below is just horrible and it takes 2 min to get 10 records (listing table has over 1M records though) but I am not sure whats the better way to write this

I simply just wanna get all the countries that have listings the table that connects countries to listings is province..

 ALTER VIEW [dbo].[CountriesWithListings]
 AS
 SELECT     distinct    
    cn.CountryID,
    cn.Code as CountryCode,
    cn.Name as CountryName
FROM         dbo.Countries AS cn

              INNER JOIN dbo.Provinces AS p ON p.CountryID = cn.CountryID
              INNER JOIN dbo.Cities c on c.ProvinceID = p.ProvinceID
              INNER JOIN dbo.Listings AS l ON l.CityID = c.CityID

WHERE l.IsActive = 1 AND l.IsApproved = 1

Upvotes: 0

Views: 1195

Answers (4)

Taras Velykyy
Taras Velykyy

Reputation: 1801

Without adding indexing or other performance tuning this query should run faster:

ALTER VIEW [dbo].[CountriesWithListings]
AS
    SELECT cn.CountryID, cn.Code as CountryCode, cn.Name as CountryName
    FROM dbo.Countries AS cn
    WHERE cn.CountryID IN
    (
        SELECT p.CountryId FROM dbo.Provinces
        INNER JOIN dbo.Cities c on c.ProvinceID = p.ProvinceID
        INNER JOIN dbo.Listings AS l ON l.CityID = c.CityID
        WHERE l.IsActive = 1 AND l.IsApproved = 1
    )

Upvotes: 0

SoulTrain
SoulTrain

Reputation: 1904

The performance of the query also depends on the indexes that exist in you environment and the column you are using.

Try this...

SELECT cn.CountryID
    ,MIN(cn.Code) AS CountryCode
    ,MIN(cn.NAME) AS CountryName
FROM dbo.Countries AS cn
INNER JOIN dbo.Provinces AS p ON p.CountryID = cn.CountryID
INNER JOIN dbo.Cities c ON c.ProvinceID = p.ProvinceID
INNER JOIN dbo.Listings AS l ON l.CityID = c.CityID
WHERE l.IsActive = 1
    AND l.IsApproved = 1
GROUP BY cn.CountryID

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Assuming you have the appropriate indices in place, using distinct is expensive. You should be able to get better performance using exists:

SELECT     
    cn.CountryID,
    cn.Code as CountryCode,
    cn.Name as CountryName
FROM dbo.Countries AS cn
WHERE EXISTS (
    SELECT 1
    FROM dbo.Provinces AS p
              INNER JOIN dbo.Cities c on c.ProvinceID = p.ProvinceID
              INNER JOIN dbo.Listings AS l ON l.CityID = c.CityID
    WHERE p.CountryID = cn.CountryID
        AND l.IsActive = 1 
        AND l.IsApproved = 1
)

Upvotes: 2

Simon Mbatia
Simon Mbatia

Reputation: 301

Is the listing table indexed? If it has like 1M entries, it would be good to index it first and check the performance after that. Your query is not that complex

Upvotes: 0

Related Questions