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

[Carl Olsen]

I'm finding that prepared statements seem to be the most flexible as far as
returning the exact data I need.  Do you think I gain a performance boost
from using prepared statements rather than stored procedures?

I'm trying to use stored procedures as much as I can, but there are just
some queries that are too complex for me to figure out using stored
procedures.  It's also faster to write the code for a prepared statement
because I don't have to leave the PHP IDE and switch to some MySQL tool to
create stored procedures.  I'm starting to think prepared statements are the
best thing that MySQL and PHP have come up with, at least that's my favorite
piece of new functionality.

I'm also thinking it would be easier for another programmer to understand
the code if all of the functionality is in one place (all in prepared
statements or all in stored procedures), rather than having some of it in
prepared statements and some of it stored procedures.  Someone is going to
have a difficult time understanding why I switched from one to the other
(because it was easier for me to write it one way as opposed to the other,
which is going to vary from one programmer to another).

--
Carl Olsen, MCSE
Web Developer, CMS Implementation
Marketing and Communications
Drake University
2507 University Avenue
115 Old Main
Des Moines, Iowa 50311-4505
Phone: 515-271-2986
Fax: 515-271-3798
Carl.Olsen at drake.edu
www.drake.edu


-----Original Message-----
From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On Behalf
Of Daniel.Juliano at wellsfargo.com
Sent: Monday, July 24, 2006 11:15 AM
To: ciapug at cialug.org
Subject: RE: [ciapug] Re: Re: MySQL 5 Stored Procedures (correction)

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

_______________________________________________
ciapug mailing list
ciapug at cialug.org
http://cialug.org/mailman/listinfo/ciapug