Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning Optimizing ORDER BY query RE: Optimizing ORDER BY query

  • June 23, 2014 at 1:59 am

    #1723911

    arijit rath (6/23/2014)

    Actually it is not a sp but a inline query with order by in the OLEDB source. The query is using 3 views in the join condition which belong to a different team . Hence I cannot making any changes to those views.

    As soon as I remove the order by the query starts populating the data into the Staging table instantly . Sometmes the number of rows are 200-400 million.

    Regarding the Issorted property and the sortkeyposition I tried using that but that actually does not help a great deal as it tells SSIS that the data is pre-sorted .

    Regarding the final business logic sproc that generates the output extracts yes I need the order by because it is comparing id with id+1 and doing some manipulations in a cursor. Again I dont want to touch this sproc for now .if optimization can be done at the extraction level then it would be good for starters.

    The ORDER BY may be making one or more of the views fully materialize. Your original suggestion of put the data into a working table and sorting that seems to be the best option at this point,

    --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)