Script optimization - Group by

[Antoine]

Hello,

I'm using this code for now, and it works just fine :

[WorkplaceStateHistoryTemp]:
SQL SELECT
"workplace",
"startDate",
"stateType",
"endDate",
"imputationDate" as [Keydate]
FROM "Serv"."dbo"."WorkplaceStateHistory";

[WorkplaceStateHistory]:
Load
[workplace],
Date([Keydate]) as [Keydate],
[stateType],
Sum(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration]
Resident [WorkplaceStateHistoryTemp]
group by workplace, stateType, [Keydate];
Drop table [WorkplaceStateHistoryTemp];

But the loading time is huge, since I'm loading a massive database THEN using it again as a resident table before dropping it.

I found this way of doing the loading that saves me 20% of loading time, but I can't figure put how to use the group by:

[WorkplaceStateHistory]:
Load
[workplace],
imputationDate as [Keydate],
[stateType],
SUM(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration];
Select
"workplace",
"startDate",
"stateType",
"endDate",
"imputationDate"
FROM "Serv"."dbo"."WorkplaceStateHistory"
group by workplace, stateType, imputation;

How am I suppose to do the group by in the second solution ? Is there even a faster way to get what I'm doing ?

[tresesco]

One alternative way you can try using sorting the table in resident load before grouping them, like suggested here:

Optimize-Group-By-Performance 

[Antoine]

It's worth a try, thanks!

But that means there's no way to do a group by without going for a resident table ?

[tresesco]

I guess we are talking about better way, right? You can always use group by in the first load and resident load is not necessary for that.

[Antoine]

Ok, so what's wrong in this script:

[WorkplaceStateHistory]:
Load
[workplace],
imputationDate as [Keydate],
[stateType],
SUM(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration];
Select
"workplace",
"startDate",
"stateType",
"endDate",
"imputationDate"
FROM "Serv"."dbo"."WorkplaceStateHistory"
group by workplace, stateType, imputationDate;

It sends me back an error message: 

Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 8120, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'Serv.dbo.WorkplaceStateHistory.startDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[tresesco · Accepted solution]

Well, I guess I got your point now. Try like:

[WorkplaceStateHistory]:
Load
[workplace],
imputationDate as [Keydate],
[stateType],
SUM(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration]
group by workplace, stateType, imputationDate;
Select
"workplace",
"startDate",
"stateType",
"endDate",
"imputationDate"
FROM "Serv"."dbo"."WorkplaceStateHistory" ;

[Antoine]

Works perfectly!

I'm still struggling on how to order THEN group by, but I will probably get it soon.

Thanks a lot for the help!