title: why does sorting cost me 70%?
body: <p>I have a view that is very slow:</p>
  
  <pre><code>ALTER  view [dbo].[TAT] as
  WITH d AS ( SELECT TAT = CASE WHEN TAT &gt; 191 THEN 192 ELSE TAT END,
     [MONTH entered],
     [YEAR] = DATEPART(yyyy,[datetime entered])
    FROM [SalesDWH].[dbo].[TurnAround] TurnAround
    left outer join hermes.lom.dbo.lom_specimen Lom
    on TurnAround.[specimen id] = lom.specimen
    LEFT outer join SalesDWH.dbo.Isomers Isomers
    on TurnAround.[specimen id] = Isomers.[accession id]
    WHERE lom.specimen is null
    and isomers.[accession id] is null
   -- GROUP BY CASE WHEN TAT &gt; 191 THEN 192 ELSE TAT END 
      --[datetime entered],[Month Entered]
  ),
  n AS ( SELECT TOP (192) n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
    FROM sys.all_columns ORDER BY [object_id]
  )
  SELECT TAT = n.n,
  
    [MONTH entered],
    [YEAR]
  
  FROM n LEFT OUTER JOIN d 
  ON n.n = d.TAT  
  
  
  
  GO
  </code></pre>
  
  <p>When I look at the execution plan, it tells me that that one of the steps which is SORTING costs me 70%.</p>
  
  <p>When I inspect the SORTING step it says my ORDER BY is:</p>
  
  <pre><code>[SalesDWH].[dbo].[QuickLabDump].Specimen ID Ascending, [SalesDWH].[dbo].[QuickLabDump].Date Entered Ascending, [SalesDWH].[dbo].[QuickLabDump].Time Entered Ascending, [SalesDWH].[dbo].[QuickLabDump].Date Completed Ascending, [SalesDWH].[dbo].[QuickLabDump].Time Completed Ascending, [SalesDWH].[dbo].[QuickLabDump].Practice Name Ascending, [SalesDWH].[dbo].[QuickLabDump].Practice Code Ascending, [SalesDWH].[dbo].[QuickLabDump].Order Count Ascending, [SalesDWH].[dbo].[QLMLISMapping].MLIS Practice ID Ascending, [SalesDWH].[dbo].[adjustedtime].TimeDifference Ascending
  </code></pre>
  
  <p>But I haven't specified an ORDER BY anywhere.</p>
  
  <p><strong>Questions:</strong></p>
  
  <ol>
  <li>Why does it say ASCENDING for ORDER BY if I don't have any ORDER BY's?</li>
  <li>How do I speed this view up?</li>
  </ol>
  
  <p>I don't know if this information is needed, but the above view is querying another view called <code>TURNAROUND</code>:</p>
  
  <pre><code>USE [SalesDWH]
  GO
  
  /****** Object:  View [dbo].[TurnAround]    Script Date: 05/25/2012 15:32:08 ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  
  
  
  ALTER VIEW [dbo].[TurnAround]
  AS
  SELECT     dbo.QuickLabDump.[Specimen ID], DATEPART(yyyy, dbo.QuickLabDump.[Date Entered]) AS [Year Entered], CAST(CAST(dbo.QuickLabDump.[Date Entered] AS DATE) 
                        AS DATETIME) + CAST(dbo.QuickLabDump.[Time Entered] AS TIME) AS [DateTime Entered], CAST(CAST(dbo.QuickLabDump.[Date Completed] AS DATE) AS DATETIME) 
                        + CAST(dbo.QuickLabDump.[Time Completed] AS TIME) AS [DateTime Completed], dbo.QuickLabDump.[Practice Code] AS [QL Practuce Code], 
                        dbo.QLMLISMapping.[MLIS Practice ID], DATEPART(m, dbo.QuickLabDump.[Date Entered]) AS [Month Entered], ROUND(CAST(DATEDIFF(mi, 
                        CAST(CAST(dbo.QuickLabDump.[Date Entered] AS DATE) AS DATETIME) + CAST(dbo.QuickLabDump.[Time Entered] AS TIME), 
                        CAST(CAST(dbo.QuickLabDump.[Date Completed] AS DATE) AS DATETIME) + CAST(dbo.QuickLabDump.[Time Completed] AS TIME)) AS FLOAT) / 60, 0) AS TAT, 
                        ROUND(CAST(DATEDIFF(mi, CAST(CAST(dbo.QuickLabDump.[Date Entered] AS DATE) AS DATETIME) + CAST('9:00AM' AS TIME), 
  
  
                        CAST(CAST(dbo.QuickLabDump.[Date Completed] AS DATE) AS DATETIME) 
                        + CAST(dbo.QuickLabDump.[Time Completed] AS TIME)) AS float) / 60, 0) 
  
                        AS [Hours TurnAround Since 9AM], TAT9AMStateAdjusted=                   
  
  
  
                        ROUND(CAST(DATEDIFF(mi, CAST(CAST(dbo.QuickLabDump.[Date Entered] AS DATE) AS DATETIME) 
                        + CAST('9:00AM' AS TIME),                                            
                        CAST(CAST(dbo.QuickLabDump.[Date Completed] AS DATE) AS DATETIME) 
                        + CAST(dbo.QuickLabDump.[Time Completed] AS TIME)) AS float) / 60, 0)                         
  
  
                        +dbo.adjustedtime.timedifference
                        ,dbo.QuickLabDump.[Order Count], CONVERT(VARCHAR(8), DATEADD(D, - (1 * DATEPART(dw, dbo.QuickLabDump.[Date Entered])) + 6,
                         dbo.QuickLabDump.[Date Entered]), 1) AS [Week Ending]
  FROM         dbo.QuickLabDump 
               INNER JOIN dbo.QLMLISMapping 
               ON dbo.QuickLabDump.[Practice Code] = dbo.QLMLISMapping.[Quicklab ID]
               left outer join dbo.AccountState
               on dbo.QuickLabDump.[Practice Code]=dbo.AccountState.[Account]
               left outer join dbo.AdjustedTime 
               ON dbo.AccountState.[state]=dbo.adjustedtime.[state]
  WHERE     (dbo.QuickLabDump.[Practice Code] NOT LIKE 'test%') AND (dbo.QuickLabDump.[Specimen ID] NOT LIKE 'of%') AND (dbo.QuickLabDump.Outcome &lt;&gt; 'rejected')
  GROUP BY dbo.QuickLabDump.[Specimen ID], dbo.QuickLabDump.[Date Entered], dbo.QuickLabDump.[Time Entered], dbo.QuickLabDump.[Date Completed], 
                        dbo.QuickLabDump.[Time Completed], dbo.QuickLabDump.[Practice Name], dbo.QuickLabDump.[Practice Code], dbo.QuickLabDump.[Order Count], 
                        dbo.QLMLISMapping.[MLIS Practice ID],dbo.adjustedtime.timedifference
  HAVING      (dbo.QuickLabDump.[Order Count] = MAX(dbo.QuickLabDump.[Order Count])) OR
                        (dbo.QuickLabDump.[Order Count] IS NULL)
  
  
  
  
  GO
  </code></pre>
  
  <p>Here are the table definitions:</p>
  
  <pre><code>CREATE TABLE [dbo].[QuickLabDump](
      [Specimen ID] [varchar](50) NULL,
      [Client Key] [int] NULL,
      [Outcome] [varchar](50) NULL,
      [Medications] [varchar](max) NULL,
      [Date Collected] [date] NULL,
      [Time Collected] [time](0) NULL,
      [Date Entered] [date] NULL,
      [Time Entered] [time](0) NULL,
      [Date Completed] [date] NULL,
      [Time Completed] [time](0) NULL,
      [Test Date] [date] NULL,
      [Test Time] [time](0) NULL,
      [Practice Name] [varchar](500) NULL,
      [Practice Code] [varchar](50) NULL,
      [Requesting Physician] [varchar](500) NULL,
      [Other Medications] [varchar](max) NULL,
      [Order Comments] [varchar](max) NULL,
      [Reference Number] [varchar](500) NULL,
      [Order Count] [int] NULL,
      [QuickLabDumpID] [int] NOT NULL,
      [Source] [varchar](50) NULL,
      [MLIS] [varchar](50) NULL,
   CONSTRAINT [PK_QuickLabDump_1] PRIMARY KEY CLUSTERED 
  (
      [QuickLabDumpID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>