470,632 Members | 2,040 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

Do calculations in C# or Stored Procedure?

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

Apr 25 '06 #1
13 5746
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
<fr**********@gmail.com> schreef in bericht
news:11**********************@e56g2000cwe.googlegr oups.com...
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

Apr 25 '06 #2
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
"Vadym Stetsyak" <va*****@ukr.net> schreef in bericht
news:%2****************@TK2MSFTNGP03.phx.gbl...
Hello, Cor!

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

Then, you say, that if we have 100K rows and want to get some subset of
them ( perform join ) and calculate some values we write client side app?

It depends, on type of operations. I can hardly imagine implementing joins
or aggregate functions on client side. DB server was designed to do that.

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

It depends on how it is written. Some time ago we got SP that took 2 hours
to complete. After number of optimizations ( removal of cursors and etc ),
stored procedure was completing in about 1-2 minutes with the same amount
of data.

It is desirable that client program use results of computations or data
manipulations and not perform them.

--
Regards, Vadym Stetsyak
www: http://vadmyst.blogspot.com

Apr 25 '06 #3

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.

fr**********@gmail.com wrote:
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

Apr 25 '06 #4
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


Apr 25 '06 #5
Vadym Stetsyak <va*****@ukr.net> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Apr 25 '06 #6
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
Apr 25 '06 #7
> Than C# will outclass the SP completely, especially if the calculation is
a complex process.


Yes, I agree.
--
Regards, Vadym Stetsyak
www: http://vadmyst.blogspot.com
Apr 25 '06 #8
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

Apr 27 '06 #9
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/de...ClassTopic.asp

I hope this helps,

Cor
..
"RoughRyde" <fr**********@gmail.com> schreef in bericht
news:11*********************@e56g2000cwe.googlegro ups.com...
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

Apr 27 '06 #10
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.

"RoughRyde" <fr**********@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...
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

Apr 27 '06 #11
Kevin Spencer <ke***@DIESPAMMERSDIEtakempis.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Apr 27 '06 #12
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.

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
Kevin Spencer <ke***@DIESPAMMERSDIEtakempis.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

Apr 27 '06 #13
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

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> schreef in bericht
news:uz**************@TK2MSFTNGP04.phx.gbl...
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.

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
Kevin Spencer <ke***@DIESPAMMERSDIEtakempis.com> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too


Apr 28 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Rhino | last post: by
8 posts views Thread by Thomasb | last post: by
2 posts views Thread by Dino L. | last post: by
2 posts views Thread by jed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.