May 28, 2009 at 10:58 am
I have been adding to this query that runs over a linked connection from a sql 2005 server to a oracle db. This thing has become a bahemoth but they need all the information that is here and more yet, but it is timing out my datagrid now, i know I can set timeout on sql connection to 0 but first wanted to find a way to optimize. Woould using a Select * from OpenQuery(LinkedServer, Query)
do teh trick and how could i change my existing code to pull this way or is there a better way.
Thanks
SELECTat.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_Stat
FROM 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.UPC
where at.upc = '77726' and Substring(at.grade, 1,1) = 'E' And ut.OESTS = 'N'
Group byat.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_CODE
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
May 28, 2009 at 12:02 pm
Performance of the affected query would be as good or as bad as the performance is in the Oracle database.
Ask you Oracle DBA to do two things for you:
1) Get an explain plan for your query, you are gonna find Oracle explain plan pretty easy to follow.
2) Trace your query, tkprof the resulting trace file and help you in identifying wait events.
Those are the two tools you need to finetune affected query.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2009 at 12:10 pm
How do I trace a query to see where its sticking, I have a good guess where its taking a lot of time but want to make sure. The Oracle variant of this code populates in 4 seconds, but the sql code takes 1.45 over a linked server.
May 28, 2009 at 1:03 pm
Shall I understand query takes 4 seconds on Oracle but 1 minute 45 seconds to return when over a link?
How much data does the query returns?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 28, 2009 at 1:06 pm
This is just a sampling but depending on how many records in a UIC it can be from 5 to 100
77726ROSEVILLE02614E979T5I30300TDACritical
77726ROSEVILLE02615E879T5I100811TDACritical
77726ROSEVILLE02616E779T4I30201TDACritical
May 28, 2009 at 1:12 pm
Here is the query I am rebuilding with an OPENQUERY to the Oracle tables it it runs 5 times as fast but I am having trouble with this one piece swithcing it to Oracle syntax. My sql syntax does not work for this. I need only the number is in bewteen the () and it then needs to be converted to numeric so that I can add to the ASGN_STR column. This column at.AUTH_DOC_POSN_TITLE has a numeric string at the end of it as such
OVERSTRENGTH SSG (1)
Maintenance SGT (5)
Cook SSG (1)
Truck DriverSGT (2)
Computer Repair SPC (8)
Heavy Maintenance SSG (0)
PBO SGT (1)
Drill SPC (4)
Here is current code I am working on but don't know how to do this in Oracle syntax.
Select* from OPENQUERY(SIDPERS, '
SELECTat.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substr(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,
at.ASGN_STR,
at.AUTH_DOC_POSN_TITLE,
case when at.AUTH_DOC_POSN_TITLE like ''(%)'' then substr(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') = 0 THEN 0
FromSIDPERS.PERS_UNIT_TBL ut Inner join
SIDPERS.PERS_AUTH_STR_TBL at on at.UPC = ut.UPC')
May 28, 2009 at 1:14 pm
If query takes only 4 seconds on Oracle side I would say connectivity is the issue.
Here is how you can trace Oracle side of the query...
Set your environment... e.g. ORACLE_HOME and ORACLE_SID
Initiate a SQLPlus session...
alter session set timed_statistics = true;
alter session set sql_trace = true;
set autotrace on explain
alter session set events='10046 trace name context forever, level 12';
++ EXECUTE TROUBLED SQL ++
alter session set sql_trace = false;
alter session set timed_statistics = false;
show parameter user_dump_destination
Go to whatever place user_dump_destination is pointing
tkprof the last large *.trc file you see there
Look at *.trf file looking for Wait Events section
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply