473,320 Members | 1,887 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

I have a question With Related to performence

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.

Jun 25 '06 #1
4 1131
(bh**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 25 '06 #2
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:
(bh**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Jun 25 '06 #3
(bh**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 25 '06 #4
Have you seen this?
http://support.microsoft.com/default...b;EN-US;836136

----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<bh**********@gmail.com> wrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
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.

Jun 26 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Maxwell Hammer | last post by:
Hi all, This is related to an earlier post 'Help with thread related tracebacks'...for which I have had no feedback yet :-( How should a thread complete i.e. how should it exit? Reading the...
2
by: Uri Lazar | last post by:
hi, im working on this for a long time. i'm using MSsql-server2000 i have a table that records users visits to rooms. the columns are room_id, user_id, visits. i want to write a query that can...
23
by: VB Programmer | last post by:
Variable scope doesn't make sense to me when it comes to Try Catch Finally. Example: In order to close/dispose a db connection you have to dim the connection outside of the Try Catch Finally...
4
by: Paul | last post by:
Hi! I want my web application to be able to handle files with the extension ..mspx (my own extension). The new extension should be handled exatly the way aspx files are handled, but with some...
2
by: dBNovice | last post by:
Hi all! I have 3 separate forms: Tasks, Subtasks, and Elements. All 3 is related by TaskId and Subtasks and Elements are related by SubtaskID. In the DB after I add a task, I want to be able to...
3
by: ss | last post by:
hI, Where do I created the Session Related things in ASP.NET 2.0? How do I do that? Could anybody give some sample code? bye
1
pritipshah
by: pritipshah | last post by:
Hi All, I am using vtiger open source CRM and in that they are using FCKeditor for Image Upload (Resource browser window) but it is not upload or make new folder for image. Vtiger CRM...
16
by: DAHMB | last post by:
I am not sure if I worded my question correctly. But what I want to do is run a report from two related tables as follows: The first table is called Category - it has one field, called...
0
by: cannonpm | last post by:
Greetings and salutations. I have developed an A2K3 MDB and have a search form modeled after Allen Browne's search form (http:// allenbrowne.com/ser-62.html). It works well except for records which...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.