SQL Server Performance

Optimizing ORDER BY query

Discussion in 'ALL SQL SERVER QUESTIONS' started by arijitnist, Jun 20, 2014.

  1. arijitnist Member

    Hi,

    In my SSIS PackageI have a query which is using a combination of views to retrieve records to insert into a stage table. The query retrieves millions of rows and is running very slow. On analysis I figured out that the order by clause is the one which is causing the query to run for hours. The order by is on columns from the different views.

    This ordering is necessary and unfortunatlely , I cannot put any indices as these views belong to a different team. The only solution I can think of is removing the order by from the current query and then inserting the rows to another stage table by ordering on the first stage table.

    Is there any other approach I can take...
  2. Naveen Gupta New Member

    Can you please provide some more details so that one should be able to get into the issue?
  3. davidfarr Member

    If you do not have access to alter or index the views of the other team, then there is no way (that I know of) to improve performance on those queries.
    However I do not think you will save any time by extracting the data from the views into a staging table and then indexing or ordering the staging table records, since that process will also take just as long as the original query.

    It seems absurd (from a company protocol perspective) that you cannot index the views owned by 'the different team'. Surely there is a senior manager or senior DBA that understands your situation and can authorize or request changes or indexes on those views ? All 'teams' work for the same company, surely ?

Share This Page