CODEWITHSUNDEEP
sqlsql-server-2008t-sqlstored-procedures
mbigun
mbigun

Reputation: 1302

How to optimize SQL query to achieve the minimum execution time

I have a simple table 'TABLE_1'

Org   Customer   Code   Ordered   Deleted   Confirmed

RU     Cust_1      A      1000       800        200 
RU     Cust_2      B      300        0          300
US     Cust_3      C      800        100        700
RU     Cust_4      B      100        100        0
US     Cust_5      C      400        200        200 
RU     Cust_6      B      500        300        200

Now I need to transform this table for those rows, where 'Deleted'<>0 like

Org   Code    Customers          Ordered   Confirmed 

RU     A      Cust_1               1000       200
RU     B      Cust_4, Cust_6       600        200
US     C      Cust_3, Cust_5       1200       900

I'm using following query and function

SELECT T1.Org,
       T1.Code,
       dbo.FUNC(T1.Code, T1.Org) AS 'Customers',
       'Ordered' = (SELECT SUM(Ordered) FROM TABLE_1 AS T2 WHERE T2.Customer = T1.Customer AND T2.Code = T1.Code AND T2.Deleted<>0),
       'Confirmed' = (SELECT SUM(Confirmed) FROM TABLE_1 AS T3 WHERE T3.Customer = T1.Customer AND T3.Code = T1.Code AND T3.Deleted<>0)
FROM TABLE_1 AS T1 
WHERE T1.Deleted <> 0

Function 'FUNC':

ALTER FUNCTION [dbo].[FUNC] (@c VARCHAR(MAX), @org VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + T1.Customer + ', '
FROM TABLE_1 AS T1
WHERE T1.Code = @c AND T1.Org = @org AND T1.Deleted <> 0
GROUP BY T1.Customer
RETURN SUBSTRING(@p, 1, LEN(@p) - 1)
END

I think this is not the best way how to get result, especially if I have a large table. Is there a better solution for this purpose?

EDIT: Table DDL example

CREATE TABLE [dbo].[TABLE_1](
[Org] [nchar](10) NULL,
[Customer] [nchar](100) NULL,
[Code] [nchar](10) NULL,
[Ordered] [decimal](18,1) NULL,
[Deleted] [decimal](18,1) NULL,
[Confirmed] [decimal](18,1) NULL) 
ON [PRIMARY]

Upvotes: 3

Views: 482

Answers (4)

Jodrell
Jodrell

Reputation: 35726

You can do this, which should work, even if your customer names contain XML control chars.

Fiddle Here

SELECT
            t1.[Org],
            t1.[Code],
            STUFF(
                (
                 SELECT
                               ', ' + c.[Customer]
                     FROM
                               [TABLE_1] c
                     WHERE
                               c.[Deleted] <> 0
                         AND
                               c.[Org] = t1.[Org]
                         AND
                               c.[Code] = t1.[Code]
                     ORDER BY
                               c.[Customer]
                     FOR XML PATH (''), TYPE
                 ).value('.', 'varchar(max)'),
                 1,
                 2,
                 '') [Customers],
            SUM(t1.[Ordered]),
            SUM(t1.[Confirmed])
    FROM
            [TABLE_1] t1

    WHERE
            t1.[Deleted] <> 0
    GROUP BY
            t1.[Org],
            t1.[Code];

In terms of performance, it makes sense to just do two queries and worry about presentation as a comma delimited list later. You get the same information but without the overhead of string aggregation which MSSQL is so misplaced to achieve.

Fiddle Here

SELECT
            t1.[Org],
            t1.[Code],
            SUM(t1.[Ordered]),
            SUM(t1.[Confirmed])
    FROM
            [TABLE_1] t1

    WHERE
            t1.[Deleted] <> 0
    GROUP BY
            t1.[Org],
            t1.[Code];

SELECT
            t1.[Org],
            t1.[Code],
            t1.[Customer]
    FROM
            [TABLE_1] t1

    WHERE
            t1.[Deleted] <> 0
    ORDER BY
            t1.[Org],
            t1.[Code],
            t1.[Customer];

Upvotes: 1

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

What is the best might require some testing on your specific data, but for start let's fix your original query to get the correct results you wrote in your question as expected:

SELECT T1.Org,
       T1.Code,
       dbo.FUNC(T1.Code, T1.Org) AS Customers,
       SUM(Ordered) AS Ordered,
       SUM(Confirmed) AS Confirmed
FROM TABLE_1 AS T1 
WHERE T1.Deleted <> 0
GROUP BY T1.Org, T1.Code

Upvotes: 1

valex
valex

Reputation: 24144

SQLFiddle demo

SELECT
   Org,
   Code,
   STUFF(
      (SELECT ','+Customer
       FROM t WHERE Code=a.Code and Deleted<>0
       FOR XML PATH('')) , 1 , 1 , '' ),
   SUM(ordered),
   SUM(Confirmed) 

FROM 
   t A 
where Deleted<>0
group by ORG,code

Upvotes: 1

Serge
Serge

Reputation: 6712

You will be facing a RBAR "problem" wathever you choose to do. Yet, you might find nicer to use FOR XML PATH('') + OUTER APPLY instead of your function.

If you don't know about those, I'll write a piece of code to demonstrate the usage. But could you provide your table DDL first (+some rows).

Here it is:

SELECT
    T1.Org
    , T1.Code
    , ISNULL(STUFF(F.Customers, 1, 2, ''), '')  AS Customers
    , SUM(T1.Ordered) OVER (PARTITION BY T1.Customer, T1.Code) AS Ordered
    , SUM(T1.Confirmed) OVER (PARTITION BY T1.Customer, T1.Code) AS Confirmed
FROM TABLE_1 AS T1
OUTER APPLY (
    SELECT
        ', ' + T2.Customer
    FROM TABLE_1 AS T2
    WHERE T2.Code = T1.Code
    AND T2.Org = T1.Org
    AND T2.Deleted <> 0
    FOR XML PATH('')
) AS F(Customers)
WHERE T1.Deleted <> 0

Upvotes: 2

Related Questions