We have a requirement wherein, we have 5 notification documents to be subscribed per database connection.
We have around 50+ different databases.
So essentially, we have 50*5=250 notification documents to be published.
Each document has only the key field based on which we extract relevant fields from the staging table.
Since this extraction logic is common, a single service can do the work.
We can also have a single trigger subscribe to all 250 documents and call the same service.
But is this approach feasible from performance standpoint?
Coz, we expect 40k+ records from each database in a single day.
So can a single trigger take care of so many documents? also, will the single service have so many individual open sessions to do the processing??
Should we have a separate trigger per database i.e. 50+ triggers and that many no. of services.
But all the service essentially will perform the same action.
Please help with an optimum approach from performance standpoint…