|
|
Posted by Erland Sommarskog on 06/25/06 17:22
(bhushanvinay@gmail.com) writes:
> 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
I'm sorry, but this is not taking you any further.
Please understand that assisting with performance issues over a
distance is from the start is a difficult task, because there are so
many parameters that are involved: indexes, table sizes, distribution
etc.
Trying to assist with a performance problem given only a partial
query, with some comment about "master" and "candidate" that does
not fit with the query. At least, I cannot connect "master" or
"candidate" to any of the table names.
There there is a view of which I have no idea what is behind it.
And on top of all there are some OR conditions that have been commented
away. What role do they play in the drama?
The first suggestion I will give you is to try to cut down the query to
involve fewer tables. (Nevermind if this leads to incorrect results.) The
purpose of this is to not speed up the query, but to narrow down where the
problem really is. Judging from your first post, it appears that this is
the crucial part:
FROM Matching_Results MRef
JOIN TRANEVENT_DG M ON MRef.MatchObjIDCion = M.RCD_NUM
LEFT JOIN TRANEVENT_DG C ON MRef.MatchObjIDCing = C.RCD_NUM
Then again, I've been in this game long enough to know that the problem
is often in a different place than you think.
Once you have a leaner query which still performs badly, then you can
post:
o That query *in full*
o CREATE TABLE statements for the involved tables (including those
added by any views) Don't forget constraints.
o CREATE INDEX statements for the same tables.
o Indication of table sizes.
o The output when the query is run preceeded by SET STATISTICS PROFILE ON.
The main reason I ask you to first cut down the query, is that I fear
that there would be too many tables to look at, not at least with the
view expanded. But there is also a fair chance that when you work with
the query that you are able to find the problem yourself.
--
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
Navigation:
[Reply to this message]
|