调整子查询 [英] Tuning subquery
问题描述
亲爱的所有人，
由于下面的查询中突出显示了更多的子查询，因此查询速度非常慢。请重写这些查询以快速查询。
Due to more number of sub-queries highlighted below in below query,the query is very slow.Help to rewrite those queries to make query fast.
SELECT
                    b.bid AS出价，
                    b.book_id AS book_id，
                    - Chapter_S200Received_Date = CONVERT（VARCHAR（11），chap.chapter_s200_recd_dt，100），
                    Chapter_S200Received_Date = CONVERT（VARCHAR，chap.chapter_s200_recd_dt，105）+''+ CONVERT（VARCHAR，chap.chapter_s200_recd_dt，108），
                    - S200_JS_Schedule = CONVERT（VARCHAR（11），chap.chapter_s200_due_dt，100），
                    S200_JS_Schedule = CONVERT（VARCHAR，chap.chapter_s200_due_dt，105）+''+ CONVERT（VARCHAR，chap.chapter_s200_due_dt，108），
                    isnull（b.book_ms_pages，0）as Book_MS_pages，
                    b.book_inputrecdType为book_inputrecdType，
                      bookSplInstructions =（情况         
         &NBSP ;        当b.book_Spl_Instructions = '1' THEN 'YES'        
                  当b.book_Spl_Instructions = '0'，则 'NO' &NBSP ;      
              &NBSP ; End），
                      ProjectManager =（SELECT USER_NAME FROM EMS_DB.dbo.tblUser其中b.book_Project_Manager COLLATE DATABASE_DEFAULT = user_employeecode COLLATE DATABASE_DEFAULT）
，NBSP;   
                      DATEDIFF（DD，GETDATE（），chap.chapter_s200_due_dt）AS jsScheduleprogress，          
                      EDITOR_NAME =（SELECT USER_NAME FROM EMS_DB.dbo.tblUser  WHERE USER_ID = chap.chapter_s200_editor），NBSP;         
                    isnull（b.book_ce_level，chap.ce_level）as ce_level，  
的 （来自tblChapterStatusDetails为d，其中出价= b.bid和menu_serialno = '18' 和process_out_dt为空SELECT COUNT（chapter_id）），NBSP预编辑=; &NBSP ;   
                    CEdit =（从tblChapterStatusDetails中选择count（chapter_id）为d，其中bid = b.bid和 menu_serialno ='19'且process_out_dt为null），
                    CeQc =（从tblChapterStatusDetails中选择count（chapter_id）为d，其中bid = b.bid和 menu_serialno ='105'且process_out_dt为null），
                    （从tblChapterStatusDetails为d，其中出价= b.bid和&NBSP SELECT COUNT（chapter_id）; menu_serialno =" 106’ 和process_out_dt为空）LE =;，     &NBSP 
                    artwrk =（从tblChapterStatusDetails中选择count（chapter_id）为d，其中bid = b.bid和 menu_serialno ='20'且process_out_dt为null），
                    Edcheck =（从tblChapterStatusDetails中选择count（chapter_id）为d，其中bid = b.bid， menu_serialno ='23'，process_out_dt为
null），
                      PGN =（SELECT COUNT（chapter_id）从tblChapterStatusDetails为d，其中出价= b.bid和  menu_serialno = '21’ 和process_out_dt为空），NBSP;     
                    CEcorr =（从tblChapterStatusDetails中选择count（chapter_id）为d，其中bid = b.bid和 menu_serialno ='24'且process_out_dt为null），
                    PgnCorr =（从tblChapterStatusDetails中选择count（chapter_id）为d，其中bid = b.bid， menu_serialno ='25'，process_out_dt为
null），
                    EdCheck2 =（从tblChapterStatusDetails中选择count（chapter_id）为d，其中bid = b.bid， menu_serialno ='90'，process_out_dt为
null），
                    （从tblChapterStatusDetails为d SELECT COUNT（chapter_id）其中出价= b.bid和  menu_serialno = '26’ 和process_out_dt是
为null）数据集=;&NBSP，      
                      uploadError时=（SELECT COUNT（chapter_id）从tblChapterStatusDetails为d，其中出价= b.bid和  menu_serialno = '27' 和process_out_dt
为null），NBSP;    
                      - b.book_location， 
                      loc.location_name  AS book_location，
                      b.book_shortcutwf 作为shortcutworkflow，
                    b.book_Cust_Type as book_Cust_Type，
                    book_s50_recd_dt AS S50_Recd_dt，
                    isnull（DATEDIFF（dd，book_s50_recd_dt，GETDATE（）），0）AS TAT
$
                  FROM           
                    tblBook as b，
                     （SELECT            
       &NBSP ;           c.bid，           
max（c.chapter_s200_recd_dt）as chapter_s200_recd_dt，            
           min（c.chapter_s200_due_dt）as chapter_s200_due_dt，
                  （来自tblchapter c选择MAX（ce_level） ）as ce_level， &n BSP; 
                    （从tblchapter选择TOP1 chapter_s200_editor，其中chapter_s200_editor不为null，bid = c.bid）作为chapter_s200_editor
                      FROM           
                    - tblchapter as c，tblChapterStatusDetails as s
                    tblchapter as c cross join tblChapterStatusDetails as s
                     其中           
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; - （c.bid = s.bid或c.bid<> s.bid）和
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.bid不为null且s.bid不为null且
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_recd_dt> ='2011-04-01'和
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_recd_dt不为空且
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_due_dt不为空且
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; c.chapter_s200_finish_dt是 空&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 和c.scw_Status<>'1'
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;和（s.node_id = '58' 和s.menu_serialno = '90' 和&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; s.process_out_dt为null）&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; group by
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; c.bid）&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; as chap，tblLocation AS Loc
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP; WHERE&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; b.bid = chap.bid AND
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; b.book_location = loc.location_id AND
&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; b.book_id IS NOT NULL b.bid订购
SELECT
b.bid AS bid,
b.book_id AS book_id,
-- Chapter_S200Received_Date=CONVERT(VARCHAR(11),chap.chapter_s200_recd_dt,100),
Chapter_S200Received_Date=CONVERT(VARCHAR,chap.chapter_s200_recd_dt,105)+' '+CONVERT(VARCHAR,chap.chapter_s200_recd_dt,108),
-- S200_JS_Schedule=CONVERT(VARCHAR(11),chap.chapter_s200_due_dt,100),
S200_JS_Schedule=CONVERT(VARCHAR,chap.chapter_s200_due_dt,105)+' '+CONVERT(VARCHAR,chap.chapter_s200_due_dt,108),
isnull(b.book_ms_pages,0) as Book_MS_pages,
b.book_inputrecdType as book_inputrecdType,
bookSplInstructions=(case
WHEN b.book_Spl_Instructions ='1' THEN 'YES'
WHEN b.book_Spl_Instructions ='0' THEN 'NO'
End ),
ProjectManager=(SELECT user_name FROM EMS_DB.dbo.tblUser where b.book_Project_Manager COLLATE DATABASE_DEFAULT =user_employeecode COLLATE DATABASE_DEFAULT)
,
DATEDIFF(dd,getdate(),chap.chapter_s200_due_dt)AS jsScheduleprogress,
editor_name = (SELECT user_name FROM EMS_DB.dbo.tblUser WHERE user_id=chap.chapter_s200_editor),
isnull(b.book_ce_level,chap.ce_level) as ce_level,
preedit=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='18' and process_out_dt is null),
CEdit =(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='19' and process_out_dt is null),
CeQc=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='105' and process_out_dt is null),
LE=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='106' and process_out_dt is null),
artwrk=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='20' and process_out_dt is null),
Edcheck=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='23' and process_out_dt is
null),
Pgn=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='21' and process_out_dt is null),
CEcorr=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='24' and process_out_dt is null),
PgnCorr=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='25' and process_out_dt is
null),
EdCheck2=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='90' and process_out_dt is
null),
DataSet=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='26' and process_out_dt is
null),
UploadError=(select count(chapter_id) from tblChapterStatusDetails as d where bid=b.bid and menu_serialno='27' and process_out_dt
is null),
--b.book_location,
loc.location_name AS book_location,
b.book_shortcutwf as shortcutworkflow,
b.book_Cust_Type As book_Cust_Type,
book_s50_recd_dt AS S50_Recd_dt,
isnull(DATEDIFF (dd,book_s50_recd_dt,GETDATE()),0) AS TAT
FROM
tblBook as b,
(SELECT
c.bid,
max(c.chapter_s200_recd_dt) as chapter_s200_recd_dt ,
min(c.chapter_s200_due_dt) as chapter_s200_due_dt,
(SELECT MAx (ce_level) from tblchapter c) as ce_level,
(select TOP 1 chapter_s200_editor from tblchapter where chapter_s200_editor is not null and bid=c.bid) as chapter_s200_editor
FROM
-- tblchapter as c,tblChapterStatusDetails as s
tblchapter as c cross join tblChapterStatusDetails as s
where
-- (c.bid=s.bid or c.bid<>s.bid )and
c.bid is not null and s.bid is not null and
c.chapter_s200_recd_dt >='2011-04-01' and
c.chapter_s200_recd_dt is not null and
c.chapter_s200_due_dt is not null and
c.chapter_s200_finish_dt is null
and c.scw_Status<>'1'
and (s.node_id='58' and s.menu_serialno='90' and
s.process_out_dt is null)
group by
c.bid)
as chap , tblLocation AS Loc
WHERE
b.bid=chap.bid AND
b.book_location=loc.location_id AND
b.book_id IS NOT NULL order by b.bid
推荐答案
嗨Raghunadhan，
Hi Raghunadhan,
这个怎么样？
;WITH chap AS
(
SELECT
c.bid,
max(c.chapter_s200_recd_dt) as chapter_s200_recd_dt,
min(c.chapter_s200_due_dt) as chapter_s200_due_dt,
(SELECT MAx (ce_level) from tblchapter c) as ce_level,
(select TOP 1 chapter_s200_editor from tblchapter where chapter_s200_editor is not null and bid=c.bid) as chapter_s200_editor
FROM tblchapter as c cross join tblChapterStatusDetails as s
-- tblchapter as c,tblChapterStatusDetails as s
where c.bid is not null
and s.bid is not null
-- (c.bid=s.bid or c.bid<>s.bid )and
and c.chapter_s200_recd_dt >='2011-04-01'
and c.chapter_s200_recd_dt is not null
and c.chapter_s200_due_dt is not null
and c.chapter_s200_finish_dt is null
and c.scw_Status<>'1'
and s.node_id='58'
and s.menu_serialno='90'
and s.process_out_dt is null
group by c.bid
),
tblChapterStatusDetails as
(
select
bid,
menu_serialno,
count(chapter_id) as v_count
from tblChapterStatusDetails
where process_out_dt is null
group by bid,menu_serialno
)
SELECT
b.bid AS bid,
b.book_id AS book_id,
-- Chapter_S200Received_Date=CONVERT(VARCHAR(11),chap.chapter_s200_recd_dt,100),
Chapter_S200Received_Date=CONVERT(VARCHAR,chap.chapter_s200_recd_dt,105)+' '+CONVERT(VARCHAR,chap.chapter_s200_recd_dt,108),
-- S200_JS_Schedule=CONVERT(VARCHAR(11),chap.chapter_s200_due_dt,100),
S200_JS_Schedule=CONVERT(VARCHAR,chap.chapter_s200_due_dt,105)+' '+CONVERT(VARCHAR,chap.chapter_s200_due_dt,108),
isnull(b.book_ms_pages,0) as Book_MS_pages,
b.book_inputrecdType as book_inputrecdType,
bookSplInstructions=(case
WHEN b.book_Spl_Instructions ='1' THEN 'YES'
WHEN b.book_Spl_Instructions ='0' THEN 'NO'
End ),
ProjectManager=(SELECT user_name FROM EMS_DB.dbo.tblUser where b.book_Project_Manager COLLATE DATABASE_DEFAULT =user_employeecode COLLATE DATABASE_DEFAULT),
DATEDIFF(dd,getdate(),chap.chapter_s200_due_dt)AS jsScheduleprogress,
editor_name = (SELECT user_name FROM EMS_DB.dbo.tblUser WHERE user_id=chap.chapter_s200_editor),
isnull(b.book_ce_level,chap.ce_level) as ce_level,
preedit=ISNULL(T1.v_count,0),
CEdit =ISNULL (T2.v_count,0),
CeQc=ISNULL(T3.v_count,0),
LE=ISNULL(T4.v_count,0),
artwrk=ISNULL(T5.v_count,0),
Edcheck=ISNULL(T6.v_count,0),
Pgn=ISNULL(T7.v_count,0),
CEcorr=ISNULL(T8.v_count,0),
PgnCorr=ISNULL(T9.v_count,0),
EdCheck2=ISNULL(T10.v_count,0),
DataSet=ISNULL(T11.v_count,0),
UploadError=ISNULL(T12.v_count,0),
--b.book_location,
loc.location_name AS book_location,
b.book_shortcutwf as shortcutworkflow,
b.book_Cust_Type As book_Cust_Type,
book_s50_recd_dt AS S50_Recd_dt,
isnull(DATEDIFF (dd,book_s50_recd_dt,GETDATE()),0) AS TAT
FROM tblBook as b
INNER JOIN chap ON b.bid=chap.bid
INNER JOIN tblLocation AS Loc ON b.book_location=loc.location_id
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='18') T1
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='19') T2
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='105') T3
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='106') T4
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='20') T5
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='23') T6
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='21') T7
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='24') T8
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='25') T9
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='90') T10
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='26') T11
OUTER APPLY (SELECT v_count FROM tblChapterStatusDetails WHERE bid=b.bid AND menu_serialno='27') T12
WHERE b.book_id IS NOT NULL
order by b.bidBest问候，
将
这篇关于调整子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
