I'm just curious why an aggregate query runs so much faster with a
GROUP BY clause than without one.
For example, this query takes almost 10 seconds to run
SELECT MIN(CreatedDate) FROM MyTable WHERE SomeIndexedValue = 1
While this one takes less than a second
SELECT MIN(CreatedDate) FROM MyTable WHERE SomeIndexedValue = 1 GROUP BY CreatedDate
There is only one
CreatedDate in this case, so the grouped query returns the same results as the ungrouped one.
I noticed the execution plans for the two queries are different - The second query uses Parallelism while the first query does not.
Is it normal for SQL server to evaluate an aggregate query differently if it doesn't have a GROUP BY clause? And is there something I can do to improve the performance of the 1st query without using a
GROUP BY clause?
I just learned I can use
OPTION(querytraceon 8649) to set the cost overhead of parallelism to 0, which makes makes the query use some parallelism and reduces the runtime to 2 seconds, although I don't know if there's any downsides to using this query hint.
SELECT MIN(CreatedDate) FROM MyTable WHERE SomeIndexedValue = 1 OPTION(querytraceon 8649)
I'd still prefer a shorter runtime since the query is meant to populate a value upon user selection, so should ideally be instantaneous like the grouped query is. Right now I'm just wrapping my query, but I know that's not really an ideal solution.
SELECT Min(CreatedDate) FROM ( SELECT Min(CreatedDate) as CreatedDate FROM MyTable WITH (NOLOCK) WHERE SomeIndexedValue = 1 GROUP BY CreatedDate ) as T
In response to Martin's request for more info:
SomeIndexedValue have a separate non-unique, non-clustered index on them.
SomeIndexedValue is actually a varchar(7) field, even though it stores a numeric value that points to the PK (int) of another table. The relationship between the two tables is not defined in the database. I am not supposed to change the database at all, and can only write queries that query data.
MyTable contains over 3 million records, and each record is assigned a group it belongs to (
SomeIndexedValue). The groups can be anywhere from 1 to 200,000 records