I knew I wasn't very good at SQL, but here is a proof.

Need to make a bulk recalculation action. Basiclly precalculate some values in a separate table to speed up acess.

1 day of work : Fully SQL solution with triggers.

Execute for test : 35 minuts !! for

Me : fuck that

Today : 7 lines c# solution (Took me less than 2 hours) . Same database, saame data set : 10 seconds execution.

Well, I guewss I'll never try again fully SQL solutions lol

That's insane. How many records? What sql server did you use?

@retoor So it's Azure SQL. In this case, it's a low Standart tier, 20 DTU.

For a test run here, it was only 125 925 records (Each record depends on multiple factors and needs at least 4 other tables to be calculated).

But honestlly, it's not SQL server fault, it's me not knowing how to implement "merge" in SQL, but knowing how to do it in c#.

So SQL solution was going line by line, and c# uses batch processing

@NoToJavaScript

Triggers are evil.

Most likely you were looking for aggregate / window functions.

@IntrusionCM Maybe !

But I can’t know what I don’t know.

It’s not aggregate for sure.

I have no idea what “window function” is.

Basiclly, let’s say you have a table which says “Between date A and date B, <hours per day with a column for each day of week>”. (And it has like 3 levels, not only for user, it can be for a team, it can be for the whole company)

Very compact storage. Can’t be used in agregations, sums.

So the “precalculated” table is : 1 user, 1 day, 1 amount.

Sounds like recursive or windowing function indeed.

In case you wanna know more...

https://sqlservertutorial.net/sql-s...

https://sqlservertutorial.net/sql-s...

Cube and Roll-up for advanced grouping

CTE

https://sqlservertutorial.net/sql-s...

Aka the "I need to generate a report and then drill the report further down" kind of thing.

https://sqlservertutorial.net/sql-s...

Aka "I have a row and need to create aggregates based on this row while maintaining the original row".

@IntrusionCM thank you for the lnks, but they are not helping in a current context.

Still, interesting read !

One db that I work with has all its entities built by an ORM. Sometimes it feels like putting out a grease fire with water if I ever need to do anything that isn’t sanctioned by the ORM. Most of the integrity checks seem pushed into application logic.