We help IT Professionals succeed at work.
Get Started

Help..SQL Server 2012 Query when using 'Order By' Query runs very slow

sbornstein2
sbornstein2 asked
on
330 Views
Last Modified: 2014-05-21
I am using SQL Server 2012 on my local home machine and the query I have runs within 1 second everytime.   On a new production server VM machine with a lot of memory etc. the same query takes over a minute to run only when using the order by.   It runs slower and slower each time as well.   Here is the query:   The first runs in about 1 second without the order by.   The order by it takes over a minute 20K records.   The order table OrderNumberId is the Primary Key and I also updated statistics on the table as well.   Each time I run it the query takes longer and longer with the order by.

This is what profiler trace is running:

--this query I have the order by commented out - 1 second
SELECT
[Extent1].[OrderNumberId] AS [OrderNumberId],
[Extent1].[OrderNumber] AS [OrderNumber],
[Extent1].[OrderTime] AS [OrderTime],
[Extent1].[PickupTime] AS [PickupTime],
[Extent1].[SortNumber] AS [SortNumber],
[Extent1].[PickupTimeSort] AS [PickupTimeSort],
[Extent1].[PickupCity] AS [PickupCity],
[Extent1].[PickupDate] AS [PickupDate],
[Extent1].[PickupState] AS [PickupState],
[Extent1].[Dcity] AS [Dcity],
[Extent1].[Dstate] AS [Dstate],
[Extent1].[DeadLineTime] AS [DeadLineTime],
[Extent1].[Po1] AS [Po1],
[Extent1].[PiecesWeight1] AS [PiecesWeight1],
[Extent1].[PiecesWeight2] AS [PiecesWeight2],
[Extent1].[ProNumber] AS [ProNumber],
[Extent1].[OrderTakenBy] AS [OrderTakenBy],
[Extent1].[CustCode] AS [CustCode],
[Extent1].[VehicleCodeName] AS [VehicleCodeName],
[Extent1].[OrderStatusCodeName] AS [OrderStatusCodeName],
[Extent1].[OrderStatusOrder] AS [OrderStatusOrder],
[Extent1].[DriverCode] AS [DriverCode]
FROM (SELECT
      [VwOrderDispatch].[OrderNumberId] AS [OrderNumberId],
      [VwOrderDispatch].[OrderNumber] AS [OrderNumber],
      [VwOrderDispatch].[OrderTime] AS [OrderTime],
      [VwOrderDispatch].[PickupTime] AS [PickupTime],
      [VwOrderDispatch].[SortNumber] AS [SortNumber],
      [VwOrderDispatch].[PickupTimeSort] AS [PickupTimeSort],
      [VwOrderDispatch].[PickupCity] AS [PickupCity],
      [VwOrderDispatch].[PickupDate] AS [PickupDate],
      [VwOrderDispatch].[PickupState] AS [PickupState],
      [VwOrderDispatch].[Dcity] AS [Dcity],
      [VwOrderDispatch].[Dstate] AS [Dstate],
      [VwOrderDispatch].[DeadLineTime] AS [DeadLineTime],
      [VwOrderDispatch].[Po1] AS [Po1],
      [VwOrderDispatch].[PiecesWeight1] AS [PiecesWeight1],
      [VwOrderDispatch].[PiecesWeight2] AS [PiecesWeight2],
      [VwOrderDispatch].[ProNumber] AS [ProNumber],
      [VwOrderDispatch].[OrderTakenBy] AS [OrderTakenBy],
      [VwOrderDispatch].[CustCode] AS [CustCode],
      [VwOrderDispatch].[VehicleCodeName] AS [VehicleCodeName],
      [VwOrderDispatch].[OrderStatusCodeName] AS [OrderStatusCodeName],
      [VwOrderDispatch].[OrderStatusOrder] AS [OrderStatusOrder],
      [VwOrderDispatch].[DriverCode] AS [DriverCode]
      FROM [dbo].[VwOrderDispatch] AS [VwOrderDispatch]) AS [Extent1]
--ORDER BY [Extent1].[OrderNumberId] DESC

With the order by uncommented it takes over a minute and gets worst every time I run it.

Any ideas what I can do or look for?
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE