Stored Procedures vs C#

Pho

Pho

Soldato
Joined
18 Oct 2002
Posts
9,334
Location
Derbyshire
I'm currently working on a web project which is a bit of a mess, half the calculations are done in C# and half are done in stored procedures.

For simple calculations the stored procedures are fine, however we have a lot of very complex calculations (lots of joins, exclusions, string concatenation etc) and trying to write them in a SP is tricky when I know it could be done much simpler using C#. Most of these calculation results are passed to Crystal Reports (via C# usually).

Which method ultimately would be the best? I can go either way really but it creates a lot of work no matter how I do it.


Cheers :).
 
If you are passing different parameters to the SP could you not just set a parameter id and then use this in the SP to determine what operations to perform?

I.e if you mass a parameter called id with a value of 1 then the SP will have an IF statement and execute the code block below it. If parameter is 2 then the SP will execute something else.

is this an option?
 
Stored procedures will always be quicker, as SQL can optimise the execution path - so always try and write efficient SQL over efficient other languages
 
Stored procedures will always be quicker, as SQL can optimise the execution path - so always try and write efficient SQL over efficient other languages
Stored procs are fast at getting data out, but databases aren't usually quick at data manipulation. Any heavy work I would certainly get C# to do - you are just going to hurt the performance of other applications that could be using the DB server.
 
I have it drilled into my head that you should always take your calculations out of the app and into the database. While the SPs may look complicated SQL Server (which I assume you are using) it should optimise out any redundancies etc.

Also if you're pushing this out to Crystal, what ever you do don't let Crystal do any 'working out', it's hideously inefficient at anything more than basic work.
 
Thank for the replies. So it seems the consensus is to use stored procedures then? Looking at it a different way.. would I be better off mixing the two? Using SPs to select the data I need, but perform any really tricky calculations in C#?

Crystal is a pain, but it was what whoever programmed this originally used. I have a fairly complex query which retrieves 16,000 rows in under a second, crystal takes about 25 to finish displaying it :o.
 
I wouldn't mix and match, it just makes things harder to manage for you or whoever manages it in future. If a tweak is needed, how do you know where to make the tweak?
 
If you are using SQL server 2005 you can use CLR sprocs (i.e. write them in C# or VB.net). If you are doing lots of data intensive stuff then processing as close to the source as possible is always desirable.
 
I wouldn't mix and match, it just makes things harder to manage for you or whoever manages it in future. If a tweak is needed, how do you know where to make the tweak?

That's partly the issue we have right now to be honest. What we're doing is retrieving figures entered by users, then performing a calculation (bespoke to the type of data it is) on it. I.e. when calculating fruits if it's a banana we use one calculation, if it's an apple we use another. Factoring all of that gets complex.

I know I'm being slightly vague about what we're calculating, but effectively we collect data on the running costs of businesses and create reports from it.

If you are using SQL server 2005 you can use CLR sprocs (i.e. write them in C# or VB.net). If you are doing lots of data intensive stuff then processing as close to the source as possible is always desirable.

I'll have a look into that, thanks. We're using SQL Server 2008.
 
I'm just having a read through them, and they look very interesting for what I need to do. I just tried out a sample project on AdventureWorks and they seem relatively straight forward.

The example on MSDN is fairly pretty complex mind. I like to be broken in gently :p.
 
Back
Top Bottom