SQL Server 如何提高SQL查询的性能？[closed]

pn9klfpd 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(177)

Closed. This question needs debugging details . It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem . This will help others answer the question.
Closed 2 days ago.
Improve this question
I have this SQL Server query, and I am using another select inside the my main select. Without this 2 sub selects my query return in 2 seconds. But when I add sub selects inside the main select query then query return in 10 seconds.
How can I increase the query's performance?

select 
    cus.name,
    cus.middle,
    cus.last_name,
    cus.age,
    cus.phone_number,
    (select top 1 first_status 
     from tbl_status as sts
     where sts.id = cus.status_id 
       and st.operation_type in (1, 2, 3)) as first_status,
    (select top 1 second_status 
     from tbl_status as status 
     where sts.id = cus.status_id 
       and st.operation_type in (4, 5, 6)) as second_status,
    st.explanation
from
    tbl_customer as cus 
join
    tbl_status as st on cus.status_id = st.id 
                     and st.operation_type = 10

sql-server

来源：https://stackoverflow.com/questions/74721535/how-can-i-increase-sql-performance-for-my-select-query

2条答案

按热度按时间

b1payxdu1#

我确实没有看到查询中的数据结构。请发布示例数据和预期结果以获得更精确的解决方案。first_status和second_status没有意义。下面是一个伪代码示例，说明如何以基于集合的操作获取第一个和第二个状态列。

with statusPrelim as (
    SELECT customer_id, status_id, status_name, status_desc
      --This probably needs a better PARTION BY but I can't show how to do it without sample data.
      , ROW_NUMBER() OVER(PATITION BY customer_id ORDER BY status_id) as row_num
    FROM tbl_status
)
select 
    cus.name,
    cus.middle,
    cus.last_name,
    cus.age,
    cus.phone_number,
    st1.status_name as first_status,
    st2.status_name as second_status,
    st.explanation
from
    tbl_customer as cus 
join
    tbl_status as st 
        on cus.status_id = st.id 
        and st.operation_type = 10
left join 
    statusPrelim as st1
      ON st1.customer_id = cus.status_id
      AND st1.row_num = 1
      AND st.operation_type in (1,2,3)
left join 
    statusPrelim as st2
      ON st1.customer_id = cus.status_id
      AND st1.row_num = 1
      AND st.operation_type in (4,5,6)

赞(0）回复(0）举报 2022-12-10

wqnecbli2#

如果内嵌查询中“operation_type”的别名不是您想要的，也许将它们分别更改为“sts.operation_type”和“status.operation_type”会有所帮助。
如果这是有意的，利用CTE可能会提高性能。

赞(0）回复(0）举报 2022-12-10