Re: Performence issue

From: Vinay Bhushan <bhushanvinay_at_gmail.com>
Date: 25 Jun 2006 07:19:35 -0700
Message-ID: <1151245175.765320.193790@y41g2000cwy.googlegroups.com>

Hi Bakker,

Here is how the query looks.

Select

-- Master. collumns [ 120]
-- Candidate.Columns [120]
-- Master Account Info [30]
-- Candidate Account info [30]
--

Matching_Results MRef
inner JOIN TRANEVENT_DG M ON (MRef.MatchObjIDCion = M.RCD_NUM) left outer JOIN TRANEVENT_DG C ON (MRef.MatchObjIDCing = C.RCD_NUM) INNER JOIN Match_BASIS RB ON (MRef.Match_basis = RB.Match_basis) INNER JOIN UserSysAcctView UV ON
( (M.ORG_ID = UV.ORG_ID AND M.BK_ID = UV.BK_ID AND M.ACCT_ID = UV.ACCT_ID) )
--OR (C.ORG_ID = UV.ORG_ID AND C.BK_ID = UV.BK_ID AND C.ACCT_ID = UV.ACCT_ID) )
INNER JOIN IVW_ACCT ACT ON ( ( M.ORG_ID = ACT.ORG_ID AND M.BK_ID = ACT.BK_ID AND M.ACCT_ID = ACT.ACCT_ID))
--OR (C.ORG_ID = ACT.ORG_ID AND C.BK_ID = ACT.BK_ID AND C.ACCT_ID = ACT.ACCT_ID) )
left outer JOIN IVW_ACCT C_ACT ON ( ( C.ORG_ID = C_ACT.ORG_ID AND C.BK_ID = C_ACT.BK_ID AND C.ACCT_ID = C_ACT.ACCT_ID))

left outer JOIN ISSUE_DG M_A     ON M_A.INSTR_ID= M.INSTR_ID
left outer JOIN ISSUE_DG C_A     ON C_A.INSTR_ID= C.INSTR_ID

INNER JOIN DP_MatchState RS ON RS.Id = MRef.NtkMatchState LEFT OUTER JOIN Match_NARRATIVE RNM ON RNM.MatchType = 'TRANSACTION' AND RNM.MatchReference = M.RCD_NUM
LEFT OUTER JOIN Match_NARRATIVE RNC ON RNC.MatchType = 'TRANSACTION' AND RNC.MatchReference = C.RCD_NUM

Sybrand Bakker wrote:

> On 24 Jun 2006 19:16:40 -0700, "Vinay Bhushan"
> <bhushanvinay_at_gmail.com> wrote:
>
> >i have a single souce table , Table a With contiains records for two
> >different entries for the same vendor by different accounting
> >instructions,
> >
> >BidId = 10,Person Name=ABC,PersonBidAmt=$100
> >BidId = 11,Person Name=ABC,PersonBidAmt=$110
> >
> >now the recors are to be shown as
> >
> >Original Rcds Vs IntutiveRecords
> >OrName,OrAmt,IntName,IntAmt
> >ABC,100,ABC,110
> >
> >for this reason i use
> >
> >Select A.name,A.Amt,B.name,B.Amt
> >from A
> >leftOuterjoin B -- There are many reasons why i do a left outer join
> >because i may not have the record in the b side at all
> >
> >Ihave more than 250 columns on each side A.250 cols and B.250 cols.
> >I have checked with indexes on the tables are are fine
> >
> >but this is causing a very big performence issue as this view is very
> >huge.31,000 records on each side is taking 2 minits to work.
> >
> >Could any one help me with this.
>
> 31000 records is not huge, it is a joke.
> As many people before you provide insufficient details, so you
> probably won't get any responses.
> My bet would be the outer join is causing this, but without an explain
> plan it is difficult to tell.
> Queries running for 2 minutes don't necessarily constitute a
> performance issue. They might if they are running every 2 seconds. If
> they are not: no performance issue.
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Sun Jun 25 2006 - 09:19:35 CDT