Urgent, this script runs in a view and takes too long ,

  • 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'

    ***************************************************************************

  • 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

  • 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?"

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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