June 6, 2016 at 10:58 am
hi,
CREATE TABLE [dbo].[T2](
[A1] [bigint] NULL,
[D1] [bigint] NULL
) ON [PRIMARY]
SELECT d1,COUNT(a1) a FROM t2
GROUP BY d1
yours sincelrey
June 6, 2016 at 11:08 am
rajemessage 14195 (6/6/2016)
hi,CREATE TABLE [dbo].[T2](
[A1] [bigint] NULL,
[D1] [bigint] NULL
) ON [PRIMARY]
SELECT d1,COUNT(a1) a FROM t2
GROUP BY d1
yours sincelrey
not sure what your question is?
heres some easy code for us all to play with
CREATE TABLE #T2
([A1] [bigint] NULL,
[D1] [bigint] NULL
)
INSERT #T2([A1], [D1]) VALUES (1, 3)
INSERT #T2([A1], [D1]) VALUES (5, 3)
INSERT #T2([A1], [D1]) VALUES (6, 3)
INSERT #T2([A1], [D1]) VALUES (9, 8)
INSERT #T2([A1], [D1]) VALUES (7, 8)
INSERT #T2([A1], [D1]) VALUES (5, 12)
SELECT d1,
COUNT(a1) a
FROM #t2
GROUP BY d1;
DROP TABLE #T2
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 6, 2016 at 11:13 am
I suspect that the question is: "Why does the Execution Plan for this query contain a 'sort' when there is no ORDER BY clause?"
If so, the answer is along the lines of the database engine needing to sort the data to be able to do the grouping.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 6, 2016 at 11:14 am
There are two ways SQL Server can implement grouped aggregations (stream and hash).
They have different performance characteristics and each work well in certain situations, but the relevant difference here is that the stream aggregate requires sorted data.
If the data is not already sorted (for example, by a suitable index), then it will have to sort the data to use a stream aggregate.
The optimizer will tend to choose a stream aggregate for smaller number of rows/groups, where the cost of sorting is likely outweighed by the stream aggregate's other benefits, and also of course for data that can be fetched already in sorted order.
To answer your question directly, you essentially have these options:
1) Create an appropriate index so that the data is suitably sorted prior to aggregation
2) Use a query hint to force a hash aggregate.
Before doing anything, though, you need to figure out if the mere fact that there's a sort operator in your plan is actually causing a performance problem.
If it's not, then there's not necessarily a reason to do anything.
How long does the query take to run? How often does it run?
Cheers!
June 6, 2016 at 11:17 am
Jacob's answer is clear.
To illustrate his point about appropriate indexes, try creating this and then running the query again:
CREATE INDEX x ON #T2(D1) INCLUDE(A1);
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply