CODEWITHSUNDEEP
sqlsql-serverperformancegroup-bylocaldb
Ahmad KFUPM
Ahmad KFUPM

Reputation: 89

SQL Server GROUP BY too slow

I'm trying to run two queries on SQL LocalDB. One of them executes fast and the other takes 12 seconds although the difference is only one column added to the select and group by clauses.

The following query executes in nearly no time:

select  [Company]
    ,sum([Hours]) as [Hours] 
from [Operation View]  
where company_id in (19,53,76,1) 
    and action_id in (5,21,23,30) 
    and [Report Date] >= '2014-04-01' 
group by [Company] 
order by Company

While the below query takes around 12 seconds:

select  [Company]
    ,Department
    ,sum([Hours]) as [Hours]
from [Operation View]  
where company_id in (19,53,76,1) 
    and action_id in (5,21,23,30) 
    and [Report Date] >= '2014-04-01' 
group by [Company]
    ,Department

As you can see, I only added one extra column Department in the select and group by clauses.

If I try to add more columns, the execution time remains at 12 seconds.

Is this a normal behavior? Is there anything I can do to speed up the query?

Note that the [Operation View] is a view that is based on a main table joined by other lookup tables that provides the link between the ID's and the Names of the fields.

I thought about indexing the view but I have a text column which prevented this option.

Upvotes: 2

Views: 15607

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12989

I would suggest you below approaches to solve the performance problem:

  1. Try to create a composite non-clustered index on Company, Department in the base table of the view, to give good performance for GROUP BY.
  2. As you are not able to create index on the view, try to create one more view which is having the columns: Company, Department, company_id, action_id, report_Date, with GROUP BY applied already and it should be possible for you index this view, as it is straightforward view. Just query the view with filters applied.

Upvotes: 0

DRapp
DRapp

Reputation: 48169

Since you can't create an index on the VIEW, I would hope the underlying tables have indexes to optimize the query, such as a compound index of ( company_id, action_id, report_date ).

That said, your group by condition appears based the company name and not the ID. It MIGHT help if the group by was based on the company_ID instead of company -- IF the company names are completely different per their corresponding IDs, such as 

id  company   Dept
1   Comp A    Dept X
2   Comp B    Dept Y
3   Comp C    Dept X

vs

id  company   Dept
1   Comp A    Dept X
2   Comp A    Dept Y
3   Comp A    Dept Z

in the second case, the ID group would not work as there could be many IDs with the same company name you would want grouped together and thus the actual name WOULD be the correct basis for grouping.

Upvotes: 0

Related Questions