Do calculations in C# or Stored Procedure?

[fredrik.ryde]

Hi,

I have a database which contains alot of finacial data. I want to
retreive some data, run som calculations with it, nothing complex just
simple arithmetic.

I wonder if it's faster to let a stored procedure to do this or is
better (faster) to just retrive the data in a simple SELECT statement
and let the application do the calculation?

Regards
Fredrik

 

[Cor Ligthert [MVP]]

Frederik,

It depends what result you want to be get by the client. Is it one value,
than I would use the SP. However if you need as well the underlying data,
than of course the C# program on the client side.

Be aware that a stored procedure has almost forever on Microsoft database to
be compiled first, so will be forever much slower than your C# solution.

I hope this helps,

Cor

 

[Cor Ligthert [MVP]]

Vadym,

If you need 100K rows which should be printed on the Client, than in my idea
I would do the calculations on the Client.

Advantage as I wrote the calculation in C# is much quicker plus that the
client is seldom a computer with much less performance than a server. Often
the client computers have quicker processors.

The same as I wrote that if only the resulting value is needed than you I
would do it on the Server.

But this is not to be given in a simple answer as, "it is quicker using a
Stored Procedure", that is depending on the way you use the data.

Cor

 

[John A. Bailo]

The question is: Do you want to load your database server with
calculations?

Or do you want it to focus on serving up data and perform those
calculations on the middleware or client.

 

[Cor Ligthert [MVP]]

Agree

No because a server can be used by thousand of users in the same time.

The Client -> Server idea is to bring as much processing on the Client
computer as far away as possible from the Server. (The advantages are not
only processing time, by instance that it is better to let a client go down,
because of an unexpected problem than a server which is serving thousand
clients).

I am sure that there are on many places clients which are much faster than
the servers. Servers have in my idea often a longer live time. Therefore
there will be on many places Lan's where the server is still served by Xeon
or older processors, which have not such more (mostly less) processing speed
as the Pentium. Especially the newer Pentiums.

On Servers with 32bits processors, and I am not sure however think that
these are still the most used the maximum usable memory is 4Gb.

Most modern clients have minium 256 while 512 and 1Gb is not unusable.

But your statement about memory. Memory does not affect speed, that does the
channels and the processor. Memory can only affect speed if there is to
less. Something that is almost unthinkable on modern clients.

Cor

 

[Jon Skeet [C# MVP]]

CLM> No because a server can be used by thousand of users in the same time.

With large data amounts being sent to clients with db server, clients
can face significant latency and that will be not the fault of the
server.

That's the nub of it, for me. Suppose you have pieces of data X and Y,
and wish to compute Z from them. If you *only* need Z at the client
(and not X and Y) then you should do the operation on the server.

If, however, you already need X and Y on the client, then it's often a
better idea to do the ocmputation on the client too. (You actually need
less data transfer this way.)

If you only need X and Z on the client, then it partly depends on the
size of Y compared with Z.

Now, that's talking in terms of data size - obviously it's more
complicated in different situations. For example, in a project I've
worked on we did *some* normalisation on the client side, because we
were normalising against just a few possible values. For values where
we couldn't possibly cache everything on the client, we did
normalisation on the database side.

In short - there are very few simple answers when it comes to this kind
of thing. You need to know an awful lot more information than we've
been provided.

 

[Cor Ligthert [MVP]]

Vadym,

P.S. As experiment you can insert 100K rows and calculate the sum by some
criterion.
Do it on SQL and return result to client and do the calculations by
client.

Why should I, I have written al the way that in this case I would calculate
it on the server.

I am all the time talking that the clients process needs the underlying data
and not only the result of calcculation with that.

Than C# will outclass the SP completely, especially if the calculation is a
complex process.

Cor

 

[Vadym Stetsyak]

Than C# will outclass the SP completely, especially if the calculation is

a complex process.

Yes, I agree.

 

[RoughRyde]

Thanks alot for your help!

What I want to do is to retrive around 10000-20000 rows that very
simplified look look like this:

Company Year Revenue Costs
======= ==== ====== =====
Google 2004 $1 000 0000 $800 000

And for each row calculate the Profit (Revenue - Costs)

Will this still imply that I should use C# as the majority have
suggested?

Fred

 

[Cor Ligthert [MVP]]

Frederik,

You even don't need C# for this, you can add a column to your Datatable with
an expression.
(Maybe you call that C# but it is an ADONET part).

http://msdn.microsoft.com/library/d.../html/frlrfSystemDataDataColumnClassTopic.asp

I hope this helps,

Cor


..

 

[Kevin Spencer]

This is actually the sort of thing best handled by the Stored Procedure that
creates the Record Set. After all, the calculation is on a per-row basis,
and adding the data to some sort of structure afterwards is going to be more
costly overall. The SP simply adds another column to the result set.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

 

[Jon Skeet [C# MVP]]

This is actually the sort of thing best handled by the Stored Procedure that
creates the Record Set. After all, the calculation is on a per-row basis,
and adding the data to some sort of structure afterwards is going to be more
costly overall. The SP simply adds another column to the result set.

I don't think adding it in the SP is the right way to do things:

1) It adds overhead at the server end which is shared, instead of the
client end which probably has cycles to spare (in a typical situation).

2) It *adds* to the network traffic, as there's one more column to
transfer. The point of doing calculations in a SP is usually to
*reduce* the amount of data transferred. When all the data required for
the calculation is already required on the client anyway, why add
redundant traffic?

Calculated data columns in the DataTable (or whatever) are the correct
solution to this, IMO. Indeed, that may well mean that fewer
calculations are done anyway. I don't know exactly how they work in
ADO.NET, but it's quite possible that they could be lazily evaluated -
so if you only needed to know the profit for *one* of a million rows,
you'd only end up doing one subtraction. If the SP does it, it'll come
down with every row, so the calculation *has* to be done for each row.

 

[Kevin Spencer]

Well, you have a point there, Jon. Still, it does simplify the code. OTOH,
if using a DataSet with DataTables, a calculated column would probably be a
better idea.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.

 

[Cor Ligthert [MVP]]

Kevin,

The only instruction needed for Roughe in C# is

MyTable.Columns.Add("Profit",Type.GetType("System.Type"),"Revenue - Cost");

How do you want to symplify that with a SP?

Cor

Well, you have a point there, Jon. Still, it does simplify the code. OTOH,
if using a DataSet with DataTables, a calculated column would probably be
a better idea.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.