[ciapug] Re: Re: MySQL 5 Stored Procedures (correction)

[Daniel.Juliano at wellsfargo.com]

Couldn't help but post to this thread - I saw the line

  "I've since concluded the architect, even for those days, was making a
bad mistake."

and since I had recently written the email you see below, I felt the
need to repost it here.
=====================================

I found that article on stored procs:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

>From the article:

"SQL Server 2000 and SQL Server version 7.0 incorporate a number of
changes to statement processing that extend many of the performance
benefits of stored procedures to all SQL statements. SQL Server 2000 and
SQL Server 7.0 do not save a partially compiled plan for stored
procedures when they are created. A stored procedure is compiled at
execution time, like any other Transact-SQL statement. SQL Server 2000
and SQL Server 7.0 retain execution plans for all SQL statements in the
procedure cache, not just stored procedure execution plans. "

And just for backup (linked in article
http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx):

"The dynamic query routine was twice as fast as the stored procedure
routine. I ran the routines for about 10,000 loops, and a number of
times, but each time the dynamic query one was faster. This was of
course because the dynamic query was tailored to the task, without
expensive statements as COALESCE(), while the stored procedure was
always using the same, slower execution plan. I didn't expect this,
because I thought the execution plan of the dynamic query is thrown away
each time a query is executed, but this is not the case. The stored
procedure version was using more CPU power on the server, while the
dynamic query was using more CPU power on the client. I didn't cache any
generated query so every time I had to create the query again in a
stringbuilder object however that worked fine (and garbage collection
kicked in rather smoothly). 

Furthermore (from the original article):

"I too find writing code like string s = "SELECT * FROM Foo WHERE Bar =
" + barValue; in your code not the right thing to do. However the
alternative is not stored procedures, it's a component that generates
this SQL on the fly so you don't have the disadvantages of stored
procedures and have the advantages of generating the SQL you need.
Writing such a component is a one-time effort, you can reuse that
component each time you access a database. Stored procedures are not the
answer, Dynamic SQL is."

Daniel Juliano


-----Original Message-----
From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On
Behalf Of Tony Bibbs
Sent: Monday, July 10, 2006 5:36 PM
To: carl-olsen at mchsi.com; ciapug at cialug.org
Subject: Re: [ciapug] Re: Re: MySQL 5 Stored Procedures (correction)

Once upon a time, I use to program with MS tools and the craze back then
was that all the VB screens did all the CRUD work using SP's.  So
EVERYTHING went into the database.  While I respected the speed of this,
I've since concluded the architect, even for those days, was making a
bad mistake.  By now I'm sure they've either rewritten or ported that
system and I can only imagine the pain and agony that came with it.

...which may explain the lack of any MSCD's where I work.

--Tony