Alternative to Group by and Having
-
Hi,
I am trying to find customers and their who had made orders more than 10 times from transaction data.
Eg:
My Query;
With CTE as
(
Select CustomerNumber, count(CustomerNumber) as Count
From SalesData
Where departmentid = 1
Group by CustomerNumber
having (Count(CustomerNumber)>10)
)Select *
from SalesData SD
inner join CTE on SD.CustomerNumber = CTE.CustomerNumber
where departmentid = 1.I had Created Primarykey and Clustered Index on OrderID but not CustomerNumber.
Could experts please help me in improvise the Query.
Many Thanks in Advance
-
What's wrong with the one you posted - performance, wrong results, error messages? Will this one not work just as well?
Select CustomerNumber, count(CustomerNumber) as Count
From SalesData
Where departmentid = 1
Group by CustomerNumber
having (Count(CustomerNumber)>10)John
-
What's wrong with group by and having? If you ned to take aggregates per <some column> and filter on those aggregates, group by and having are what you need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - John Mitchell-245523 - Wednesday, June 21, 2017 8:03 AM
Yours returns one row per customer returning customers that have more than 10 sales, the original returns one row per sale for customers who have more than 10 sales.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This is an alternative. It may or may not improve the query (it would definitively not improvise it 🙂 )
With CTE as
(
Select sd.*, count(*) OVER(PARTITION BY CustomerNumber) as Count
From SalesData sd
Where departmentid = 1
)
Select *
from CTE on SD.CustomerNumber = CTE.CustomerNumber
where Count >10; - GilaMonster - Wednesday, June 21, 2017 8:10 AM
It works fine, I am put this entire code in view, when other applications using this view, the performance is quite bad. When I checked execution plan, Sort cost 50% though I didn't using order by in my query. Instead of CTE, I have tried to use Temp table yesterday evening,, the query works absolutely fine, but I can't use temp table in view structure.
-
Do you have the necessary indexes in place on the table to support this query?
John
-
Can you post the execution plan please? Actual plan, not estimated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Thursday, June 22, 2017 4:48 AM
Hi,
I am glad for your reply,
I have created above code using above script,
- Sangeeth878787 - Thursday, June 22, 2017 5:35 AMGilaMonster - Thursday, June 22, 2017 4:48 AM
Hi,
I am glad for your reply,
I have created above code using above script,
Your image is of no use. For people to help you, you'll need to share an actual .sqlplan file. Read more about it here: How to Post Performance Problems - SQLServerCentral
You could also use SQL Sentry to anonymize your plan if needed. Note that you'll have to do more work to share DDL for tables and indexes.
One more thing. A SELECT * query with no filters won't use indexes and would simply read the whole table. There's no optimization available there. - Luis Cazares - Thursday, June 22, 2017 6:28 AMSangeeth878787 - Thursday, June 22, 2017 5:35 AMGilaMonster - Thursday, June 22, 2017 4:48 AM
Hi,
I am glad for your reply,
I have created above code using above script,
Your image is of no use. For people to help you, you'll need to share an actual .sqlplan file. Read more about it here: How to Post Performance Problems - SQLServerCentral
You could also use SQL Sentry to anonymize your plan if needed. Note that you'll have to do more work to share DDL for tables and indexes.
One more thing. A SELECT * query with no filters won't use indexes and would simply read the whole table. There's no optimization available there. -
You've got some large differences between the estimated and actual number of rows for those clustered index scans, suggesting that your statistics might not be up to date. And the fact that you have clustered index scans, especially on the large table, suggests that you're missing an index, possibly on the CustomerNumber column. Finally, it may be be possible to rewrite that query to avoid that self-join and hence having to read twice from the same table.
John
-
Something like this, maybe. Although I'm a bit puzzled how there are two base tables in your execution plan, but only one in the query you posted. If any of the tables are actually views, please post the view definition.
With CTE as
(
Select *, count(CustomerNumber) OVER (PARTITION BY CustomerNumber) AS CustCount
From SalesData
Where departmentid = 1
)
Select *
from CTE
WHERE CustCount > 10John
- John Mitchell-245523 - Thursday, June 22, 2017 7:33 AM
That looks so similar to mine 😛
There are 2 base tables because the OP is querying a view. A view has low possibilities to appear as itself in an Execution plan. - Luis Cazares - Thursday, June 22, 2017 7:55 AM
Almost identical... I didn't copy, I promise!
John
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply