Solved

sql query tuning and GROUP BY

Posted on 2015-02-06
15
73 Views
Last Modified: 2015-02-25
I have a pretty big query with unions. Can some help me improve the query. Also I can not group the output by the alias name.

SELECT
			U.lastname+', '+U.firstname as PurchaserName,
			LEFT(ProductList, LEN(ProductList) - 1) AS ProductList,
			(O.subtotal+O.shipTotal) as ListedPrice,
			(O.subtotal+O.shipTotal-isNull(O.promoCodeAmount,0)) as paidAmount,
			isNUll(O.promoCodeAmount,0) as discount,
			isNull(O.referralIssueAmount,0) as refferalDiscount,
			TR.amount as PaidAmount2,
			(SELECT NewCode  from PromoCodeTracker PCT where PCT.createdBy = PCTU.UsedBy and PCT.transID = TR.transKey) as codeGenerated,
			t.reffererName,
			O.promoCode,
			(O.subtotal+O.shipTotal-isNull(O.promoCodeAmount,0)-isNull(O.referralIssueAmount,0)) as totalPaid,
			'Referral Code' as Type,
			(
				SELECT   top 1 v.locationName
				FROM     viewSessions AS V WITH (NOLOCK)
				WHERE    O.orderKey = V.orderKey
				--HAVING COUNT(v.locationName) = 1

			) locationList,
			PCTU.UsedWhen as redeemedOn
		FROM PromoCodeTrackerUse PCTU
		INNER JOIN trans TR on PCTU.TransID= TR.transKey and TR.validated='Y'
		INNER JOIN ORDERS O on O.orderKey= TR.orderKey
		INNER JOIN USERS U on PCTU.UsedBy=U.userKey
		CROSS APPLY (
				SELECT   LI2.description + '|'
				FROM     lineitems AS LI2 WITH (NOLOCK)
				WHERE    O.orderKey = LI2.orderKey
				ORDER BY LI2.description
				FOR      XML PATH ('')
				) AS Cross1(ProductList)

		CROSS APPLY(
			SELECT PCT.createdBy as RefferdBy,U.lastname+', '+U.firstname as reffererName,U.userKey as gavecodeID
			FROM PromoCodeTracker PCT
			INNER JOIN Users U on U.userKey=PCT.createdBy
			where PCT.promoCodeTrackerID = PCTU.promoCodeTrackerID
			
				and PCT.CodeID=1 

		)t

		where 1=1

		


		UNION
	

			
		SELECT
			U.lastname+', '+U.firstname as PurchaserName,
			LEFT(ProductList, LEN(ProductList) - 1) AS ProductList,
			(O.subtotal+O.shipTotal) as ListedPrice,
			(O.subtotal+O.shipTotal-isNull(O.promoCodeAmount,0)) as paidAmount,
			isNUll(O.promoCodeAmount,0) as discount,
			isNull(O.referralIssueAmount,0) as refferalDiscount,
			TR.amount as PaidAmount2,
			PCT.NewCode as codeGenerated,
			'' as reffererName,
			O.promoCode,
			(O.subtotal+O.shipTotal-isNull(O.promoCodeAmount,0)-isNull(O.referralIssueAmount,0)) as totalPaid,
			'Referral Code' as Type,
			(
				SELECT   top 1 v.locationName
				FROM     viewSessions AS V WITH (NOLOCK)
				WHERE    O.orderKey = V.orderKey
				--HAVING COUNT(v.locationName) = 1

			) locationList,
			PCT.discountIssueDate as redeemedOn
		FROM PromoCodeTracker PCT
		INNER JOIN trans TR on PCT.TransID= TR.transKey and TR.validated='Y'
		INNER JOIN ORDERS O on O.orderKey= TR.orderKey
		INNER JOIN USERS U on PCT.createdBy=U.userKey
		CROSS APPLY (
				SELECT   LI2.description + '|'
				FROM     lineitems AS LI2 WITH (NOLOCK)
				WHERE    O.orderKey = LI2.orderKey
				ORDER BY LI2.description
				FOR      XML PATH ('')
				) AS Cross1(ProductList)

		WHERE 1=1
		
			AND PCT.CodeID = 1
			UNION
				
			SELECT
				U.lastname+', '+U.firstname as PurchaserName,
				LEFT(ProductList, LEN(ProductList) - 1) AS ProductList,
				(O.subtotal+O.shipTotal) as ListedPrice,
				(O.subtotal+O.shipTotal-isNull(O.promoCodeAmount,0)) as paidAmount,
				isNUll(O.promoCodeAmount,0) as discount,
				isNull(O.referralIssueAmount,0) as refferalDiscount,
				TR.amount as PaidAmount2,
				'' as codeGenerated,
				'' as reffererName,
				O.promoCode,
				(O.subtotal+O.shipTotal-isNull(O.promoCodeAmount,0)-isNull(O.referralIssueAmount,0)) as totalPaid,
				'Promo Code' as Type,
				(
				SELECT   top 1 v.locationName
				FROM     viewSessions AS V WITH (NOLOCK)
				WHERE    O.orderKey = V.orderKey
				--HAVING COUNT(v.locationName) = 1

			) locationList,
				PCU.UsedWhen as redeemedOn
			FROM PromoCodeUsage PCU
			INNER JOIN trans TR on PCU.TransID= TR.transKey and TR.validated='Y'
			INNER JOIN ORDERS O on O.orderKey= TR.orderKey
			INNER JOIN USERS U on PCU.UsedBy=U.userKey
			CROSS APPLY (
					SELECT   LI2.description + '|'
					FROM     lineitems AS LI2 WITH (NOLOCK)
					WHERE    O.orderKey = LI2.orderKey
					ORDER BY LI2.description
					FOR      XML PATH ('')
					) AS Cross1(ProductList)



			WHERE 1=1
			
				AND PCU.CodeID = 1
				
				GROUP BY PurchaserName

Open in new window

0
Comment
Question by:erikTsomik
  • 5
  • 5
  • 5
15 Comments
 
LVL 19

Author Comment

by:erikTsomik
ID: 40594866
What I trying to do is create a record set of all people who had issue the referral code , people who used the referral code and people who just happened to use the discount code.

The discount code is straight forward. Is the referral that give an issue.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40594869
This is the order a query is processed:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

So, therefore, the alias in Select will be available long after group by is processed.

I understand your SQL is in the following order:

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

But, T-SQL has a Logical order shown first.

Mike


Change

Group By PurchaserName

to

Group By  U.lastname+', '+U.firstname

Post has been revised...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40594927
>> "I can not group the output by the alias name"
as eghtebas has informed you, don't use the alias because of the sequence.
(That sequence is not unique to TSQL by the way)

BUT there are other issues.

In TSQL (and most flavours of SQL) you MUST specify ALL non-aggregating columns in the GROUP BY clause, so you can't just ask to group by that single column.

Then, on top of that, there is no point in asking for a GROUP BY because you are using UNION. UNION will remove any rows that repeated, so there is no point in using GROUP BY.

e.g. all 3 of the following queries produce the same output

select 'one' as x UNION select 'one' UNION select 'one'

      x
      ------
      one

select x from (select 'one' as x UNION select 'one' UNION select 'one') t GROUP BY x

      x
      ------
      one

select x from (select 'one' as x UNION ALL select 'one' UNION ALL select 'one') t GROUP BY x

      x
      ------
      one
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40595042
How would I rewrite the query to make it better. I know union are not the best option
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40595377
That is very hard to answer without the table indices and data to trial/test against.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40595577
erikTsomik,

There are two aspects to a query:

Logical: Correctness
Physical: Performance

We can work together to address the logical aspect of it and possibly somewhat affect the physical part of it by the way we design the logical part.

So to focus on the logical part first, please apply the comments made so far and get it working. Then comes the performance issues to be handled later using SSMS tools.

Here is the summary of the points made above:
- Don't use the aliases defined in SELECT elsewhere.
- You MUST specify ALL non-aggregating columns in the GROUP BY clause.
  (for the fields in the starting SELECT, either use aggregate functions to force them to return a single result per grouped columns listed in the GROUP BY clause.)
- The "SELECT   top 1..." aliased as locationList will be okay (I think) because it returns returns a single value.
- Read and apply the rest of Paul's notes on union.  

Mike
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40595613
I do not use aliases elsewhere only in a select statement. and I do not have any aggregate functions in the select. Correct me if I am wrong
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40595625
re:> I do not use aliases elsewhere only in a select statement.
GROUP BY PurchaserName   <-- this is wrong, alias defined in SELECT and used in GROUP BY

GROUP BY U.lastname+', '+U.firstname <-- this is correct

(note U is alias defined in FROM which has been executed first thus available for GROUP BY. But because PurchaserName will be executed after GROUP BY, therefore it is not ready for use here.)

re:>I do not have any aggregate functions in the select
SELECT f1, f2, SUM(f3) FROM TABLE1
GROUP BY f1, f2

When you use GROUP BY, you need to list all your fields in GROUP BY unless some aggregate function applied to them. Here because we have used SUM(f3), f3 does not need to be listed in GROUP BY.

You need to include all your fields in  GROUP BY unless they are handled by a  aggregate function.
--------------
More on the use of alias:
SELECT f1 as A, f2, SUM(f3), (A/2) as B FROM TABLE1

Even using alias A to calculate B is not allowed in T-SQL and will produce:
Error Message: Msg 207, Level 16, State 1, Line 1
Invalid column name 'A'

I think, in some other database like MS Access, the use of aliases defined and used in the same SELECT statement will be okay but not in T-SQL.

Mike

This post has been revise...
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40595641
let me try to explain what I am trying to do. The referrername is the initial purchaser. so should be listed first and all the purchaser name that assosiated with with the referrername, some of the purchasers can be referrer people
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40595643
I have revised my previous post, please read it again. Your last post is addressed there.

If you decide to include, "... ORDER BY clause is the first and only clause that is allowed to refer to column aliases defined in the SELECT clause. That’s because the ORDER BY clause is the only one to be evaluated after the SELECT clause."
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40595720
added the GROUP by statement
GROUP BY U.lastname+', '+U.firstname,ProductList,O.subtotal,O.shipTotal,O.promoCodeAmount,O.referralIssueAmount,TR.amount ,PCTU.UsedBy,TR.transKey,t.reffererName,O.promoCode,O.orderKey,PCTU.UsedWhen

but geeting the error message
Msg 4104, Level 16, State 1, Line 133
The multi-part identifier "PCTU.UsedBy" could not be bound.
Msg 4104, Level 16, State 1, Line 133
The multi-part identifier "t.reffererName" could not be bound.
Msg 4104, Level 16, State 1, Line 133
The multi-part identifier "PCTU.UsedWhen" could not be bound.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40596331
If you really need to use GROUP BY on this query, do this:

1. do NOT use "UNION", use "UNION ALL" instead. UNION ALL does NOT attempt to reduce the result to unique rows.
2. "push down" the existing query into a subquery. This can sometimes be called using a "derived table" or might be called a "nested subquery"; but this technique allows you to then use the column aliases.
3. now introduce any aggregations and the required GROUP BY clause

I have not introduced any aggregations below, but I have pushed the existing question down one layer to form a derived table, and here the column aliases will work:
SELECT
      PURCHASERNAME
    , PRODUCTLIST
    , LISTEDPRICE
    , PAIDAMOUNT
    , DISCOUNT
    , REFFERALDISCOUNT
    , PAIDAMOUNT2
    , CODEGENERATED
    , reffererName
    , promoCode
    , TOTALPAID
    , LOCATIONLIST
    , REDEEMEDON
FROM (

            SELECT
                  U.lastname + ', ' + U.firstname AS PURCHASERNAME
                , LEFT(PRODUCTLIST, LEN(PRODUCTLIST) - 1) AS PRODUCTLIST
                , (O.subtotal + O.shipTotal) AS LISTEDPRICE
                , (O.subtotal + O.shipTotal - ISNULL(O.promoCodeAmount, 0)) AS PAIDAMOUNT
                , ISNULL(O.promoCodeAmount, 0) AS DISCOUNT
                , ISNULL(O.referralIssueAmount, 0) AS REFFERALDISCOUNT
                , TR.amount AS PAIDAMOUNT2
                , (
                        SELECT
                              NewCode
                        FROM PromoCodeTracker PCT
                        WHERE PCT.createdBy = PCTU.UsedBy
                              AND PCT.transID = TR.transKey
                  ) AS CODEGENERATED
                , T.reffererName
                , O.promoCode
                , (O.subtotal + O.shipTotal - ISNULL(O.promoCodeAmount, 0) - ISNULL(O.referralIssueAmount, 0)) AS TOTALPAID
                , 'Referral Code' AS TYPE
                , (
                        SELECT TOP 1
                              V.locationName
                        FROM viewSessions AS V WITH (NOLOCK)
                        WHERE O.orderKey = V.orderKey
                  ) LOCATIONLIST
                , PCTU.UsedWhen AS REDEEMEDON
            FROM PromoCodeTrackerUse PCTU
                  INNER JOIN trans TR ON PCTU.TransID = TR.transKey
                  AND TR.validated = 'Y'
                  INNER JOIN ORDERS O ON O.orderKey = TR.orderKey
                  INNER JOIN USERS U ON PCTU.UsedBy = U.userKey
                  CROSS APPLY (
                        SELECT
                              LI2.description + '|'
                        FROM lineitems AS LI2 WITH (NOLOCK)
                        WHERE O.orderKey = LI2.orderKey
                        ORDER BY LI2.description
                        FOR xml PATH ('')
                  ) AS CROSS1 (PRODUCTLIST)
                  CROSS APPLY (
                        SELECT
                              PCT.createdBy AS REFFERDBY
                            , U.lastname + ', ' + U.firstname AS REFFERERNAME
                            , U.userKey AS GAVECODEID
                        FROM PromoCodeTracker PCT
                              INNER JOIN Users U ON U.userKey = PCT.createdBy
                        WHERE PCT.promoCodeTrackerID = PCTU.promoCodeTrackerID
                              AND PCT.CodeID = 1
                  ) T
            WHERE 1 = 1

            UNION ALL

                  SELECT
                        U.lastname + ', ' + U.firstname AS PURCHASERNAME
                      , LEFT(PRODUCTLIST, LEN(PRODUCTLIST) - 1) AS PRODUCTLIST
                      , (O.subtotal + O.shipTotal) AS LISTEDPRICE
                      , (O.subtotal + O.shipTotal - ISNULL(O.promoCodeAmount, 0)) AS PAIDAMOUNT
                      , ISNULL(O.promoCodeAmount, 0) AS DISCOUNT
                      , ISNULL(O.referralIssueAmount, 0) AS REFFERALDISCOUNT
                      , TR.amount AS PAIDAMOUNT2
                      , PCT.NewCode AS CODEGENERATED
                      , '' AS REFFERERNAME
                      , O.promoCode
                      , (O.subtotal + O.shipTotal - ISNULL(O.promoCodeAmount, 0) - ISNULL(O.referralIssueAmount, 0)) AS TOTALPAID
                      , 'Referral Code' AS TYPE
                      , (
                              SELECT TOP 1
                                    V.locationName
                              FROM viewSessions AS V WITH (NOLOCK)
                              WHERE O.orderKey = V.orderKey
                        --HAVING COUNT(v.locationName) = 1

                        ) LOCATIONLIST
                      , PCT.discountIssueDate AS REDEEMEDON
                  FROM PromoCodeTracker PCT
                        INNER JOIN trans TR ON PCT.TransID = TR.transKey
                        AND TR.validated = 'Y'
                        INNER JOIN ORDERS O ON O.orderKey = TR.orderKey
                        INNER JOIN USERS U ON PCT.createdBy = U.userKey
                        CROSS APPLY (
                              SELECT
                                    LI2.description + '|'
                              FROM lineitems AS LI2 WITH (NOLOCK)
                              WHERE O.orderKey = LI2.orderKey
                              ORDER BY LI2.description
                              FOR xml PATH ('')
                        ) AS CROSS1 (PRODUCTLIST)
                  WHERE 1 = 1
                        AND PCT.CodeID = 1

            UNION ALL

                  SELECT
                        U.lastname + ', ' + U.firstname AS PURCHASERNAME
                      , LEFT(PRODUCTLIST, LEN(PRODUCTLIST) - 1) AS PRODUCTLIST
                      , (O.subtotal + O.shipTotal) AS LISTEDPRICE
                      , (O.subtotal + O.shipTotal - ISNULL(O.promoCodeAmount, 0)) AS PAIDAMOUNT
                      , ISNULL(O.promoCodeAmount, 0) AS DISCOUNT
                      , ISNULL(O.referralIssueAmount, 0) AS REFFERALDISCOUNT
                      , TR.amount AS PAIDAMOUNT2
                      , '' AS CODEGENERATED
                      , '' AS REFFERERNAME
                      , O.promoCode
                      , (O.subtotal + O.shipTotal - ISNULL(O.promoCodeAmount, 0) - ISNULL(O.referralIssueAmount, 0)) AS TOTALPAID
                      , 'Promo Code' AS TYPE
                      , (
                              SELECT TOP 1
                                    V.locationName
                              FROM viewSessions AS V WITH (NOLOCK)
                              WHERE O.orderKey = V.orderKey
                        --HAVING COUNT(v.locationName) = 1

                        ) LOCATIONLIST
                      , PCU.UsedWhen AS REDEEMEDON
                  FROM PromoCodeUsage PCU
                        INNER JOIN trans TR ON PCU.TransID = TR.transKey
                        AND TR.validated = 'Y'
                        INNER JOIN ORDERS O ON O.orderKey = TR.orderKey
                        INNER JOIN USERS U ON PCU.UsedBy = U.userKey
                        CROSS APPLY (
                              SELECT
                                    LI2.description + '|'
                              FROM lineitems AS LI2 WITH (NOLOCK)
                              WHERE O.orderKey = LI2.orderKey
                              ORDER BY LI2.description
                              FOR xml PATH ('')
                        ) AS CROSS1 (PRODUCTLIST)
                  WHERE 1 = 1
                        AND PCU.CodeID = 1
      ) AS DERIVED
GROUP BY
      PURCHASERNAME
    , PRODUCTLIST
    , LISTEDPRICE
    , PAIDAMOUNT
    , DISCOUNT
    , REFFERALDISCOUNT
    , PAIDAMOUNT2
    , CODEGENERATED
    , reffererName
    , promoCode
    , TOTALPAID
    , LOCATIONLIST
    , REDEEMEDON
;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40596337
@eghtebas
>>"I think, in some other database like MS Access, the use of aliases defined and used in the same SELECT statement will be okay but not in T-SQL."

It is extremely rare to find products that allow re-use of column aliases established in the select clause, except at the ORDER BY clause. This restriction is NOT exclusive to T-SQL.

The only exception to this I am familiar with is Teradata.

However there are 2 techniques  for forcing SQL into recognizing column aliases (and one IS exclusive to TSQL).

1. Using a 'Derived Table' (or 'nested subquery')

or, you can use a "common table expression" (CTE) to achieve the same effect

2. Using Cross Apply

but this is exclusive to TSQL
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40596358
Hi Paul,

I just tested the following query and it works (alias UP is used to calculate alias DoublUP)

SELECT [Order Details].[Order ID], [Order Details].[Unit Price] AS UP, [UP]*2 AS DoublUP
FROM [Order Details];

This I knew because of many years working with Access.

Mike

Order ID UP	DoublUP
30	$14.00	$28.00
30	$3.50	$7.00
31	$30.00	$60.00
31	$53.00	$106.00
31	$3.50	$7.00
32	$18.00	$36.00
32	$46.00	$92.00
33	$9.20	$18.40
34	$9.20	$18.40
35	$12.75	$25.50
36	$9.65	$19.30
37	$40.00	$80.00
38	$46.00	$92.00
39	$12.75	$25.50
40	$2.99	$5.98
41	$46.00	$92.00
42	$25.00	$50.00
42	$22.00	$44.00
42	$9.20	$18.40
43	$3.50	$7.00
43	$2.99	$5.98
44	$18.00	$36.00
44	$46.00	$92.00
44	$2.99	$5.98
45	$9.65	$19.30
45	$18.40	$36.80
46	$19.50	$39.00
46	$34.80	$69.60
47	$14.00	$28.00
48	$40.00	$80.00
48	$9.20	$18.40
50	$10.00	$20.00
51	$21.35	$42.70
51	$9.65	$19.30
51	$18.40	$36.80
56	$12.75	$25.50
55	$14.00	$28.00
79	$30.00	$60.00
79	$53.00	$106.00
78	$39.00	$78.00
77	$25.00	$50.00
76	$22.00	$44.00
75	$12.75	$25.50
74	$12.75	$25.50
73	$9.65	$19.30
72	$46.00	$92.00
71	$18.40	$36.80
70	$40.00	$80.00
69	$3.50	$7.00
67	$10.00	$20.00
60	$34.80	$69.60
63	$10.00	$20.00
63	$40.00	$80.00
58	$81.00	$162.00
58	$7.00	$14.00
80	$38.00	$76.00
81	$2.99	$5.98
81	$38.00	$76.00

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40596361
Mike, Thanks. Didn't know that.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now