微调SQL查询 [英] Fine tuning SQL query

问题描述

大家好，

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

这是我的连接查询，查询返回大约800行，需要一点时间

即20秒。



如果我单独运行查询

即SELECT * FROM GETFINALCTE CTE和

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

另外，每一行分别给出1200行和144行，并且只需1秒钟来检索行。



但是当我加入这两行时，如我的第一个查询所示，它是需要20秒才能检索行。



有人能告诉我发生这种情况的原因。是连接中的COUNT（）函数是否会产生问题？



只有在连接后，即使两个查询都返回限制行数，导致此时间增加的原因是什么？



任何人都可以指导我进行必要的更改以优化查询并更快地检索数据可能是1秒。



提前致谢!!

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!!

推荐答案

@ andrewCharlz，_Asif，Tomas，deepak，aravinth04



谢谢各位回复！



抱歉，我没有时间实施您的修复。



但我自己修复了，



i把结果集从

@andrewCharlz,_Asif,Tomas,deepak,aravinth04

Thanks guys for your replies!!

Sorry i didnt have the time to implement your fix.

But did a fix of my own,

i put the result set from

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

以上查询临时表和加入临时表，解决了这个问题。



感谢您的回复。

the above query into a temp table and joined it on the temp table, that solved the issue.

Thanks for your reply.

这篇关于微调SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！