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:
Request received from client
Business layer invokes data layer to retrieve data from
database
Business layer processes result and
determines which operation to
execute
Business layer invokes appropriate
data operation
Response sent to client
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 initial benchmark tests show this is actually orders of magnitude slower than my original code, but I attribute that recompilation of the code I have not worked out yet and/or some flaw in my environment.
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.
