Posted by bhushanvinay on 06/25/06 14:16

Hi Erland,

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
Erland Sommarskog wrote:

> (bhushanvinay@gmail.com) writes:
> > 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
> >
> > 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.
>
> No, you can't expect to get any help with the information you have
> provided. For starters, post the actual query and also the CREATE
> TABLE and CREATE INDEX statments for the table(s) in question.
>
> Could you also clarify: is there 31000 rows in the table totally,
> or does the query return 31000 rows?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]