In an ASP.NET application, I have a small number of fairly complex, frequently used operations to execute against a database. In these operations, one or more of several tables needs updates or inserts based a logical evaluation of both input parameters and values of certain tables. I've maintained a separation of logic and data access, so the operation currently looks like this:
As you can see, the client is kept waiting while two separate requests are made to the database. In searching for a solution to this, I've found CLR Stored Procedures, but I'm not sure if I have the right idea about what they are useful for.
I have written a replacement for the code above which especially places steps 2-4 in a CLR SP. My understanding is that the SP will be executed locally by SQL Server and result in only one call being made to the server.
My question is basically, is this the intended use of CLR SPs or am I missing something?
I realize this is a bit of a compromise structurally, so if there's a better way to do it I'd love to hear it.
CLR procs are meant to do advanced query processing where it would be really hard for a stored procedure to do. I've ran into very few situations where they were that much more beneficial. They aren't meant for business layer work, though you could do that. I wouldn't embed everything within the CLR proc; separation of layers is very beneficial in an app. If you need to speed up your code, check out memory/code profilers available that can help you identify trouble points in your code.
When possible, replace server-side code with AJAX callbacks, web services, etc, as that would really speed it up (at least allow you to do more asynch work).
HTH.