使用子查询优化存储过程

发布于 2025-01-11 23:37:41 字数 587 浏览 1 评论 0原文

我正在研究使用子查询的存储过程的优化。我需要替换这个子查询以获得更少的执行时间。

代码：

SELECT t1.*,
       detail= case when ( DC_ID in ( select DC_ID 
                                      from TableFinale1 
                                      where frais is not null 
                                      and WDT is not null 
                                      and type <> 'CDFS'
                                      )
                         )
                    then 'OK'
                    Else '' 
                    End       
into TableFinale2
From TableFinale1 t1

原文

I'im working on optimization of stored procedure that uses a subquery.
I need to replace this subquery to get less execution time.

code:

SELECT t1.*,
       detail= case when ( DC_ID in ( select DC_ID 
                                      from TableFinale1 
                                      where frais is not null 
                                      and WDT is not null 
                                      and type <> 'CDFS'
                                      )
                         )
                    then 'OK'
                    Else '' 
                    End       
into TableFinale2
From TableFinale1 t1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

莫言歌 2025-01-18 23:37:41

您可以切换到 JOIN，但是...如果有超过 1 个 DC_ID 具有非空 fraisT1 记录code>、非空 WDT 和 type<>'CDFS。因此，除非您的数据符合模型，否则它并不完全是 1:1。

SELECT t1.*, CASE WHEN t2.DC_ID IS NOT NULL THEN 'OK' END
FROM TableFinale1 t1
   LEFT OUTER JOIN TableFinale1 t2
       ON t1.DC_ID = t2.DC_ID
       AND t2.frais is not null 
       and t2.WDT is not null 
       and t2.type <> 'CDFS'

You could switch to a JOIN, BUT... this will cause duplicate T1 records if there are more than 1 DC_ID that have a non-null frais, a non-null WDT, and a type<>'CDFS. So it's not exactly 1:1 unless your data fits the mold.

SELECT t1.*, CASE WHEN t2.DC_ID IS NOT NULL THEN 'OK' END
FROM TableFinale1 t1
   LEFT OUTER JOIN TableFinale1 t2
       ON t1.DC_ID = t2.DC_ID
       AND t2.frais is not null 
       and t2.WDT is not null 
       and t2.type <> 'CDFS'

回复收藏 0 原文

橘味果▽酱 2025-01-18 23:37:41

如果没有查询计划，很难预测性能变化。如果TableFinale1.DC_ID上没有索引，
也许准备好的过滤和索引临时表会有所帮助。假设 DC_ID bigint：

create table #temp(DC_ID bigint not null primary key)

insert #temp 
    select distinct DC_ID 
    from TableFinale1 
    where 
        frais is not null 
        and WDT is not null 
        and type <> 'CDFS'
        
select t1.*, case when #temp.DC_ID is not null then 'OK' else '' end as [detail]
into TableFinale2
From 
    TableFinale1 t1
    left join #temp on #temp.DC_ID=t1.DC_ID

It is hard to predict performance changes without a query plan. If there are no indexes on TableFinale1.DC_ID,
maybe a prepared filtered and indexed temporary table will help. Assuming DC_ID bigint:

create table #temp(DC_ID bigint not null primary key)

insert #temp 
    select distinct DC_ID 
    from TableFinale1 
    where 
        frais is not null 
        and WDT is not null 
        and type <> 'CDFS'
        
select t1.*, case when #temp.DC_ID is not null then 'OK' else '' end as [detail]
into TableFinale2
From 
    TableFinale1 t1
    left join #temp on #temp.DC_ID=t1.DC_ID

回复收藏 0 原文

~没有更多了~