CODEWITHSUNDEEP
sql-server
SqlGeek
SqlGeek

Reputation: 31

Cross Join Performance issue

I have the below query which is performing very slow .

SELECT  A.city_id
            , FIRST_Hotel_By_City.hotel_name        
    FROM    hotel AS A  
            CROSS APPLY
            (
                SELECT  TOP 1 I.*
                FROM    hotel AS I
                WHERE   A.city_id = I.city_id
                ORDER   BY I.created_date ASC , hotel_name ASC
            ) FIRST_Hotel_By_City
    GROUP BY A.city_id
            , FIRST_Hotel_By_City.hotel_name
            , FIRST_Hotel_By_City.created_date
    ORDER BY A.city_id

I have the below non clustered index on City_id

CREATE NONCLUSTERED INDEX ix_cityid
  ON hotel(city_id )
  include (created_date, hotel_name)

Can somebody please help if we can optimize it further. Below are the stats and execution plan for the query.

Stats , ExecutionPlan

Upvotes: 3

Views: 143

Answers (2)

Martin Smith
Martin Smith

Reputation: 453388

For each city_id you are trying to return the hotel_name of the hotel ordered first by created_date ASC, hotel_name ASC.

You can use (Demo)

SELECT city_id,
       MIN(created_date) AS created_date,
       SUBSTRING(MIN(STR(DATEDIFF(DAY,'0001-01-01',created_date)) + hotel_name), 11, 8000)
FROM   hotel
GROUP  BY city_id

which gives a simple execution plan with a single index scan.

enter image description here

Upvotes: 0

PSK
PSK

Reputation: 17943

After looking at your query and the output you are getting, I think you don't need a CROSS APPLY, you can simplify change your query like following for better performance.

   SELECT DISTINCT city_id,hotel_name  FROM
    (
        SELECT A.city_id,
        (
         SELECT TOP 1 hotel_name FROM hotel I  WHERE   A.city_id = I.city_id
         ORDER BY  I.created_date ASC , hotel_name ASC
        ) AS hotel_name
        FROM hotel  A
    ) T

Upvotes: 1

Related Questions