Re: basic question

On Mon, 14 Aug 2006 21:31:16 +0200, "Cor Ligthert [MVP]"
<notmyfirstname@xxxxxxxxx> wrote:

"CSharpguy" <CSharpguy@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
news:0DA4358F-D9AF-4DA9-B2C1-2861C9E4BCEA@xxxxxxxxxxxxxxxx
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

Sounds good - minimal data transferred.

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

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

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)

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

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

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

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?


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 :-)



--
Phil
.

Relevant Pages

  • Re: LINQ Queries vs Stored Procs
    ... Stored Procedures, you can limit the user to only executing stored ... most sql injection attacks, which are more possible with dynamic sql. ... Also about the security aspects: it's not as you claim it to be: I can ... I as a user in Marketing have to use your app which uses procs and I ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Security level to run SP_OA... Procedures
    ... I'm not sure why you are executing sp_OA* procs here since you can ... sp_OA* procs as well as other master database stored procedures. ... Configure the SQL Agent proxy account. ...
    (microsoft.public.sqlserver.security)
  • Re: Frage zu View-Tabellen
    ... eigenes Element in SQL. ... Erweiterungen sind indizierte Sichten (SQL Server 2000), ... Performance und vom Speicherverbrauch ein Unterschied, ob ich eine Anfrage auf eine View-Tabelle oder eine Stored Procedure schicke? ... Bei Stored Procedures wird der kompilierte Ausführungsplan im Cache gespeichert, der bei Zugriffen auf die View in der Regel neu erstellt wird. ...
    (microsoft.public.de.sqlserver)
  • Re: Critique my LINQ to SQL strategy using stored procs, please
    ... LINQ to SQL seems like a good option for our data access needs, ... All business logic will reside in the stored procs (company ... Using LINQ to SQL, create a single LINQ to SQL dbml ... The power is the use of LINQ to work with the virtual database, which using all stored procedures defeats the purpose of using LINQ-To-SQL. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Stored Procedure vs direct execute SQL
    ... another of dynamic sql (otherwise SQL Server can't take advantage of ... You seem to be saying that SQL Server can use ... To say that stored procedures are "far ... > select listingId, listingName from Property ...
    (microsoft.public.dotnet.framework.aspnet)
Loading