CODEWITHSUNDEEP
sqlsql-server-2008group-byquery-optimization
ariel
ariel

Reputation: 16150

Different Group By Clause on same Query (Select/Where)

Can i reuse a complex select Where clause to different group by?

What i am doing:

(Select X, Count(*) From [Big where clause with many tables] Group By X)
Union All
(Select Y, Count(*) From [Big where clause with many tables] Group By Y)

How can i optmise this?

I'm using SQL Server 2008 and this will go inside a Function, but could be a stored procedure.

Upvotes: 1

Views: 3600

Answers (4)

Andriy M
Andriy M

Reputation: 77667

SQL Server 2008 has introduced GROUPING SETS () which seems to be just what you are after. The UNION solution suggested by others can now easily be replaced with a single select with GROUPING SETS.

Basically, you are using it like this:

SELECT A, B, C
FROM …
WHERE …
GROUP BY GROUPING SETS ( (A), (B), (C) )

which is equivalent to

SELECT A, NULL, NULL, …
FROM …
WHERE …
GROUP BY A

UNION ALL

SELECT NULL, B, NULL, …
FROM …
WHERE …
GROUP BY B

UNION ALL

SELECT NULL, NULL, C, …
FROM …
WHERE …
GROUP BY C

So, your query might look like this:

SELECT X, Y, COUNT(*)
FROM your complex joins and filters
GROUP BY GROUPING SETS ( (X), (Y) )

Or like this:

SELECT
  CASE WHEN X IS NULL THEN 'Y' ELSE 'X' END AS ObjType
  CASE WHEN X IS NULL THEN  Y  ELSE  X  END AS Obj,
  COUNT(*)
FROM your complex joins and filters
GROUP BY GROUPING SETS ( (X), (Y) )

The second one assumes that X cannot be NULL.

References:

Upvotes: 2

Andrew Lazarus
Andrew Lazarus

Reputation: 19320

WITH basequery AS (Select * From [Big where clause with many tables])
SELECT X, COUNT(*) FROM basequery GROUP BY X
UNION ALL
SELECT Y, COUNT(*) FROM basequery GROUP BY Y;

CTEs were made for situations like this.

Upvotes: 5

Roman Asanov
Roman Asanov

Reputation: 297

create view

CREATE VIEW SomeView
AS
    Select X, Y, Count(*) AS C From [Big where clause with many tables]

then use the view:

Select X, C FROM SomeView GROUP BY X

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453243

One option you could evaluate (particularly if there are relatively few x,y groups) would be to materialise the intermediate results yourself into a @table or #temp table then just SELECT from that (a #temp table would be better as you could set up better indexes than the below and it allows the select query that populates it to be parallelised but these are not available in a function)

DECLARE @T TABLE
(
X int,
Y int,
Cnt int,
UNIQUE(X,Y,Cnt), /*Cnt just included to make index covering*/
UNIQUE(Y,X,Cnt)
)

INSERT INTO @T
Select X, Y, Count(*) 
From [Big where clause with many tables] 
Group By X, Y


SELECT X, SUM(Cnt)
FROM @T
GROUP BY X
UNION ALL
SELECT Y, SUM(Cnt)
FROM @T
GROUP BY Y

Upvotes: 2

Related Questions