Best Practice for Handling Large SQL Queries (40+ Seconds) That Run Only a Few Times Per Month?

In my current implementation, I have 8 different update services, but instead of running them all at once, I execute them sequentially through a single scheduled service that runs every 30 seconds.

I use a property (for example, syncCounter as shown in the screenshot) to store the current iteration/state. Each time the timer service executes, it checks the counter value and runs only the corresponding update service:

• Iteration 1 → Run Service 1, update counter to 2, then exit.
• Iteration 2 → Run Service 2, update counter to 3, then exit.
• Iteration 3 → Run Service 3, and so on.

After the last service runs, the counter is reset and the cycle starts again.

This is currently a temporary solution because each individual service takes only around 10 seconds to complete. By spreading the workload across multiple timer executions, I avoid running all 8 updates simultaneously and reduce the impact on the platform.

In the future, I may move more of the processing into SQL Server and let the database handle the updates directly, which could further improve performance. However, for the current workload and update frequency, this approach has been working reliably.

 

This strategy may not be ideal for very long-running operations, but for low-frequency updates with manageable execution times, it can be a practical and simple solution.

switch (me.syncCounter) {

    case 1:
        me.serviceOne();
        logger.info("serviceOne gestartet");
        break;

    case 2:
        me.serviceTwo();
        logger.info("serviceTwo gestartet");
        break;

    case 3:
        me.serviceThree();
        logger.info("serviceThree gestartet");
        break;

    case 4:
        me.serviceFour();
        logger.info("serviceFour gestartet");
        break;

    case 5:
        me.serviceFive();
        logger.info("serviceFive gestartet");
        break;

    case 6:
        me.serviceSix();
        logger.info("serviceSix gestartet");
        break;

    case 7:
        me.serviceSeven();
        logger.info("serviceSeven gestartet");
        break;
}