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

  • 1
    That's insane. How many records? What sql server did you use?
  • 1
    @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
  • 1

    Triggers are evil.

    Most likely you were looking for aggregate / window functions.
  • 0
    @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.
  • 1
    Sounds like recursive or windowing function indeed.

    In case you wanna know more...



    Cube and Roll-up for advanced grouping



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


    Aka "I have a row and need to create aggregates based on this row while maintaining the original row".
  • 1
    @IntrusionCM thank you for the lnks, but they are not helping in a current context.

    Still, interesting read !
  • 0
    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.
Add Comment