ORDER BY clause is causing my query to run for a very long time (possibly indefinitely!)

[Former Member]

Hi Everyone,

I have a query that runs fine when I do not include an ORDER BY clause, however once the a fore mentioned clause is added the query run for an extended period (potentially indefinitely) without producing any results!

Here is the query -

/*

Name:            Customer Transaction History

Author:         David Dartnell

Created:        9/07/2017

Last Updated:    22/10/2014

Purpose:        Lists the sales history of items

Usage:

Support Issue ID/Software Fix: N/A

Supported Versions: SQL 2008R2

DECLARE 
      @fromDate DATE
    , @toDate DATE
    , @begItemCode nvarchar(20)
    , @endItemCode nvarchar(20)
    , @whsCode nvarchar(5)
    , @indCode nvarchar(5)
    , @custNo nvarchar(10)

SET @fromDate = ''
    IF @fromDate = ''
        SET @fromDate = '1999-01-01'

SET @toDate = '2014-10-22'
    IF @toDate = '1753-01-01'
        SET @toDate = GETDATE()

SET @begItemCode = ''
    IF @begItemCode = ''
        SET @begItemCode = '00%'

SET @endItemCode = '' + 'Z'
    IF @endItemCode = 'Z'
        SET @endItemCode = 'ZZZZZ%'

SET @whsCode = ''
    IF @whsCode = ''
        SET @whsCode = '%'

SET @indCode = ''
    IF @indCode = ''
        SET @indCode = '%'

SET @custNo = ''
    IF @custNo = ''
        SET @custNo = '%'

/*** SAP: Invoices ***/

SELECT
T1.DocDate AS 'Document Date'
, T1.DocNum AS 'Document No.'
--, 'Invoice' AS 'Doc Type'
, T1.CardCode collate DATABASE_DEFAULT AS 'Cust Code'
, T2.CardName collate DATABASE_DEFAULT AS 'Customer Name'
, T0.ItemCode collate DATABASE_DEFAULT AS 'Item Code'
, T0.Dscription AS 'Item Description'
, T0.Quantity
, T0.WhsCode collate DATABASE_DEFAULT AS 'Warehouse'
, CAST(T0.LineTotal AS decimal(10,2)) AS 'Line Net'
, CAST(T0.StockValue AS decimal(10,2)) AS 'Line Cost'
, CAST(T3.AvgPrice AS decimal(10,2)) AS 'Item Avg Cost'
, CAST((T0.LineTotal - T0.StockValue) AS decimal(10,2)) AS 'Profit / Loss'
, CASE 
    WHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS nvarchar) = '0.00' THEN 'N/A'
    WHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS nvarchar) IS NULL THEN 'N/A'
    ELSE CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS nvarchar) END
    AS 'Profit / Loss %'
, T4.CardName AS 'Vendor Name'
, T5.SlpName AS 'Salesman Name'

FROM AU.dbo.INV1 T0
INNER JOIN AU.dbo.OINV T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN AU.dbo.OCRD T2 ON T2.CardCode = T1.CardCode    -- Used to get Customer Name
INNER JOIN AU.dbo.OITM T3 ON T3.ItemCode = T0.ItemCode
INNER JOIN AU.dbo.OCRD T4 ON T4.CardCode = T3.CardCode    -- Used to get preferred Vendor
INNER JOIN AU.dbo.OSLP T5 ON T5.SlpCode = T1.SlpCode

WHERE T1.DocType = 'I' 
    AND (T0.ItemCode >= @begItemCode AND T0.ItemCode <= @endItemCode)
    AND T0.WhsCode LIKE @whsCode
    AND T3.U_SCE_IN_Industry LIKE @indCode AND T1.CardCode LIKE @custNo

UNION ALL

/*** SAP: Credits ***/

SELECT
T1.DocDate AS 'Document Date'
, T1.DocNum AS 'Document No.'
--, 'Credit' AS 'Doc Type'
, T1.CardCode collate DATABASE_DEFAULT AS 'Cust Code'
, T2.CardName collate DATABASE_DEFAULT AS 'Customer Name'
, T0.ItemCode collate DATABASE_DEFAULT AS 'Item Code'
, T0.Dscription AS 'Item Description'
, T0.Quantity
, T0.WhsCode collate DATABASE_DEFAULT AS 'Warehouse'
, CAST(-1 * T0.LineTotal AS decimal(10,2)) AS 'Line Net'
, CAST(-1 * T0.StockValue AS decimal(10,2)) AS 'Line Cost'
, CAST(T3.AvgPrice AS decimal(10,2)) AS 'Item Avg Cost'
, CAST(-1 * (T0.LineTotal - T0.StockValue) AS decimal(10,2)) AS 'Profit / Loss'
, CASE 
    WHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * -100, 2) AS decimal(10,2)) AS nvarchar) = '0.00' THEN 'N/A'
    WHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * -100, 2) AS decimal(10,2)) AS nvarchar) IS NULL THEN 'N/A'
    ELSE CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * -100, 2) AS decimal(10,2)) AS nvarchar) END
    AS 'Profit / Loss %'
, T4.CardName AS 'Vendor Name'
, T5.SlpName AS 'Salesman Name'

FROM AU.dbo.RIN1 T0
INNER JOIN AU.dbo.ORIN T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN AU.dbo.OCRD T2 ON T2.CardCode = T1.CardCode
INNER JOIN AU.dbo.OITM T3 ON T3.ItemCode = T0.ItemCode
INNER JOIN AU.dbo.OCRD T4 ON T4.CardCode = T3.CardCode
INNER JOIN AU.dbo.OSLP T5 ON T5.SlpCode = T1.SlpCode

WHERE T1.DocType = 'I' 
    AND (T0.ItemCode >= @begItemCode AND T0.ItemCode <= @endItemCode)
    AND T0.WhsCode LIKE @whsCode
    AND T3.U_SCE_IN_Industry LIKE @indCode AND T1.CardCode LIKE @custNo

UNION ALL

/*** CUBE: Invoices & Credits ***/

SELECT
T1.DocDate collate DATABASE_DEFAULT AS 'Document Date'
, T1.DocNum AS 'Document No.'
--, 'Cube' AS 'Doc Type'
, T1.CardCode collate DATABASE_DEFAULT AS 'Cust Code'
, T2.CardName collate DATABASE_DEFAULT AS 'Customer Name'
, T0.ItemCode collate DATABASE_DEFAULT AS 'Item Code'
, ISNULL(T3.ItemName, '') AS 'Item Description'
, T0.Quantity
, T0.WhsCode collate DATABASE_DEFAULT AS 'Warehouse'
, CAST(T0.LineTotal AS decimal(10,2)) AS 'Line Net'
, CAST(T0.StockValue AS decimal(10,2)) AS 'Line Cost'
, CAST(T3.AvgPrice AS decimal(10,2)) AS 'Item Avg Cost'
, CAST((T0.LineTotal - T0.StockValue) AS decimal(10,2)) AS 'Profit / Loss'
, CASE 
    WHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS nvarchar) = '0.00' THEN 'N/A'
    WHEN CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS nvarchar) IS NULL THEN 'N/A'
    ELSE CAST(CAST(ROUND(((T0.LineTotal - T0.StockValue) / NULLIF(T0.StockValue, 0)) * 100, 2) AS decimal(10,2)) AS nvarchar) END
    AS 'Profit / Loss %'
, ISNULL(T4.CardName, '') AS 'Vendor Name'
, 'No record - Cube data!'

FROM SCE.dbo.AU_SALES_R T0
INNER JOIN SCE.dbo.AU_SALES_H T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN AU.dbo.OCRD T2 ON T2.CardCode = T1.CardCode collate SQL_Latin1_General_CP850_CI_AS
LEFT JOIN AU.dbo.OITM T3 ON T3.ItemCode = T0.ItemCode collate SQL_Latin1_General_CP850_CI_AS
LEFT JOIN AU.dbo.OCRD T4 ON T4.CardCode = T3.CardCode

WHERE (T0.ItemCode >= @begItemCode AND T0.ItemCode <= @endItemCode)
    AND T0.WhsCode LIKE @whsCode
    AND T3.U_SCE_IN_Industry LIKE @indCode 
    AND T1.CardCode LIKE @custNo

--ORDER BY [Document Date]

If I run the query above it typically takes 17 seconds to return a result set consisting of over 500K records.

However as soon as I un-comment the ORDER BY clause I do not see any results returned!!

I am linking from a SAP database to a custom database, and have had to include COLLATION arguments, and it has occurred to me that perhaps the 'Document Date' is in a slightly different format between the databases, however I do not appear to be able to apply the
'collate DATABASE_DEFAULT' command to the 'Document Date'. Doing so simply produces the error below -

If anybody has any ideas of why my ORDER BY clause is causing this challenge please let me know.

Kind Regards,

David

[Former Member · Accepted solution]

Hi David,

Using date to sort query result may only be workable if you have a small table or that column is indexed.

In your case, try to order by document number to see if the performance is acceptable. Although it is not as good as date column, it is still better than no particular order.

Thanks,

Gordon

[Former Member · Accepted solution]

Hello Gordon,

Thank you for your contribution. I have added an Index to the relevant table in the Custom Database.

Doing so dramatically improved query performance and I am now able to ORDER BY document date.

Kind Regards,

David

[Former Member]

Hi

try to use

ORDER BY 1

shachar

[Former Member]

Hi Saado,

Thank you for the suggestion, however performance did not improve when taking this approach.

Kind Regards,

David

[Former Member]

can you post your query without the line numbers?

[Johan_H]

Hi David,

Are you allowed to add functions and/or stored procedures to your custom database ?

Regards,

Johan

[Former Member]

Yes, adding functions / stored procedures is probably ok.

[Johan_H]

Hi David,

You could create a scalar function that takes a DATETIME as parameter, and that returns a numeric value:

/*input parameter */

@d AS DATETIME

/* return value integer */

RETURN floor(cast(@d as float))

or

/* return value float */

RETURN cast(@d as float)

You can then use this function in your SORT BY clause

Regards,

Johan