ADO.NET Loop or SQL SERVER SP and Cursor for Complex Processing?

[Guest]

Hi,

Scenario is; while developing a Web Application based on ASP.NET/C# and SQL
Server 2K, usually we need to have a complex processing which requires
multiple looping operations apart from math calculations!
So which is the best approach to encapsulate the Logic in?
1. C# Class which gets the raw data from SQL Server and does the processing
on it?
OR
2. SQL Stored Procedure which does all processing and returns almost ready
to display data to the C# code!

Please provide your feedback based on following parameters!
1. Performance
2. Maintainability
3. Ease of Deployment (Hot Fix etc)/ Server down Time while Deploy!
Any other important parameter that I am missing!
Thanks,
Rupang

 

[David Browne]

Hi,

Scenario is; while developing a Web Application based on ASP.NET/C# and
SQL
Server 2K, usually we need to have a complex processing which requires
multiple looping operations apart from math calculations!
So which is the best approach to encapsulate the Logic in?
1. C# Class which gets the raw data from SQL Server and does the
processing
on it?
OR
2. SQL Stored Procedure which does all processing and returns almost ready
to display data to the C# code!

First, consider using SQL to perform the logic. You say it requires
looping, but if you can find a set-based approach to do it in SQL it will
usually perform much better than cursor loops in either TSQL or C#. But
assuming you need to loop, do it in C#, unless that would require returning
too much data to the client.

Please provide your feedback based on following parameters!
1. Performance

The performance depends on the size of the dataset. Very large datasets can
be iterated more efficiently in TSQL, small datasets are more efficient in
C#.

2. Maintainability

TSQL cursor loops are ugly, verbose and difficult to maintain. Similar
logic implemented in C# using a strongly-typed DataSet is vastly simpler to
write and maintain.

3. Ease of Deployment (Hot Fix etc)/ Server down Time while Deploy!

Similar.

David

 

[Sahil Malik]

My 2 cents ---

Your deciding factors should be
a) How much data
b) How much complexity.

T-SQL sucks as far as logical operations go - which was the thrust behind
..NET CLR stored procedures in Sql 2k5. But T-SQL Rocks in retreiving data.
..NET C# on the other hand has a serious overhead in hitting the database and
querying data out of it, but logical operations on it work great.

So
1. Performance ---- TSQL will be better especially on large data
2. Maintainability ---- C# easier to write code, simpler, TSQL easier to
update (assuming your C# code will go to istanbul turkey).
3, Ease of deployment/hotfix -- TSQL easier, C# tougher especially in a very
very distributed app.

One last factor to consider - how complicated is what you're trying to do -
from a mathematics point of view. i.e.if you are calculating if a certain #
is prime or not - that isn't too much data, but it's a lot of calculation -
that's a perfect candidate to do in C# because the query is not expensive,
but your database server is offloaded with the processing required to do
that job - not to mention the TSQL complexity/ugliness.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik