basic question

[Guest]

I'm not sure if this is the correct forum or not, but I have a basic
question. Currently we have are doing calculations via stored procedures and
then returning the results back to the client in either a web page or a
winForm style application. There are times that the stored procedure takes 20
minutes or 3 hours to run. Now, our database person says that all the
calculations needs to be removed from SQL and put in either the web
applications code or the winForms code (based on what is calling it) the db
person also states that SQL is not intended to do any kind of math
(calculations) and they should all be done in the application. I think the
calcs should be done on the db and its going to take 20 mins to 3 hours for
these things to run no matter where the calcs are happening.

So, my question is: Where should the math(calculations) take place? In the
Stored Procedure or the application side?

 

[Kevin Spencer]

Not possible to answer. You haven't said anything about what these
calculations are, or what database you're using.

In general, business logic should be kept out of the database.

--
HTH,

Kevin Spencer
Microsoft MVP
Chicken Salad Surgery

Orange you bland I stopped splaying bananas?

 

[Cor Ligthert [MVP]]

CSarpGuy,

How many servers do you have and how many Clients do you have.

What kind of processors do they have (the memory size is for this question
mostly not so important)

Do you have a DB2 database where the stored procedure is in a build format
or another one where it has forever to be build before running. On your
Clients you are with Net build programs always running in a predefined, for
only calculating probably a little bit less efficient, than machine code
build format.

Maybe can the answers on these give you an answer in real Client Server
processing. That will probably with on Intel like processor based DataBase
Servers not be the same as that from the answer from mainframe ICT people,
which mostly have no other answer than "In was forever done on the mainframe
and the docs say it is the best" . The last can be true if your DataBase
Server is a hug mainframe with very fast non intel or like that based
processors.

Let your database server handle collecting, providing (aggregating) and
storage from data as good as it can. Let the clients do as much possible
handling with the data as possible that can be done on those.

Just my idea about your question.

Cor

 

[Phil]

Sounds good - minimal data transferred.

That's very variable - do you have a locking problem? Or is the
variability to be expected from the input data?

Nonsense. It's phenomenally good for surprisingly complicated
mathematics. You may want to consider getting another "SQL person"

I tend to agree (on the db, not sure about the time...)

Hmmm, it's difficult to say. I assume that one of the reasons these
procs take so long is because they have to process a large amount of
data, in which case *I* would like to have them run on the SQL server
as it means less data being transmitted on the network. SQL server is
also usually able to make very good decisions about accessing the data
in the best way because it maintains the indexes and statistics about
the distribution of values in the columns.

You definitely shouldn't move it out to the web/WinForms code, that
would be dumb, when instead you can move it out into a business object
layer (basically a class library) that is callable from either web or
WinForms. Some people advocate doing this, and it can be useful, but
only if you can find another box (often called an "application
server") to run it on; and bear in mind that you would have to
transfer the data from the SQL box to this application server box.
One of the advantages of moving the logic out of the database is that
people usually find it easier to build flexible business rules into
the logic, though personally I am a data-centric person and find no
problem with doing it all in procs...

Don't jump yet.

Frankly, I would be tempted to start by really understanding what your
procs are doing. Is this a CPU bound or an IO bound problem? Have you
determined where the time is being spent in the procs? I never met a
stored proc I couldn't speed up somehow. Ask your SQL person to do a
query plan analysis and check the indexing - that should keep him
quiet for a bit Assuming this is MS SQL, you may want to take this
to microsoft.public.sqlserver.programming but you need to be more
specific about your problem first.

Good luck! Wish I could do it for you, I love speeding things like
this up, my spider-sense is telling you should be able to get it going
10 times faster

 

[Pritcham]

I agree with the other posts - whether it makes sense to move the
calculations from the DB to the client really depends on what the
calculations are and how much data is needed in order to do the
calculations so with the info you've posted it's difficult to suggest
which may be better in your case.

One resource you may want to check out to help you make the decision
(and I can't recommend this highly enough) is to pop over to the forums
as www.sqlservercentral.com (assuming you're using SQL Server) as the
guys over there really know their stuff (not suggesting that the guys
here don't) and should be able to give you some sound advice (be
prepared to give them more detail on what calculations are being done
if necessary though).

HTH
Martin