Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Quicken up a SQL 2005 to Oracle Query Expand / Collapse
Author
Message
Posted Thursday, May 28, 2009 10:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:57 PM
Points: 54, Visits: 107
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

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_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 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_CODE
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg
Post #725255
Posted Thursday, May 28, 2009 12:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #725309
Posted Thursday, May 28, 2009 12:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:57 PM
Points: 54, Visits: 107
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.
Post #725315
Posted Thursday, May 28, 2009 1:03 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #725362
Posted Thursday, May 28, 2009 1:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:57 PM
Points: 54, Visits: 107
This is just a sampling but depending on how many records in a UIC it can be from 5 to 100

77726 ROSEVILLE 026 14 E9 79T5 I 3 0 3 0 0 TDA Critical
77726 ROSEVILLE 026 15 E8 79T5 I 10 0 8 1 1 TDA Critical
77726 ROSEVILLE 026 16 E7 79T4 I 3 0 2 0 1 TDA Critical
Post #725366
Posted Thursday, May 28, 2009 1:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:57 PM
Points: 54, Visits: 107
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, '
SELECT at.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
From SIDPERS.PERS_UNIT_TBL ut Inner join
SIDPERS.PERS_AUTH_STR_TBL at on at.UPC = ut.UPC')
Post #725372
Posted Thursday, May 28, 2009 1:14 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #725377
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse