You could use JMS. For each report define a route from timer/quartz to a
JMS queue - perhaps setting the query or the report name as the body.
You could have a single report execution route reading from the JMS queue,
and then executing the report defined in the message.
Use concurrentConsumers to control concurrency. You probably need to
specify a prefetchPolicy for the JMS consumer to ensure reports *are* run
in parallel.
Paul
On Tue, 20 Jul 2021 at 10:29, Tomasz Melcer <tmel...@datax.pl.invalid>
wrote:
> Hi!
>
> We've got a Camel instance that handles, among other things, reporting
> from SQL. We've got tens of different SQL routes, each of which uses an
> SQL consumer like:
>
> sql://SELECT foo, bar FROM
> baz?scheduler=spring&scheduler.cron=0+0+9+*+*+*&…
>
> Each route queries a different database view, but all of the reports
> were initially scheduled either at 9am or at midnight. As we were adding
> more and more reports, we've noticed that database load started
> increasing to the point where other database clients were failing. As a
> short-term remedy, we've spread out the reports a bit, so that each
> starts at a different hour. This requires us to manually tune each
> reports' time.
>
> Is it possible to have some kind of bounded concurrency on these
> consumers, so that we wouldn't have to manually manage the times? What I
> imagine is doing sth like: within routes X₁, …, X₄₄, there can be at
> most 5 consumers working at a time regardless of what time they were
> scheduled to run at; while routes Y₁, …, Y₃₇ are unaffected by the limit.
>
> I initially thought that I could probably use the Scheduler component like:
>
> from("scheduler:reports?poolSize=5");
>
> from("sql://SELECT * FROM X₇?scheduler=#reports")…
>
> but I'm not sure whether this will work, or whether each query can still
> have its own schedule.
>
> An in-process solution would be enough for us, these reports (and a
> bunch of other routes) all work within a single JVM service.
>
> Thanks for any ideas!
>
>
> --
> Tomasz Melcer
>
