Fine tuning SQL query

See more:

SQL

Hi All,

SELECT * FROM GETFINALCTE CTE
    LEFT OUTER JOIN
    (
       SELECT COUNT(city_name) YTD, mvmt.override_payee_id PAYEEID
       --,STP.city_name CITY_NAME
       FROM dbo.movement mvmt
       INNER JOIN dbo.stop stp ON mvmt.id=stp.movement_id
       WHERE STP.city_name ='CHICAGO' -- @origincity
       GROUP BY mvmt.override_payee_id
    ) AS X ON X.PAYEEID = CTE.CARRIERID AND X.PAYEEID IS NOT NULL

This is my join query and the query returns around 800 rows and is taking a bit of time
i.e 20 seconds.

If i run the query individually
i.e SELECT * FROM GETFINALCTE CTE and

SELECT COUNT(city_name) YTD, mvmt.override_payee_id PAYEEID
      --,STP.city_name CITY_NAME
      FROM dbo.movement mvmt
      INNER JOIN dbo.stop stp ON mvmt.id=stp.movement_id
      WHERE STP.city_name ='CHICAGO' -- @origincity
      GROUP BY mvmt.override_payee_id

separately, each one gives 1200 and 144 rows respectively and takes only 1 seconds to retrieve the rows.

But when i join these two as shown in my first query it is taking 20 seconds to retrieve the rows.

Can anyone tell me the reason why this is happening. Is it the COUNT() function in join that is creating the problem?

What is causing this increase in time only after join even though both query returns limit number of rows?

Can anyone guide me on the necessary changes that i could make to optimize the query and retrieve the data faster may be 1 second.

Thanks in advance!!