architecture question

I asked this in the C# forum and got some input from some developers, now
I'm looking for some input from DBA's.
thanks
I'm working on a project and the lead developer wants to put all of the
business logic on the database side. He wants the proc to do all of the
logic and create XML on the fly, parse out data, etc, and then return the
final result to the business layer. I pushed back and said that the business
layer should do all of the logic, just have SQL return the data needed and
then have the code do all of the XML creation, parsing of data, etc. I was
told I was wrong and thats not the best practice.
Now, this app can hit the db by a total of 100-500 users a day, and having
the db do all of the processing can have a performance hit. Then he
mentioned something about using temp table in the database side, do a
select, then insert into the temp table, then do a select on the temp table
and do the business logic. I pushed back on that as well..
Now, I'm not a DBA but a developer and I've seen DB's take performance hits
when doing logic such as this on the database side, so my question is, which
would be the best solution for this?Hi
"Jon" wrote:
> I asked this in the C# forum and got some input from some developers, now
> I'm looking for some input from DBA's.
> thanks
> I'm working on a project and the lead developer wants to put all of the
> business logic on the database side. He wants the proc to do all of the
> logic and create XML on the fly, parse out data, etc, and then return the
> final result to the business layer. I pushed back and said that the business
> layer should do all of the logic, just have SQL return the data needed and
> then have the code do all of the XML creation, parsing of data, etc. I was
> told I was wrong and thats not the best practice.
> Now, this app can hit the db by a total of 100-500 users a day, and having
> the db do all of the processing can have a performance hit. Then he
> mentioned something about using temp table in the database side, do a
> select, then insert into the temp table, then do a select on the temp table
> and do the business logic. I pushed back on that as well..
> Now, I'm not a DBA but a developer and I've seen DB's take performance hits
> when doing logic such as this on the database side, so my question is, which
> would be the best solution for this?
>
You may want to look at http://msdn2.microsoft.com/en-us/library/ms973279.aspx
With client-server applications you can put business logic into the
database, but implementing a n-tier architecture you are moving away from
this and the business logic resides in one of the tiers.
As far as XML is concerned SQL Server can create and store XML data, you
would need to determine if the interface would work be better as a recordset
or XML and how much load the different methods would create.
John