471,337 Members | 1,252 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

architecture question

Jon
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?
Jul 6 '07 #1
24 1347
PS
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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?
What does the business layer do with the XML? Is it becoming an object or is
the XML purely data like reporting data?

PS
Jul 6 '07 #2
If your Lead Developer is suggesting this, then I think you need a new
Lead Developer ASAP.

Thanks
Robb Andrew

Jul 6 '07 #3
Jon
The developer is generating his own XML to be returned to the C# web
service. Though he's creating a dataset and returning that from his method
which calls the database, he has the stored procedure generating XML within
the proc and then just using that as his dataset to populate Grid,
listboxes, etc.
so the XML isn't being used for reporting, is just being created and then
being used to populate grid, textboxes, labels, listboxes, etc.

he's creating his own dataset XML instead of using the XML format that is
returned when creating a dataset in .NET, though his dataset XML and the one
that .NET returns actually mirror each other, so why is he creating his own
XML I have no idea

"PS" <ec***********@hotmail.comwrote in message
news:u4**************@TK2MSFTNGP06.phx.gbl...
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>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?

What does the business layer do with the XML? Is it becoming an object or
is the XML purely data like reporting data?

PS

Jul 6 '07 #4
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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?
In addition to what others have replied, I think you have two choices:

1) find another job or, at the very least, ask to be transferred to another
project with a different "lead" (ha! ha!) developer;

2) show this thread to your lead developer and ask for his/her comments.

In fact, I'd probably do both... :-)
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Jul 6 '07 #5
Jon
LOL
the good side of this, is that I'm only helping out so they meet their
deadline. I'm not even truly on the project full time, but they brought me
in on this project to help out and kind of over see it to make sure its
going down the correct path technology wise and architecture wise, and so
far, I think its ready for huge downfall performance wise and after going
through some of the code, I'm seeing some other performance issues, as well
as other things.

"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:e0**************@TK2MSFTNGP02.phx.gbl...
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>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?

In addition to what others have replied, I think you have two choices:

1) find another job or, at the very least, ask to be transferred to
another project with a different "lead" (ha! ha!) developer;

2) show this thread to your lead developer and ask for his/her comments.

In fact, I'd probably do both... :-)
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Jul 6 '07 #6
//he's creating his own dataset XML instead of using the XML format that
is
returned when creating a dataset in .NET, though his dataset XML and the
one
that .NET returns actually mirror each other, so why is he creating his
own
XML I have no idea//

Wow.

Here are some articles:
(mine)
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry
(pay attention to the dataset vs custom collection performance in the 1.1
code)
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry
(microsoft)
http://msdn.microsoft.com/library/de...tml/BOAGag.asp

He is misusing XML. Somebody needs to reel that guy in.
Life is too short dude. Find another job if you can. Get on another
project if you can.
This guy is going to set you back so many years, you won't be able to get a
good 'next' job.

If this guy has any (perceived) authority/power, get away from him. (from
your earlier comment that "his way is the best way, period")


"Jon" <Jo*@community.nospam.comwrote in message
news:OY**************@TK2MSFTNGP04.phx.gbl...
The developer is generating his own XML to be returned to the C# web
service. Though he's creating a dataset and returning that from his method
which calls the database, he has the stored procedure generating XML
within
the proc and then just using that as his dataset to populate Grid,
listboxes, etc.
so the XML isn't being used for reporting, is just being created and then
being used to populate grid, textboxes, labels, listboxes, etc.

he's creating his own dataset XML instead of using the XML format that is
returned when creating a dataset in .NET, though his dataset XML and the
one
that .NET returns actually mirror each other, so why is he creating his
own
XML I have no idea

"PS" <ec***********@hotmail.comwrote in message
news:u4**************@TK2MSFTNGP06.phx.gbl...
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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?
What does the business layer do with the XML? Is it becoming an object
or
is the XML purely data like reporting data?

PS


Jul 6 '07 #7
// In fact, I'd probably do both... :-)//

Amen.


"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:e0**************@TK2MSFTNGP02.phx.gbl...
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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?

In addition to what others have replied, I think you have two choices:

1) find another job or, at the very least, ask to be transferred to
another
project with a different "lead" (ha! ha!) developer;

2) show this thread to your lead developer and ask for his/her comments.

In fact, I'd probably do both... :-)
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Jul 6 '07 #8

"Jon" <Jo*@community.nospam.comwrote in message news:%2****************@TK2MSFTNGP05.phx.gbl...
LOL
the good side of this, is that I'm only helping out so they meet their
deadline. I'm not even truly on the project full time, but they brought me
in on this project to help out and kind of over see it to make sure its
going down the correct path technology wise and architecture wise, and so
far, I think its ready for huge downfall performance wise and after going
through some of the code, I'm seeing some other performance issues, as well
as other things.
You should remove yourself from the project, and let it go down tubes without you, if possible.

Or become a contractor where you can think to yourself, "It's your bed lay in it, as I am just passing through -- just pay me." :)

If you're up in a NG posting about these kind of issues, then you don't need the stress, as it will kill you early. :)


Jul 6 '07 #9
Wow, flame on here.
Generally people who don't listen/admit mistakes/take advice are hard to
work with, and under... I know little compared to some of the flyers around
here, but it's that admission that keeps us current/fresh/growing.

"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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?


Jul 6 '07 #10
I think that pretty much everything said here is applicable, but there
are times when you want the database to handle certain business logic
because the database is better at handling those kinds of operations.

Foreign keys are a good example of this. Say you have a simple table
for a Car, and there is an id on the table which is a foreign key which
points to the Manufacturers table (so you know who manufactured the car).
This column is also non-nullable. While this is definitely a schema issue,
it is reflective of business logic, that all cars have manufacturers.

Also, when you have some other criteria that needs to be met which
requires set operations to be performed, let SQL Server handle that, it's
better than your procedural code at doing so. Also, loading all the data
into another process to perform the set operations manually (outside of SQL
Server) is going to create a great deal of overhead in itself.

Of course, this doesn't mean I think that your consultant is correct (I
think the XML generation and the whole temp table business is garbage, but
admittedly, I don't know the whole story for either side of your
disagreement), but don't let that put you in a place where you don't utilize
a technology fully in what it is good at for the benefit of your
application.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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?


Jul 6 '07 #11
That makes no sense. I would immediately question if this was dedicated
database server, as if its shared then the other projects sharing it wont be
happy as your DB wil be hit more than necessary. DB cursors are no
relacement for a well architected caching solution across a load balanced
server farm.

From a cost perspective alone, its usually cheaper to follow a typcal
presentation/business/data layer approach. Gil;ding the lilly without
significant reason is just an excuse to delay a project. Sounds like he is
a contractor who is looking to extend his reason for being.
Regards

John Timney (MVP)
http://www.johntimney.com
http://www.johntimney.com/blog
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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?


Jul 6 '07 #12
Jon
On this particular server, we will be running up to 30 or so different
database for 20 different applications. So this is not a dedicated box for
this application

"John Timney (MVP)" <x_****@timney.eclipse.co.ukwrote in message
news:MK******************************@eclipse.net. uk...
That makes no sense. I would immediately question if this was dedicated
database server, as if its shared then the other projects sharing it wont
be happy as your DB wil be hit more than necessary. DB cursors are no
relacement for a well architected caching solution across a load balanced
server farm.

From a cost perspective alone, its usually cheaper to follow a typcal
presentation/business/data layer approach. Gil;ding the lilly without
significant reason is just an excuse to delay a project. Sounds like he
is a contractor who is looking to extend his reason for being.
Regards

John Timney (MVP)
http://www.johntimney.com
http://www.johntimney.com/blog
"Jon" <Jo*@community.nospam.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>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?



Jul 6 '07 #13
Jon wrote:
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.
Whether to use to put all the business logic in the code or to put it
(or some of it) in stored procedures within the database is a very old
discussion.

If you are doing a product that needs to support multiple databases,
then SP's is going to be a nasty headache. But if it is a project
specific solution, then it is no big problem, because a company
does not change database every other week.

If you have applications written in different non compatible
languages, then putting the logic in SP's may allow the
different application to share the same logic instead of
duplicating it.

Generally it is expected that:
- SP's perform better
- all logic in code scale better

So it is not so obvious what the optimal solution is.

But the description you give with "create XML" and "parse out data"
could indicate that he has gone a bit to far with the SP's. SQL
and the extended SQL dialects use din SP's are very good for
manipulating relation data, but it is a misuse to use it as a
general programming language.

Arne
Aug 19 '07 #14
Arne Vajhøj wrote:
Jon wrote:
>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.

Whether to use to put all the business logic in the code or to put it
(or some of it) in stored procedures within the database is a very old
discussion.

If you are doing a product that needs to support multiple databases,
then SP's is going to be a nasty headache. But if it is a project
specific solution, then it is no big problem, because a company
does not change database every other week.

If you have applications written in different non compatible
languages, then putting the logic in SP's may allow the
different application to share the same logic instead of
duplicating it.

Generally it is expected that:
- SP's perform better
- all logic in code scale better

So it is not so obvious what the optimal solution is.

But the description you give with "create XML" and "parse out data"
could indicate that he has gone a bit to far with the SP's. SQL
and the extended SQL dialects used in SP's are very good for
manipulating relation data, but it is a misuse to use it as a
general programming language.
You say "SP's perform better". That's a sweeping statement and more
often than not might be wrong. It needs skill and a detailed knowledge
of the database and how it stores data to make a SP perform better than
a simple (or even complex) query. I'm sceptical. I agree with your last
para.

Cheers,

Cliff

--

Have you ever noticed that if something is advertised as 'amusing' or
'hilarious', it usually isn't?
Aug 19 '07 #15
Enkidu wrote:
>But the description you give with "create XML" and "parse out data"
could indicate that he has gone a bit to far with the SP's. SQL
and the extended SQL dialects used in SP's are very good for
manipulating relation data, but it is a misuse to use it as a
general programming language.
You say "SP's perform better". That's a sweeping statement and more
often than not might be wrong. It needs skill and a detailed knowledge
of the database and how it stores data to make a SP perform better than
a simple (or even complex) query. I'm sceptical. I agree with your last
para.
It is common knowledge, however. At least with the likes of SQL Server,
MySQL, Oracle, and their kind, a stored procedure will always outperform
hand-carved SQL statements from application code. Assuming, of course,
the query, insert, or whatever, can be expressed in a sproc; inline SQL
statements can be more flexible but it's rare indeed where you need to
access a database fashion in that way and can't refactor that bit to
work with sproc(s).

Would you be able to post an example of inline SQL that performs faster
than a corresponding sproc?
--
-glenn-
Aug 19 '07 #16
GlennDoten wrote:
Arne Vajhøj wrote:
>There can be both performance and multiple application support reasons
to put logic in SP's in the database.

I would say that this is an extremely rare design avenue to take.
It can be discussed whether it is good or bad.

It is a fact that it is often seen.
I
can't even think of why one would do this.
Reasons given in what you quoted.
Some sort of code (in this forum most likely C#) must call the stored
procedures. At worst, business logic may be placed into those methods
but I cannot think of a single reason why business logic would ever have
to go any "lower" than that (i.e., right in a sproc). And even putting
business logic into the data access layer like this is practically as
bad an idea as putting it right in the sproc, for the same reason that
some sort of code (again, C#) would need to call into the data access
layer and business logic should really never go any lower than that.
With models like this you still have full multiple application support.
You are making an assumption that all the applications are made
in same language/framework. That is not always the case.
And I would counter the performance claim (in the general sense, anyhow)
with pointing out that a database probably needs to be denormalized to
some extent to make it faster way before anyone should ever put business
logic into a sproc.
If denormalization (or more nice: materialized views) help then the
problem it is unlikely that the usage of SP's will help and vice versa.
The one is a query problem the other is primarily a tier crossing
overhead problem.
>Whether such reasons exist in this case or whether there are good
reasons not to do so is not possible to say based on the
information present.

Yes, each application is different. But I do think there are some pretty
basic guiding principles that should be adhered to, or in this case
principles that should be completely avoided.
Why do you think MS put the ability to write SP's in C# in 2005 ?

Arne
Aug 20 '07 #17
Enkidu wrote:
Arne Vajhøj wrote:
>Generally it is expected that:
- SP's perform better
- all logic in code scale better
You say "SP's perform better". That's a sweeping statement and more
often than not might be wrong. It needs skill and a detailed knowledge
of the database and how it stores data to make a SP perform better than
a simple (or even complex) query. I'm sceptical.
There are no reason to believe a single statement SP is faster, but
there are good reasons to believe a multi statement SP is faster.

Arne
Aug 20 '07 #18
Arne Vajhøj wrote:
GlennDoten wrote:
>Arne Vajhøj wrote:
>>There can be both performance and multiple application support reasons
to put logic in SP's in the database.

I would say that this is an extremely rare design avenue to take.

It can be discussed whether it is good or bad.
Certainly! And my part of the discussion was that it is an extremely
rare thing to even worry about. I didn't mean to say it shouldn't be
discussed.
>Some sort of code (in this forum most likely C#) must call the stored
procedures. At worst, business logic may be placed into those methods
but I cannot think of a single reason why business logic would ever
have to go any "lower" than that (i.e., right in a sproc). And even
putting business logic into the data access layer like this is
practically as bad an idea as putting it right in the sproc, for the
same reason that some sort of code (again, C#) would need to call into
the data access layer and business logic should really never go any
lower than that. With models like this you still have full multiple
application support.

You are making an assumption that all the applications are made
in same language/framework. That is not always the case.
Yes, I am. That's why I clearly called out the fact that this is the C#
forum so, yes, some assumptions are being made.
>And I would counter the performance claim (in the general sense,
anyhow) with pointing out that a database probably needs to be
denormalized to some extent to make it faster way before anyone should
ever put business logic into a sproc.

If denormalization (or more nice: materialized views) help then the
problem it is unlikely that the usage of SP's will help and vice versa.
The one is a query problem the other is primarily a tier crossing
overhead problem.
My experience with many databases is that when you start to think you
need to put business logic into sprocs the first thing to look at is an
overly normalized database. A highly normalized database can easily
cause performance problems. But it's always better to denormalize a bit
than to put any business logic into sprocs.

Views wouldn't help an overly normalized database. But I'm not sure what
you mean with that point.
>>Whether such reasons exist in this case or whether there are good
reasons not to do so is not possible to say based on the
information present.

Yes, each application is different. But I do think there are some
pretty basic guiding principles that should be adhered to, or in this
case principles that should be completely avoided.

Why do you think MS put the ability to write SP's in C# in 2005 ?
I think that was a marketing ploy and nothing more. No one I've worked
with thinks that writing sprocs in C# makes any sense, mostly because
the performance is horrible. Word is that's what the SQL team thinks
too. I can see creating and sharing C# user-defined types in SQL Server,
but that's about it with regards to CLR integration into SQL Server.

--
-glenn-
Aug 20 '07 #19
Arne Vajhøj wrote:
Enkidu wrote:
>Arne Vajhøj wrote:
>>Generally it is expected that:
- SP's perform better
- all logic in code scale better
>You say "SP's perform better". That's a sweeping statement and more
often than not might be wrong. It needs skill and a detailed knowledge
of the database and how it stores data to make a SP perform better
than a simple (or even complex) query. I'm sceptical.

There are no reason to believe a single statement SP is faster, but
there are good reasons to believe a multi statement SP is faster.
But, um, yes, single-statement sprocs are indeed faster than their
inline SQL counterparts. Every time. Enkidu, there's no skill involved
there. If you time running, say, a single SELECT run via inline SQL and
the same SELECT run via a sproc then sproc will always be faster. A lot
faster? Not really. But faster nonetheless.

--
-glenn-
Aug 20 '07 #20
GlennDoten wrote:
Arne Vajhøj wrote:
>GlennDoten wrote:
>>Arne Vajhøj wrote:
There can be both performance and multiple application support reasons
to put logic in SP's in the database.

I would say that this is an extremely rare design avenue to take.
>>Some sort of code (in this forum most likely C#) must call the stored
procedures. At worst, business logic may be placed into those methods
but I cannot think of a single reason why business logic would ever
have to go any "lower" than that (i.e., right in a sproc). And even
putting business logic into the data access layer like this is
practically as bad an idea as putting it right in the sproc, for the
same reason that some sort of code (again, C#) would need to call
into the data access layer and business logic should really never go
any lower than that. With models like this you still have full
multiple application support.

You are making an assumption that all the applications are made
in same language/framework. That is not always the case.

Yes, I am. That's why I clearly called out the fact that this is the C#
forum so, yes, some assumptions are being made.
It is a very valid assumption considering the forum that C# is
used.

Assuming that nothing else than .NET is used is not a very
realistic assumption.
>>And I would counter the performance claim (in the general sense,
anyhow) with pointing out that a database probably needs to be
denormalized to some extent to make it faster way before anyone
should ever put business logic into a sproc.

If denormalization (or more nice: materialized views) help then the
problem it is unlikely that the usage of SP's will help and vice versa.
The one is a query problem the other is primarily a tier crossing
overhead problem.

My experience with many databases is that when you start to think you
need to put business logic into sprocs the first thing to look at is an
overly normalized database. A highly normalized database can easily
cause performance problems. But it's always better to denormalize a bit
than to put any business logic into sprocs.
Two different problems.
Views wouldn't help an overly normalized database. But I'm not sure what
you mean with that point.
I did not say "view" - I said "materialized view".

And it is a common remedy for join performance. Sligthly more
advanced than just denormalising the database.

(which is called something else in SQLServer but the concept
is wellknown)
>>>Whether such reasons exist in this case or whether there are good
reasons not to do so is not possible to say based on the
information present.

Yes, each application is different. But I do think there are some
pretty basic guiding principles that should be adhered to, or in this
case principles that should be completely avoided.

Why do you think MS put the ability to write SP's in C# in 2005 ?

I think that was a marketing ploy and nothing more. No one I've worked
with thinks that writing sprocs in C# makes any sense, mostly because
the performance is horrible. Word is that's what the SQL team thinks
too. I can see creating and sharing C# user-defined types in SQL Server,
but that's about it with regards to CLR integration into SQL Server.
Oracle, Sybase, DB2 etc. has SP's in Java.

Rather surprising that everybody has it if noone uses it.

Arne
Aug 27 '07 #21
GlennDoten wrote:
Arne Vajhøj wrote:
>Enkidu wrote:
>>Arne Vajhøj wrote:
Generally it is expected that:
- SP's perform better
- all logic in code scale better
>>You say "SP's perform better". That's a sweeping statement and more
often than not might be wrong. It needs skill and a detailed
knowledge of the database and how it stores data to make a SP perform
better than a simple (or even complex) query. I'm sceptical.

There are no reason to believe a single statement SP is faster, but
there are good reasons to believe a multi statement SP is faster.

But, um, yes, single-statement sprocs are indeed faster than their
inline SQL counterparts. Every time. Enkidu, there's no skill involved
there. If you time running, say, a single SELECT run via inline SQL and
the same SELECT run via a sproc then sproc will always be faster. A lot
faster? Not really. But faster nonetheless.
The performance benefits of SP's for single statement is usually
very small.

For obvious reasons - the exact same things are done.

For multi statement the performance benefits can be
rather big.

Again for obvious reasons: roundtrips between app and
db are eliminated.

Arne
Aug 27 '07 #22
Enkidu wrote:
You say "SP's perform better". That's a sweeping statement and more
often than not might be wrong. It needs skill and a detailed knowledge
of the database and how it stores data to make a SP perform better than
a simple (or even complex) query. I'm sceptical.
Usualy you would use the exact same SQL in the SP and non SP
solution, so the extra skills required for a SP solution
compared to a non SP solution is: zero.

Arne

Aug 27 '07 #23
On Aug 20, 3:59 am, GlennDoten <gdo...@gmail.comwrote:
But, um, yes, single-statement sprocs are indeed faster than their
inline SQL counterparts. Every time. Enkidu, there's no skill involved
there. If you time running, say, a single SELECT run via inline SQL and
the same SELECT run via a sproc then sproc will always be faster. A lot
faster? Not really. But faster nonetheless.
Glenn, that's a sweeping statement that's no longer always true. SQL
Server 2005 does a much better job reusing execution plans for
parameterized statements, even when the parameter values change
between queries. Here's MSDN, under the execute_sql entry:

<MSDN>
In earlier versions of SQL Server, the only way to be able to reuse
execution plans is to define the Transact-SQL statements as a stored
procedure and have the application execute the stored procedure. This
generates additional administrative overhead for the applications.
Using sp_executesql can help reduce this overhead while still letting
SQL Server reuse execution plans. sp_executesql can be used instead of
stored procedures when executing a Transact-SQL statement several
times, when the only variation is in the parameter values supplied to
the Transact-SQL statement. Because the Transact-SQL statements
themselves remain constant and only the parameter values change, the
SQL Server query optimizer is likely to reuse the execution plan it
generates for the first execution.
</MSDN>

I put together a little benchmark to compare a simple query with its
equivalent stored procedure. I ran both 10,000 times. Here's what I
saw:

Stored procedure 10000 iterations. Elapsed time: 00:00:09.9843111
Statement 10000 iterations. Elapsed time: 00:00:09.9530613

(to be fair, sometimes the stored procedure would come out ahead.
they were always within half a percent of each other)

Here's my source:

using System;
using System.Data.SqlClient;
using System.Data;

namespace StatementVersusSP
{
class Program
{
static string server = XXXX
static string database = XXXX;
static string connString = "Data Source={0};Initial
Catalog={1};Integrated Security=True";

static void Main(string[] args)
{
Compare(10000);
Console.ReadLine();
}

static private void Compare(int iterations)
{
// preload
for (int i = 0; i < 10; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStoredProcedure(conn, i);
}
}

for (int i = 0; i < 10; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStatement(conn, i);
}
}
//

DateTime start;
DateTime end;

start = DateTime.Now;
for (int i = 0; i < iterations; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStoredProcedure(conn, i);
}
}
end = DateTime.Now;
Console.WriteLine(String.Format("Stored procedure {0} iterations.
Elapsed time: {1}", iterations, end - start));

start = DateTime.Now;
for (int i = 0; i < iterations; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStatement(conn, i);
}
}
end = DateTime.Now;
Console.WriteLine(String.Format("Statement {0} iterations. Elapsed
time: {1}", iterations, end - start));

}

private static void QueryWithStoredProcedure(SqlConnection conn, int
i)
{
// defined as:
//create proc test_sp
//@id int
//as
//SELECT * from TEST WHERE ID = @id
SqlCommand command = new SqlCommand("test_sp", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@id", i));
conn.Open();
using (IDataReader dr = command.ExecuteReader())
{
dr.Read();
}
}

private static void QueryWithStatement(SqlConnection conn, int i)
{
SqlCommand command = new SqlCommand("SELECT * FROM Test WHERE ID =
@id", conn);
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@id", i));
conn.Open();
using (IDataReader dr = command.ExecuteReader())
{
dr.Read();
}
}
}
}

Michael

Aug 28 '07 #24
Finally!

Due to maintenance reasons sp's are a "bad" option. IMHO.

However, unfortunately it doesn't apply to sequences of statements, for
example, when you have to do complex joins and use intermediary tables and
(maybe) updates and inserts. Probably caching issues or space for
improvement.

Somehow, on my SQL 2005 Express sp's are somewhat better in general.
<mp*******@gmail.comwrote in message
news:11**********************@l22g2000prc.googlegr oups.com...
On Aug 20, 3:59 am, GlennDoten <gdo...@gmail.comwrote:
>But, um, yes, single-statement sprocs are indeed faster than their
inline SQL counterparts. Every time. Enkidu, there's no skill involved
there. If you time running, say, a single SELECT run via inline SQL and
the same SELECT run via a sproc then sproc will always be faster. A lot
faster? Not really. But faster nonetheless.

Glenn, that's a sweeping statement that's no longer always true. SQL
Server 2005 does a much better job reusing execution plans for
parameterized statements, even when the parameter values change
between queries. Here's MSDN, under the execute_sql entry:

<MSDN>
In earlier versions of SQL Server, the only way to be able to reuse
execution plans is to define the Transact-SQL statements as a stored
procedure and have the application execute the stored procedure. This
generates additional administrative overhead for the applications.
Using sp_executesql can help reduce this overhead while still letting
SQL Server reuse execution plans. sp_executesql can be used instead of
stored procedures when executing a Transact-SQL statement several
times, when the only variation is in the parameter values supplied to
the Transact-SQL statement. Because the Transact-SQL statements
themselves remain constant and only the parameter values change, the
SQL Server query optimizer is likely to reuse the execution plan it
generates for the first execution.
</MSDN>

I put together a little benchmark to compare a simple query with its
equivalent stored procedure. I ran both 10,000 times. Here's what I
saw:

Stored procedure 10000 iterations. Elapsed time: 00:00:09.9843111
Statement 10000 iterations. Elapsed time: 00:00:09.9530613

(to be fair, sometimes the stored procedure would come out ahead.
they were always within half a percent of each other)

Here's my source:

using System;
using System.Data.SqlClient;
using System.Data;

namespace StatementVersusSP
{
class Program
{
static string server = XXXX
static string database = XXXX;
static string connString = "Data Source={0};Initial
Catalog={1};Integrated Security=True";

static void Main(string[] args)
{
Compare(10000);
Console.ReadLine();
}

static private void Compare(int iterations)
{
// preload
for (int i = 0; i < 10; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStoredProcedure(conn, i);
}
}

for (int i = 0; i < 10; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStatement(conn, i);
}
}
//

DateTime start;
DateTime end;

start = DateTime.Now;
for (int i = 0; i < iterations; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStoredProcedure(conn, i);
}
}
end = DateTime.Now;
Console.WriteLine(String.Format("Stored procedure {0} iterations.
Elapsed time: {1}", iterations, end - start));

start = DateTime.Now;
for (int i = 0; i < iterations; i++)
{
using (SqlConnection conn = new
SqlConnection(String.Format(connString, server, database)))
{
QueryWithStatement(conn, i);
}
}
end = DateTime.Now;
Console.WriteLine(String.Format("Statement {0} iterations. Elapsed
time: {1}", iterations, end - start));

}

private static void QueryWithStoredProcedure(SqlConnection conn, int
i)
{
// defined as:
//create proc test_sp
//@id int
//as
//SELECT * from TEST WHERE ID = @id
SqlCommand command = new SqlCommand("test_sp", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@id", i));
conn.Open();
using (IDataReader dr = command.ExecuteReader())
{
dr.Read();
}
}

private static void QueryWithStatement(SqlConnection conn, int i)
{
SqlCommand command = new SqlCommand("SELECT * FROM Test WHERE ID =
@id", conn);
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@id", i));
conn.Open();
using (IDataReader dr = command.ExecuteReader())
{
dr.Read();
}
}
}
}

Michael

Aug 28 '07 #25

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by comp.lang.php | last post: by
3 posts views Thread by Michael Crawford | last post: by
6 posts views Thread by Tim | last post: by
1 post views Thread by dilip ranganathan | last post: by
3 posts views Thread by Ken H | last post: by
2 posts views Thread by John A | last post: by
13 posts views Thread by rrs.matrix | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.