Stored Procedures vs C#

Pho · 16 Jan 2009 at 16:22

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

.

ringo747 · 16 Jan 2009 at 18:01

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?

HTMLHugo · 16 Jan 2009 at 18:21

for reports ive always done this as a stored procedure / embedded query in all honesty.

havent used crystal reports thankfully.

Andre · 16 Jan 2009 at 18:36

Stored procedures will always be quicker, as SQL can optimise the execution path - so always try and write efficient SQL over efficient other languages

Goksly · 16 Jan 2009 at 19:58

Andre said:
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.

SiD the Turtle · 16 Jan 2009 at 20:42

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.

Pho · 16 Jan 2009 at 21:09

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

.

SiD the Turtle · 16 Jan 2009 at 21:15

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?

philjohn · 16 Jan 2009 at 21:16

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.

Pho · 16 Jan 2009 at 21:36

SiD the Turtle said:
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.

philjohn said:
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.

philjohn · 16 Jan 2009 at 21:45

I believe it's also supported on 2k8, so you should be golden.

Pho · 16 Jan 2009 at 22:27

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

.