October 20, 2008 at 3:49 pm
Hello all,
First of all i need to tell every 1 i am new at this. I have this view that runs fine in QA server , but when put it in UAT(testing) this view has perfomance issues. one idea i have is becasue we have a very large amount of data in uat than in QA but still it sholdnt take this long ( when run the view in UAT it almost kind a hangs).
i really need to fix this as my boss is up my ****. any 1s help will be so much helpfull.
below is the script of the view
****** Object: View dbo.uvw_EverTracHoldings Script Date: 10/16/2008 1:36:28 PM ******/
CREATE VIEW dbo.uvw_EverTracHoldings
AS
SELECT h.PID, pmf1.PortfolioCode, smf1.SecurityName, smf1.SecurityId,
pmf1.AccountNbr, smf2.PerfClassPmf,
smf1.SecurityTypeCd, h.SiebelProductDescription,
h.Qty, h.MktPrc,
h.MktValue, h.LoadDateTime as CurrentDt,
h.MktValueExchgRate, smf1.BaseCrncyCd
FROM dbo.Holdings h WITH (NOLOCK), Holdings_SMF1 smf1 WITH (NOLOCK), Holdings_SMF2 smf2 WITH (NOLOCK), Holdings_PMF1 pmf1 WITH (NOLOCK)
WHERE h.PortiaSecurityName = smf1.Security
ANDh.portiasecurityname = smf2.security
ANDh.AccountName = pmf1.AccountName
ANDh.SourceId = 'SuperPortia'
***************************************************************************
October 20, 2008 at 4:05 pm
Okay, I think I know where the problem is - however, you are going to have to determine how to fix it. The easiest way to find the problem is to format the SQL so it is easier to read:
Create View dbo.uvw_EverTracHoldings
As
Select h.PID
,pmf1.PortfolioCode
,smf1.SecurityName
,smf1.SecurityId
,pmf1.AccountNbr
,smf2.PerfClassPmf
,smf1.SecurityTypeCd
,h.SiebelProductDescription
,h.Qty
,h.MktPrc
,h.MktValue
,h.LoadDateTime As CurrentDt
,h.MktValueExchgRate
,smf1.BaseCrncyCd
From dbo.Holdings h With(Nolock)
,Holdings_SMF1 smf1 With(Nolock)
,Holdings_SMF2 smf2 With(Nolock)
,Holdings_PMF1 pmf1 With(Nolock)
Where h.PortiaSecurityName = smf1.Security
And h.portiasecurityname = smf2.security
And h.AccountName = pmf1.AccountName
And h.SourceId = 'SuperPortia'
Now, I can immediately see that there are some potential problems here. First off - what is the relationship between Holdings and Holdings_SMF1? Between Holdings and Holdings_SMF2? Between Holdings and Holdings_PMF1?
Instead of using the above syntax, it would be a lot clearer if you changed it to:
From dbo.Holdings h With(Nolock)
Inner Join dbo.Holdings_SMF1 smf1 With(Nolock) On smf1.Security = h.PortiaSecurityName
Inner Join dbo.Holdings_SMF2 smf2 With(Nolock) On smf2.Security = h.PortiaSecurityName
Inner Join dbo.Holdings_PMF1 pmf1 With(Nolock) On pmf1.AccountName = h.AccountName
Where h.SourceId = 'SuperPortia'
With this - can you verify that the relationships are correct? Are there any other columns that need to be included for the relationships?
Are the key columns in all tables indexed? The key columns are those columns used in the ON portion of the join clause.
Is the SourceID column indexed?
And finally, make sure you really understand when to use 'Nolock' and what the implications are. If you are not sure, search this site and you will find plenty of articles on when to use it and what some of the problems are.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 20, 2008 at 4:08 pm
Gonna need more information.
How much data is "very large amount of data"? How many rows in each of the 4 tables you are working with?
How long is "sholdnt take this long"?
What are the relationships between the tables? Are each of those Many to Many?
What are you trying to retrieve out of it, are you just doing "Select * from uvw_EverTracHoldings"? How many rows are you returning with your selection?
As of right now, you've basically called a Mechanic and said "My car doesn't work. It's a Nissan, what's wrong with it?"
October 20, 2008 at 4:10 pm
first thing I'fd suggest is avoiding trying to use NOLOCK;
the NOLOCK in should only be used as a last ditch attempt to improve performance...you usually don't need to allow dirty reads in a view.
second thing, are any of the tables used a view? view-of-a-view perforamnce has always sucked for me.
For convenience, I'd rewrite the view like this:
CREATE VIEW dbo.uvw_EverTracHoldings
AS
SELECT
h.PID,
pmf1.PortfolioCode,
smf1.SecurityName,
smf1.SecurityId,
pmf1.AccountNbr,
smf2.PerfClassPmf,
smf1.SecurityTypeCd,
h.SiebelProductDescription,
h.Qty,
h.MktPrc,
h.MktValue,
h.LoadDateTime as CurrentDt,
h.MktValueExchgRate,
smf1.BaseCrncyCd
FROM dbo.Holdings h
ON h.PortiaSecurityName = smf1.Security
inner join Holdings_SMF1 smf1
ON h.portiasecurityname = smf2.security
inner join Holdings_SMF2 smf2
ON smf1.security = smf2.security
inner join Holdings_PMF1 pmf1
ON h.AccountName = pmf1.AccountName
WHERE h.SourceId = 'SuperPortia'
finally, note each of the JOINS being used....they may be important enough to have indexes on them...do they?
for example, are there indexs on these:
SiebelProductDescription.PortiaSecurityName
SiebelProductDescription.AccountName
Holdings_SMF1.Security
Holdings_SMF2.Security
Holdings_SMF2.AccountName
Lowell
October 21, 2008 at 6:01 am
Also, if you can, post the actual execution plan. This can show where you have missing or incorrect indexes, indicate the possibility of out of date statistics... all useful stuff for troubleshooting query performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2008 at 6:18 am
Ok guys first of all thanking u all of ur input.u got many advises and some ask for more information so i am going to work on this and get back to every 1. i hope i can fix it.
Thanks
B
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply