Thank you very much for your response.
Solomon Rutzky (11/23/2014)
Hi there. What do you consider to be "frequent" inserts? How many rows in this table already, and how many projected to be there in the next year or two? How much load on the system (transactions per second, or even minute)?
Well it would be low load for a day but it might insert in bursts. E.g. 100 rows but if say 5 go in within 100ms, we'd want the triggered process to complete it's task in less than a second ideally. That is from insert into the table, the trigger firing, the executable or CLR proc completing work. In practice, from xp_cmdshell, the executable completion is < 30ms so the bottleneck seems very much to be the mechanism queuing/firing the executable.
Solomon Rutzky (11/23/2014)
CLR is the most direct route, though could be a SQLCLR stored procedure (or function) that is called from a T-SQL trigger. That is often a little more flexible. But still not sure I would recommend it as you are tying an external resource to the transaction (i.e. the INSERT operation). If there is any delay in that external system, then the transaction is on hold, and that could cause blocking on other operations.
Our external call I was thinking could be done async. The system is designed so that if that external process fails, it is retried by a timed mechanism so we don't impact the insert or trigger operation. Not that we expect it to fail but in case it does...
Solomon Rutzky (11/23/2014)
Service Broker does offer a nice separation there that would allow the transaction to complete and queue the message. Then the activation stored procedure kicked off automatically by Service Broker could call the SQLCLR proc or function to do the Web Service call.
Yes it does sound like it ticks a lot of the boxes. Hesitant to go down this path as if this becomes "core" to our solution, it is another component that requires configuration on deployments. Short-term manual config. Hopefully automated in medium to long term. Not sure about the responsiveness of the Service Broker as well...
Solomon Rutzky (11/23/2014)
Or, you can do a custom queue that you just feed into via an INSERT trigger. Then set up a SQL Agent Job to run every 30 seconds (or 1 minute; minimum is every 10 seconds) to pull from the queue. If the job runs over the next ideal time slot, it will wait for the next one in the pattern. So, if it runs every minute starting on the minute, and takes 1 minute and 20 seconds to run (hence now in the second minute), it will wait for the start of minute 3 to start again.
Was a little worried about setting the job queue execution so tight - we would likely have to configure this approach to 10s. Seems quite "busy loop" approach? And is not edge triggered so lower preference to go with this approach. Potentially over 10s of wait time for external process to trigger as well.
Thank you again for your analysis/response. The CLR proc, deployment requirements aside, seems like it would or could be the most performant choice at this stage so we'll prototype and see how it goes.
Thanks,
SC