|
kdeutsch
Yak Posting Veteran
83 Posts |
 Posted - 2009-05-28 : 13:39:38
|
| I have a mungo query with lost of sub-select to get information viewed in a datagrid on and aspx page. The problem i have is it has gotten so big its timing out the page. I could set the connection timeout to zero but I am not done adding to query and need to find a way to optimize. Any Ideas???SELECT at.UPC as UIC, ut.Addr_City as City, at.Auth_Para_Dsg as PARA, at.Auth_Line_Dsg as LINE, at.GRADE, substring(at.POSC, 1,4) as DMOS, case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER, at.AUTH_STR, CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0 ELSE case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS, (Select(Count(ps.SSN_SM)) from SIDPERS..SIDPERS.PERS_SVCMBR_TBL as ps Inner Join SIDPERS..SIDPERS.PERS_DUTY_POSN_TBL as dp on ps.MPC = dp.MPC and ps.ASG_SEQ_NBR = dp.ASG_SEQ_NBR where dp.UPC = at.UPC and dp.AUTH_Para_DSG = at.Auth_Para_Dsg and dp.AUTH_Line_DSG = at.Auth_Line_Dsg and ps.REC_PREC = '99999999' and ps.POSN_NBR_EXCESS_IND not in ('999F','9991','999C')) as ASGN_STR, (Select(Count(ac.strAcn)) from tblACNREquest as ac where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and dtExpire >= getdate() and dtCancelACN is null) as ACN, at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0 ELSE case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - (Select(Count(ps.SSN_SM)) from SIDPERS..SIDPERS.PERS_SVCMBR_TBL as ps Inner Join SIDPERS..SIDPERS.PERS_DUTY_POSN_TBL as dp on ps.MPC = dp.MPC and ps.ASG_SEQ_NBR = dp.ASG_SEQ_NBR where dp.UPC = at.UPC and dp.AUTH_Para_DSG = at.Auth_Para_Dsg and dp.AUTH_Line_DSG = at.Auth_Line_Dsg and ps.REC_PREC = '99999999' and ps.POSN_NBR_EXCESS_IND not in ('999F','9991','999C')) - (Select(Count(ac.strAcn)) from tblACNREquest as ac where ac.strUic = at.UPC and ac.strPara = at.Auth_Para_Dsg and ac.strLine = at.Auth_Line_Dsg and dtExpire >= getdate() and dtCancelACN is null) END AS VAC, case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as Unit_Type, (Select top 1 case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' when 'R' then 'Rear Det UIC' Else '' end from tblStatUIC where st.dtExpire > Getdate() and st.strUIC = ut.UPC) as UNIT_StatFROM SIDPERS..SIDPERS.PERS_UNIT_TBL as ut Inner join SIDPERS..SIDPERS.PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join recruitDotNet.dbo.tblACNRequest as ac on ac.strUIC = at.UPC Left Outer Join RecruitDotNet.dbo.tblStatUic as st on st.strUIc = at.UPCwhere at.upc = '77726' and Substring(at.grade, 1,1) = 'E' And ut.OESTS = 'N'Group by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg, ut.ADDR_CITY, at.GRADE, substring(at.POSC, 1,4), at.AUTH_PERS_IDENT, at.AUTH_DOC_POSN_TITLE, at.ASGN_STR, at.DOC_NBR, st.strStat, st.dtExpire, st.strUic, ut.UPC, at.AUTH_STR, ut.RPT_SEQ_CODEOrder by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg |
|
Speed up a sql 2005 to oracle query