SQL Server execute too slow for multiple select

SELECT 
   CountryCode, CountryName, CountryEName, FirstShow, iseTicketFirstShow 
FROM 
   Tristar.dbo.COMCountry 
WHERE 
  ContinentName = 'Continent Name' AND iseTicket = 1 
  and CountryCode in 
    (select Distinct CountryCode 
     from eTicketSubAirport 
     where AirportCode in 
         (select DISTINCT eTDestinationAirport 
          from eTicketMain 
          where eTChecked=1 and eTDepartAirport='TPE')
    ) 
ORDER BY 
   iseTicketFirstShow DESC, FirstShow,CountryEName

The above code took 3 seconds….which is unaccepted. The 2 inner select execute very fast by themselves.

And if I took away one inner select, such as ….

  • How to get data from XML with XMLNAMESPACES
  • SSRS: Report label position dynamic
  • Do Instances Share Same Services? - SQL Server
  • Issue in edition upgrade of SQL Server
  • SQL - help on “top 1 by group”
  • How can I manipulate SQL Server CE 4.0 Database?
  • SELECT 
       CountryCode, CountryName, CountryEName, FirstShow, iseTicketFirstShow 
    FROM 
       Tristar.dbo.COMCountry 
    WHERE 
       ContinentName = 'continent name' AND iseTicket = 1 
       and CountryCode in 
         (select Distinct CountryCode 
          from eTicketSubAirport) 
    ORDER BY 
       iseTicketFirstShow DESC, FirstShow,CountryEName
    

    This executes very fast too.

    Hash Match too 79% of processing. [eTicketSubAirport]

    I cannot take out any part of the select as they are all necessary….

    One Solution collect form web for “SQL Server execute too slow for multiple select”

    Try with a join, it would looke like something like this:

    SELECT DISTINCT
       Country.CountryCode, 
       Country.CountryName, 
       Country.CountryEName, 
       Country.FirstShow, 
       Country.iseTicketFirstShow 
    FROM 
       Tristar.dbo.COMCountry AS Country
       INNER JOIN eTicketSubAirport ON Country.CountryCode = eTicketSubAirport.CountryCode
       INNER JOIN eTicketMain       ON eTicketSubAirport.AirportCode = eTicketMain.eTDestinationAirport
    WHERE 
      Country.ContinentName = 'Continent Name' 
      AND Country.iseTicket = 1 
      AND eTicketMain.eTChecked = 1 
      AND eTicketMain.eTDepartAirport = 'TPE'
    ORDER BY 
       Country.iseTicketFirstShow DESC, 
       Country.FirstShow,
       Country.CountryEName
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.