Post reply

View performance

  • August 10, 2010 at 10:06 am

    #238823

    Hi friends,

    Is there a way to tune this view by anyway?

    CREATE VIEW [dbo].[servew] AS

    select TOP 100 percent a.create_to,a.to_code,

    b.prof_code sh_prof_code,

    c.prof_code sl_prof_code,

    x.fname sh_fname,

    x.mint sh_mint,

    x.lname sh_lname,

    b.eventid sh_eventid,

    y.fname sl_fname,

    y.mint sl_mint,

    y.lname sl_lname,

    c.eventid sl_eventid

    from serv a, shdept b, sldept c, inst x, inst y

    where a.to_code = b.to_code and

    a.create_to = c.create_to and

    b.prof_code = x.prof_code and

    c.prof_code = y.prof_code;

    When we run a select on the view which returns no records, the cost is very high at 81% for index pk_inst(primary key on inst.prof_code) in inst table.

    Please give me your suggestions. Thank you

  • August 10, 2010 at 10:07 am

    #1204887

    Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 15, 2010 at 1:41 pm

    #1207098

    Hi There,

    Changing the query to explicit INNER JOINs with ON statements might prevent improper execution plan.

    In your current query, their might be a possibility that SQL first joins all tables and then applies a filter.

    By explicitely using INNER JOIn with ON, you will be sure that the tables are pre-filtered before the are joined. As a result less I/O will be needed.

    Hope this will help you out.

    Kind regards,

    Dave

  • August 15, 2010 at 1:45 pm

    #1207100

    By the way,

    Also I should get rid of the "TOP 100 percent" if you don't use it.

    Kind regards,

    Dave

  • August 15, 2010 at 9:25 pm

    #1207137

    Dave Hoogendoorn (8/15/2010)

    Hi There,

    Changing the query to explicit INNER JOINs with ON statements might prevent improper execution plan.

    In your current query, their might be a possibility that SQL first joins all tables and then applies a filter.

    By explicitely using INNER JOIn with ON, you will be sure that the tables are pre-filtered before the are joined. As a result less I/O will be needed.

    Hope this will help you out.

    Kind regards,

    Dave

    Do you have a coded example that shows such a thing?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 16, 2010 at 9:08 am

    #1207430

    All depends on how many rows you have in each table, which columns are indexed etc - i agree with previous post - not using INNER JOIN won't be helping

    Sometimes SQL Server doesn't go the right way on a join i.e. pick the smallest dataset to start with. Using sub-selects in the SELECT and FROM sections can help it to make the correct decision, but the answer lies in knowing the data, indexes etc.

  • August 16, 2010 at 12:22 pm

    #1207512

    Please follow Gail's recommendations and then we dont have to guess...

    with the above requested info we can give you an answer very quickly

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply