Performance for Avg & Max in SQL

I want to decrease the query execution time for the following query. This query is taking around 1 min 20 secs for about 2k records.

Numbers of records in table: 1348474

Number of records processed through where query: 25000

Number of records returned: 2152

SELECT Col1, Col2,
ISNULL(AVG(Col3),0) AS AvgCol,
ISNULL(MAX(Col3),0) AS MaxCol,
COUNT(*) AS Col5
FROM TableName WITH(NOLOCK)
GROUP BY Col1, Col2
ORDER BY Col1, MaxCol DESC

​x
 
SELECT Col1, Col2,ISNULL(AVG(Col3),0) AS AvgCol,ISNULL(MAX(Col3),0) AS MaxCol,COUNT(*) AS Col5FROM TableName WITH(NOLOCK)GROUP BY Col1, Col2ORDER BY Col1, MaxCol DESC​

I tried removing the AVG & MAX columns and it lowered to 1 sec. Is there any optimized solution for the same? I have no other indexing other than Primary key.

Update

Indexes added: nonclustered located on PRIMARY – Col1

nonclustered located on PRIMARY – Col2

clustered, unique, primary key located on PRIMARY – Id

======

Thanks in advance..Happy coding !!!

Answer

For this query:

SELECT Col1, Col2,
       COALESCE(AVG(Col3), 0) AS AvgCol,
       COALESCE(MAX(Col3), 0) AS MaxCol,
       COUNT(*) AS Col5
FROM TableName
GROUP BY Col1, Col2
ORDER BY Col1, MaxCol DESC;

 
SELECT Col1, Col2,       COALESCE(AVG(Col3), 0) AS AvgCol,       COALESCE(MAX(Col3), 0) AS MaxCol,       COUNT(*) AS Col5FROM TableNameGROUP BY Col1, Col2ORDER BY Col1, MaxCol DESC;​

I would start with an index on (Col1, Col2, Col3).

I’m not sure if this will help. It is possible that the issue is the time for ordering the results.

Advertisement

Answer