really large stored procedures

i'm not much of a sql developer, most of my exposure to it comes from
needing to get data for my web applications.
i've been kinda wondering, is it abnormal to have stored procs that are
over 500 lines long?
the business requirements seem to warrant such a long procedure and it
doesn't seem to have any absurd inefficiencies, but at the same time is
it very unusual?
in the object oriented world, this would be considered horrible style,
yet i can't really find any guidelines on the topic, so i thought i'd
ask.
TIAnot necessarily - in sql, typically it's the size of the statements, e.g.
select name from sysobjects where xtype='U'
and
select
name
from
sysobjects
where
xtype = 'U'
are the same statement, but one is 1 line and the other is 6 lines.
however, many statements often indicates that you've got a lot of
procedural code and not much relational (set-based) code.
in the oo world, you have no choice - e.g., if you want to update all
rows in a resultset to have the same StatusCode, you have to loop
through it and set it.
but in sql, you wouldn't need a cursor to do this -- you would just need
a single update statement, e.g.
update mytable
set status = 'P'
where <conditions>
so, the [well, my] answer is -- line count is not intrinsically
indicative of a bad proc. bad code is.
vortep@.gmail.com wrote:
> i'm not much of a sql developer, most of my exposure to it comes from
> needing to get data for my web applications.
> i've been kinda wondering, is it abnormal to have stored procs that are
> over 500 lines long?
> the business requirements seem to warrant such a long procedure and it
> doesn't seem to have any absurd inefficiencies, but at the same time is
> it very unusual?
> in the object oriented world, this would be considered horrible style,
> yet i can't really find any guidelines on the topic, so i thought i'd
> ask.
> TIA
>|||T-SQL is not object oriented, so 500 or more is very common. When I write a
SQL statement, I use a style that breaks each selected column onto a
seperate line (and I typically select a lot of columns when developing
reports or fact tables for OLAP cubes), so my procedures and views tend to
be 100s or even 1000s of lines becuase of this.
<vortep@.gmail.com> wrote in message
news:1136407727.493541.163990@.g44g2000cwa.googlegroups.com...
> i'm not much of a sql developer, most of my exposure to it comes from
> needing to get data for my web applications.
> i've been kinda wondering, is it abnormal to have stored procs that are
> over 500 lines long?
> the business requirements seem to warrant such a long procedure and it
> doesn't seem to have any absurd inefficiencies, but at the same time is
> it very unusual?
> in the object oriented world, this would be considered horrible style,
> yet i can't really find any guidelines on the topic, so i thought i'd
> ask.
> TIA
>|||vortep@.gmail.com wrote:
> i'm not much of a sql developer, most of my exposure to it comes from
> needing to get data for my web applications.
> i've been kinda wondering, is it abnormal to have stored procs that
> are over 500 lines long?
> the business requirements seem to warrant such a long procedure and it
> doesn't seem to have any absurd inefficiencies, but at the same time
> is it very unusual?
> in the object oriented world, this would be considered horrible style,
> yet i can't really find any guidelines on the topic, so i thought i'd
> ask.
> TIA
No. 500 lines is fine as long as you are not experiencing Recompiles or
allowing extended locks on tables during production hours. If you see
recompiles (SQL 2000), break out the code causing the recompile or
eliminate it if possible. SQL 2005 implements statement recompilation,
which eliminates most of the recompile overhead. In any case, recompiles
hurt performance, so eliminate them if possible. You can check for them
using Profiler and the SP:Recompile event (SQL 2000) and the
SQL:StmtRecompile for SQL 2005.
David Gugick
Quest Software
www.quest.com|||(vortep@.gmail.com) writes:
> i'm not much of a sql developer, most of my exposure to it comes from
> needing to get data for my web applications.
> i've been kinda wondering, is it abnormal to have stored procs that are
> over 500 lines long?
> the business requirements seem to warrant such a long procedure and it
> doesn't seem to have any absurd inefficiencies, but at the same time is
> it very unusual?
> in the object oriented world, this would be considered horrible style,
> yet i can't really find any guidelines on the topic, so i thought i'd
> ask.
A database is not an object-oriented world.
A 500-line stored procedure is not a small baby, but we have quite a
few of that size in our database. I think our longest is around 3000
lines.
Partly this is due to the fact that T-SQL is not well aimed of breaking
things into pieces. You can write procedures and pass parameters, but
these are scalar, and in SQL your rather work with tables. You can share
tables between stored procedures, but it is a bit kludgy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Coming from an OO background I also wondered about the efficiencies of
large stored procs when transitioning to SQL Server development (as you
I had some exposure to it but from a front-end / middle-tier
perspective only).
I think that if you want to evaluate your stored proc and maybe
consider rewriting them you should definitly read up on set-based vs.
procedural. There's tons of info in this newsgroup for you to lap up in
any case.
regards,
Gerard|||From my own point of view is very common to have this kind of sp in a
production environment.
"Gerard" wrote:

> Coming from an OO background I also wondered about the efficiencies of
> large stored procs when transitioning to SQL Server development (as you
> I had some exposure to it but from a front-end / middle-tier
> perspective only).
> I think that if you want to evaluate your stored proc and maybe
> consider rewriting them you should definitly read up on set-based vs.
> procedural. There's tons of info in this newsgroup for you to lap up in
> any case.
> regards,
> Gerard
>|||Not at all abnormal. Bear in mind that in SQL, unlike in other
languages, there can be two reasons for a stored procedure being long:
a) Large number of statements
b) Large number of lines - long statements running over multiple lines.
Long statements are more common in SQL than in other languages due to
the sheer complexity of single SELECT statements, which may include a
long list of columns, references to multiple tables joined together,
and complex WHERE clauses. A long statement doesn't necessary mean a
long processing time, as long as joins are well-designed and tables are
indexed. Just switched windows to a stored procedure containing
multiple 80-line SQL statements - processing time is under a second.
The things to watch for for efficiency's sake are the things that SQL
Server is slow at: I've found that DELETE operations, and looping
through cursors are the worst for this.
Seb|||thanks guys,
i definately use one line for every "word" and don't use cursors unless
absolutely necessary
general format i use is
select
blah1,
blah2,
blah3
from
tablesmth
inner join
someothertable
on
somefield
=someotherfield
where
stuff
=stuff2
and
stuff3
=stuff4|||Line breaks are a renewable resource, so use them whereever you feel they
are useful.
<vortep@.gmail.com> wrote in message
news:1136488641.467676.117480@.z14g2000cwz.googlegroups.com...
> thanks guys,
> i definately use one line for every "word" and don't use cursors unless
> absolutely necessary
> general format i use is
> select
> blah1,
> blah2,
> blah3
> from
> tablesmth
> inner join
> someothertable
> on
> somefield
> =someotherfield
> where
> stuff
> =stuff2
> and
> stuff3
> =stuff4
>