Re: [PHP] Re: Stored Procedures
>On Sat, 06 Jul 2002 15:27:47 -0500 >> Do you have *ANY* idea how quickly: >> >> "select * from MyTable" can be parsed and an execution plan selected?! >> >> It's CHUMP CHANGE in time. >> >> *ONLY* if your SQL is so incredibly complicated that you can't even >> understand it will the parse/compile time of SQL be a factor in >> performance. > >It is "CHUMP CHANGE" when you make a tinie web site with 2 users. Do the >math. If you have a SQL statement that takes 250 milliseconds to parse >and create an execution plan, then 250 * 1,000,000 page request per week >(which is what the site I finished averages, the company I >work for has 110,000 employees) = ??? This is second grade math. > >No matter how you look at it, 10 extra milliseconds here or there >adds up when you work on a big site. The db's I work with are not >simple "select foo from bar" queries. An enterprise db is usually pretty >complex. My main reason for posting a reply was not to start a stupid >flame war with you. It was from stopping you from filling the heads of >new programmers on this list with bunk. Stored procedures are not junk! >I wonder why they are the most requested feature for MySQL? Why would all >the Big DB's (Oracle, DB2, PostgreSQL, SQL Server, etc.) support them >if they had no benifit? The biggest benifit is SPEED, the second is >the ability to encapsulate the underlying database structure. A DBA can >change the db structure at will as long as the sproc returns the same >columns. Yes, if your site gets millions of hits, an SPROC can improve raw SPEED. And it *MAY* be the only option available to wring out that performance you need. There are some significant down-sides to the administration of SRPOC, as I noted earlier. The company I was working for was *NOT* getting millions of hits, had no *CHANCE* of getting millions of hits, and mindlessly wasted weeks' of developer time and drastically increased maintenance costs because "SPROCS make it faster" has been the watch-word. Their DBA was their chief IT who was their main lead developer, and then there was me. Two programmers in the entire 7-person company. If somebody actually *HAS* a system large enough to need SPROCs and *has* a DBA who isn't also the chief-bottle-washer, they almost-for-sure don't need me, or you, to tell them about SPROCs. :-) My primary concern is the little guy who's going to run out and change their entire architecture around for a 3000-hits-a-month web-site because they keep hearing "SPROCs will make it faster" without anybody ever bothering to mention little things like "overhead" and "administration" and "maintenance" to them. I'm sorry my rant touched off a nerve, and if there *IS* anybody stuck with a million-hits-a-day site, by all means, *TEST* some SPROCs and see if they improve your performance enough to make the maintenance hit worthwhile! By all means, *DESIGN* the darn thing with as many layers of solid, clean, simple API as it takes. But if you're trying to improve page load speed on a low-traffic site, SPROCs are almost for sure not your answer. If you don't even *have* a speed problem, running out and spending a gazillion $$$ worth of people-hours on SPROCs is downright silly. -- 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
Re: [PHP] Re: Stored Procedures
On Sat, 06 Jul 2002 15:27:47 -0500 "Richard Lynch" <[EMAIL PROTECTED]> wrote: > Do you have *ANY* idea how quickly: > > "select * from MyTable" can be parsed and an execution plan selected?! > > It's CHUMP CHANGE in time. > > *ONLY* if your SQL is so incredibly complicated that you can't even > understand it will the parse/compile time of SQL be a factor in > performance. It is "CHUMP CHANGE" when you make a tinie web site with 2 users. Do the math. If you have a SQL statement that takes 250 milliseconds to parse and create an execution plan, then 250 * 1,000,000 page request per week (which is what the site I finished averages, the company I work for has 110,000 employees) = ??? This is second grade math. No matter how you look at it, 10 extra milliseconds here or there adds up when you work on a big site. The db's I work with are not simple "select foo from bar" queries. An enterprise db is usually pretty complex. My main reason for posting a reply was not to start a stupid flame war with you. It was from stopping you from filling the heads of new programmers on this list with bunk. Stored procedures are not junk! I wonder why they are the most requested feature for MySQL? Why would all the Big DB's (Oracle, DB2, PostgreSQL, SQL Server, etc.) support them if they had no benifit? The biggest benifit is SPEED, the second is the ability to encapsulate the underlying database structure. A DBA can change the db structure at will as long as the sproc returns the same columns. -- 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
Re: [PHP] Re: Stored Procedures
>Stored procedures are like any other type of programming construct. >You can do them right or you can do them wrong. Yeah, and GOTO is perfectly fine... In the right place. >When making a stored >procedure you should stick to ansi SQL as much as possible. All my SQL was so dirt-simple, it couldn't have been non ANSI. select name, skill from applicants where job_id = $job_id >Most of >my stored procedures I can move from a SQL Server 2000 box to an Oracle >8i box with not problems at all. Oh they *MOVED* okay. Now update the Stored Procedures for sub-section A of your on-line application on the Development box, and try to push the changes through to the Production Server. So, which Stored Procedures got changed? You have to keep track of them all, cuz MS sure doesn't. >Stored procedures are NOT over head. They are a *LOT* of painful administrative overhead, and their gains are mythical, not real. >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. Bullshit. If I have to change an SQL statement, I know right where it is, right where it belongs, right in the code. >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. Do you have *ANY* idea how quickly: "select * from MyTable" can be parsed and an execution plan selected?! It's CHUMP CHANGE in time. *ONLY* if your SQL is so incredibly complicated that you can't even understand it will the parse/compile time of SQL be a factor in performance. >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. *NOTHING* else changed, except you went to stored procedures?... I'm from Missouri. Show me. >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. I only have to change one SQL in one location, and everything is fine. -- 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
Re: [PHP] Re: Stored Procedures
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 PRO
Re: [PHP] Re: Stored Procedures
Sorry, didn't think they did. Does someone have an example, I have never seen one. -- _ Mark McCulligh, Application Developer / Analyst Sykes Canada Corporation www.SykesCanada.com (888)225-6824 ex. 3262 [EMAIL PROTECTED] "B I G D O G" <[EMAIL PROTECTED]> wrote in message 009201c2245d$9638ab90$[EMAIL PROTECTED]">news:009201c2245d$9638ab90$[EMAIL PROTECTED]... > Actually, > > Postgres supports stored procedures, triggers and views to name a few where > mysql does not... > > B i g D o g > > > > - Original Message - > From: "Mark McCulligh" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, July 05, 2002 1:45 PM > Subject: [PHP] Re: Stored Procedures > > > > I think postgres doesn't support store procedure / triggers. Just like > > MySQL doesn't. > > > > Mark. > > > > -- > > _ > > Mark McCulligh, Application Developer / Analyst > > Sykes Canada Corporation www.SykesCanada.com > > [EMAIL PROTECTED] > > > > "David Busby" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > List, > > > 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? > > > > > > /B > > > > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Re: Stored Procedures
MySQL does in 4.0, but that's in alpha right now. -Original Message- Postgres supports stored procedures, triggers and views to name a few where mysql does not... This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete the message. Thank you very much. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Stored Procedures
Actually, Postgres supports stored procedures, triggers and views to name a few where mysql does not... B i g D o g - Original Message - From: "Mark McCulligh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, July 05, 2002 1:45 PM Subject: [PHP] Re: Stored Procedures > I think postgres doesn't support store procedure / triggers. Just like > MySQL doesn't. > > Mark. > > -- > _ > Mark McCulligh, Application Developer / Analyst > Sykes Canada Corporation www.SykesCanada.com > [EMAIL PROTECTED] > > "David Busby" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > List, > > 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? > > > > /B > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
