On Sat, 06 Jul 2002 01:16:28 -0500 "Richard Lynch" <[EMAIL PROTECTED]> wrote:
> > I'm using a postgres datbase for my PHP project, how do I make stored > >procedures? Or if no SPs then what would be recomendation for building > >simple/reuseable "Put" and "Get" procedures for my data? > > Well, if nothing else, PostgreSQL does support user-defined functions, > going'way back. > > Technically not quite the same as a Stored Procedure, but should do what > you need. > > Of course, *WHY* you want to add such a ridiculous layer of overhead to > your code is beyond my comprehension, but that's another story... :-) > > I once worked at a place where the head IT guy was convinced Stored > Procedures were the bomb. > > Alas, he didn't tell me that until after I had coded the most of the ASP > application without them. > > Meanwhile, deadlines were looming, and I wasn't migrating to Stored > Procedures, since I was furiously coding all the change requests (well, > okay, they were really "Features The Client Thought Up During Development > Because He Didn't Design Anything Beforehand" but they were called change > requests anyway. > > So a new guy they hired was assigned the task of converting all my: > > <% > $query = "select blah, blah, blah"; > %> > > code into Stored Procedures. > > Guess what? > > *ONE* of the pages was a little faster. The other hundred pages were just > as fast with $query = "select..." > > Guess what else? > > When we migrated from SQL 6.5 to SQL 7.x, all the Stored Procedures puked. > > Guess what else? > > Before the Stored Procedure conversion, it was trivial to Push from the > Dev box to the Production box. > After the conversion, it was a nightmare. I ended up writing an Admin > tool to connect to both databases and compare the text of the Stored > Procedure source (buried in badly-designed Microsoft tables) between the > Dev Server and the Production Server. > Of course, in the first round *ALL* the procedures were different, since > Microsoft added/stripped altered the text of the Stored Procedures while > copying them from Dev to Server in the first place. > > Guess what else? > > There weren't enough queries "the same" that there was any real code > re-use. > I coded the application and the pages were designed properly in the first > place, so very seldom were two queries the same. If they had been the > same, I would have put those two pages (features) into one. > > Guess what else? > > The new guy was in such a hurry, that in the few instances that two > queries*were* the same, he didn't bother to figure that out, so we ended > up with some Stored Procedures that were duplicates of others in > everything except their name. > > Guess what else? > > The @@INSERT_ID I was using worked differently inside a Stored Procedure, > so I wasted days tracking down a bug introduced by the Stored Procedures. > > Guess what else? > > When I went to edit the pages he had changed, I'd have no idea what data > was coming back from the Stored Procedure, without reading way too many > lines of code. With the $query = "select x, y, z" style, I knew exactly > what I was getting. > > All in all, the company spent 4 weeks of this guys' life, 40 hours a week, > making the application less portable, less maintainable, and no faster. > > And people wonder why I see little value in Stored Procedures. > > -- > Like Music? http://l-i-e.com/artists.htm > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > Stored procedures are like any other type of programming construct. You can do them right or you can do them wrong. When making a stored procedure you should stick to ansi SQL as much as possible. Most of my stored procedures I can move from a SQL Server 2000 box to an Oracle 8i box with not problems at all. Stored procedures are NOT over head. If you need to change an SQL statement, then you would have to search through all your code to make changes intstead of just one stored proc. If you don't see any speed increase from stored procs then you are doing something wrong. Stored procs are compile SQL statements. Every time a your php page does something like $query="Select * from MyTable" the DB needs to parse the query and create an execution plan. The stored procs do it only ONCE the first time it is ran and all the other calls to it save you many millisecond to seconds. That might not sound like much but if you have a site with more than 5 users you will see a difference. The intranet I finished for my company has 100,000 users and sustains almost 1,000 users per second. The pages took 7 to 10 seconds without stored procs and went down to 3-4 seconds with them. Also, new features needed to be added to the site and required some tables to be changed. I only had to change one SQL in on location and everything was fine. Jim Drabb -- James Drabb JR - Programmer Analyst - Orlando, FL - [EMAIL PROTECTED] --------------------------------------------------------------------- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
