Using CASE statement in a oracle stored proc

Question

Hi,

I've a .Net 3.5 windows application.One of the modules uses a Oracle stored proc which returns a recordset to the C# client.

I just want to know which one is a better approach among the following two[wrt. code-readability and performance.]:

1.If I write a multiple CASE statement in the stored proc(SP) itself, then the recordset count is about 125.Here, the code of SP looks a bit messy.But on the positive side, the number of rows returned to the client are less.

2.If I dont use the CASE statement, then the recordset count is @375.In this approach, I will move the "case" logic of SP to the C# middler tier...may be using LINQ/delegates... Here, the SP code is cleaner but the number of records returned is high.

So, shall i go with approach 1 or 2?

Thanks for reading.

Answer 1

It depends on the server load and security policy. I prefer 1), but if you need to have a really available sql server you should prefer 2) in order to light the sql server load.

Answer 2

It is bad policy to have a database stored procedure return too much data and then have a middle tier/client-side process winnow out the chaff. Not just because that process will punish the user by taking longer than filtering the result set in the database, but it will unnecessarily consume resources (network bandwidth, app server cycles) which could be used to satisfy other users' activities.

"Here, the SP code is cleaner but the number of records returned is high."

Code hygiene is in the eye of the beholder. I prefer to have program units which are correct in their own right, rather than rely on external processes to make things right. Especially external processes which reside on other architectural tiers.