Re: Bet the Business
I would have to agree with you. The kinds of applications you mentioned need every scrap of speed possible. All I was saying is that stored procedure use is a trade off of flexibility vs. performance. In many applications the flexibility is more important than the performance gain and that the gain will be minor in many kinds of applications. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
The fundamental reason for using stored procedures is performance. Stored procedures are compiled code. This means the database has reviewed the SQL, came up with the most efficient plan of action (often sorting through thousands of permutations when multiple table joins are concerned), and (given the right directives from the DBA) has this plan of action cached in memory, waiting to be called by name (optionally with parameters). When you issue your SQL directly from the application or business tier rather than use a stored procedure - you are issuing what's called dynamic SQL. The process of coming up with a plan of action for this SQL repeats itself every time the SQL is issued and is very expensive. In sophisticated transaction system it's common to see a 1000-fold improvement in the number of transactions per second processed - when switching from dynamic SQL to stored procedures. This is a big deal. On a small application with simple SQL and not much action - it's less of an issue. On an airline reservation or amazon type situation - stored procedures are an absolute must, and yes, they tie you to the specific database at hand because every database's stored procedure language is different (that's one way for the vendors to make it hard to switch). No pain no gain. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
The concept of n-tier design is mainly one of flexibility. A proper n-tier design should allow you to change either the front-end, business logic or back end (database) with no (or minimal) changing of the other layers. This means that the user interface (front end) is one layer (local exe, web page, etc). This communicates with the business logic layer (a set of dlls usually). The business layer then communicates with the database (back end). Sometimes another layer is inserted between the business logic and back end to translate for a specific database. The front end never directly comunicates with the data so it doesn't care what database you use. The business logic layer should use some generic API (like ADO/ODBC) so that it (for the most part) doesn't care what database that you use or what front end you use. The database should only store data so it doesn't care how the data was entered or what logic it went through before being stored. While this gives a lot of flexibilty you can sometime sacrifice performance as many back ends offer performance gains by integrating more tightly with it. This is what stored procedures are: business logic that is embedded in the data layer. You are basically trading n-tier for 2-tier to acheive a (often slight) performance gain and locking yourself inst a specific data source. In this 2-tier model changing your data source forces a change (re-write) of the business logic layer (which actually no longer exists). Sorry to be so long winded. I would use stored procedures if the performance gain was significant or if it was demanded by the customer but not otherwise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Quoting Mike <[EMAIL PROTECTED]>: > One thing to remember is that every stored procedure you execute steals > resources that would otherwise go to handling queries, so you do not > necesscarily see a performance boost by using stored procedures for > everything, in fact a MySQL server handling a lot of stored procedures could > very well show poorer performance than a proper n-tier application. > People have said this a few times, and I'm showing my ignorance level here - but a lot of the n-tier documentation I've seen (allbeit MS and probably dubious) has the data tier as an SQL Server full of stored procedures which is called from a business logic tier. Now, if I don't have stored procedures I'm basically making the same SQL calls (or different ones, but still SQL calls) from queries formed in my business layer - so I'm unsure how removing stored procedures helps as your still hitting the database. I'm new so I am probably missing something, I'm just interested in the details of how removing stored procedures might cause performance increase? Is it because you've moved logic, rather than data access, away from the stored procedure? I must admit, in my limited capacity, most of my simple apps so far have involve basic selects, inserts, updates, etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
>So what are the justifications? What makes a wise stored procedure >and an unwise stored procedure? Use them when there is a real benefit to using stored procedures. I will give one example when I would have used them: With a CRM solution I once wrote, I wanted to get a list of n randomly selected customers to issue a survey to. The generation and retreival of the customers was fairly painless, you simply add ORDER BY RAND() to your query with a LIMIT n clause. The catch is that I also wanted to add a row to a log table for each of these people to show that thay were going to be part of this particular random mailing. Now of course at this point these people were going to be on the client side. The solution is to SELECT the random people into a temporary table, then mangle the table to be able to INSERT it into the log table. If I would have had access to stored procedures, I would have had the stored procedure retrieve the people and both return them to the client app AND INSERT a row to the log, saving me time and trouble. One thing to remember is that every stored procedure you execute steals resources that would otherwise go to handling queries, so you do not necesscarily see a performance boost by using stored procedures for everything, in fact a MySQL server handling a lot of stored procedures could very well show poorer performance than a proper n-tier application. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
All IMHO of course... > > Will I use them in MySQL 5.0? Probably, but only when justified. > > > > > > So what are the justifications? What makes a wise stored procedure and an > unwise stored procedure? I've used them a lot with Oracle. The n-tier days weren't really alive when I was doing that project. We had several reasons to use Stored Procedures: 1) the same set of complicated processing to be called by multiple applications and updating that particular piece of code without having to update the (client) apps 2) dragging a lot of data across the network was slow. With a stored procedure, the processing was A LOT faster. 3) without a middle tier: having to use a particular piece of code in different programming languages 4) Oracle Jobs require a single statement or procedure to run. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Original Message - From: "robert_rowe" <[EMAIL PROTECTED]> > > Will I use them in MySQL 5.0? Probably, but only when justified. > > So what are the justifications? What makes a wise stored procedure and an unwise stored procedure? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Back to stored procedures for a second. I use them with MSSQL. For certain tasks they greatly improve performance because I don't have to move all the data client side. For the vast majority of tasks though stored procedures yeild little performance gain. I've seen systems that (designed as Microsoft suggests) wrapped all SQL usage in stored procedures. This is nonsense. Also, stored procedure have two very important liabilities: they break the n-tier model and they expose source code to the customer (if you sell your app). If you change your back end then you get to re-write all your stored procedures (I had to do this once). I routinely get calls from IT managers who have looked at my stored procedures and have questioned why I did so and so. This forces me to research it and come up with a reasonable answer. In my opinion the performance gain of stored procedures are rarely worth the headaches so their lack of them in MySQL is really not a problem. Will I use them in MySQL 5.0? Probably, but only when justified. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
> Obviously, the details are vague here, I was just wondering if anyone had > any stories, personal ones, rather than the press announcements, of adopting > MySQL for line of business, critical stuff - what it involved, how it went, > what issues they faced in getting it accepted and so on. > > I'm genuinly interested. We started a web site with MySQL in 1999 or so. The site ran on a modest little server, sharing it with dozens of other web sites. With success traffic grew and we always figured in 6 months MySQL would never be able to handle the load and we'd have to shell out mega bucks and mega pain for something like Oracle. Our site generated perhaps 5 queries a second back in the day. Every time performance started to drop we've always been able to solve the problem more easily than imagined. Besides throwing hardware at it (which can only go so far), we could always better optimize a query, do some kind of preprocessing, tweak some database settings, etc. I've stopped worrying. It's 2004 and our single server now processes 2000+ queries a second, and the site today is just as fast as it was in 1999 (fast!) What I like best about MySQL is not all of the features that it comes shrink-wrapped with, but that with a little creativity and a few hours of work you can get it to do almost anything you want. MySQL finds a good balance between what the RDBMS should do and what it should leave alone. Maybe this was an accident, maybe it was a sign of technical brilliance--ignoring marketing demands to achieve technical superiority. Regardless, it's to a great effect. I suspect all of the clever and hard work in the world won't get MS SQL Server to do the unconventional. Also, insert obligatory benefits of open source boilerplate here. -- Michael Bacarella24/7 phone: 1-646-641-8662 Netgraft Corporation http://netgraft.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
As I understand stored procedures: Stored procedures offer a level of performance that you normally can not replicate in code. The stored procedure actually has the query stored in the query parser ready to rumble. You do not have to invoke network or socket overhead in calling a long query and you do not have to invoke the query parser at each invocation of the procedure. Hopefully, the procedure takes advantages of the caches found in most database servers. Theoretically, the performance gains are tremendous if you can knock out network access and the CPU cycles of parsing the query. That being said, I am a fan of MySQL and would use it over MSSQL any day. Not having sub-selects, stored procedures, and until recently transaction support I have found programmatic methods to get around these issues. My experience has been this - let the application that is closest to the data do the manipulation. This is true if the primary purpose is to store, retrieve, and manipulate data. So, when MySQL supports stored procedures you will see, again, a jump in usability and performance. HTH Quoting [EMAIL PROTECTED]: > Quoting robert_rowe <[EMAIL PROTECTED]>: > > > > > I'm lead developer for a company that writes custom software for the > mining > > industry. We support MSSQL and MySQL. I've found that from a programming > > aspect (VB + ADO) there is relatively little difference between MSSQL and > > MySQL. There is some sight syntax differences and MySQL versions < 5.0 do > not > > support stored procedures. We use the InnoDB table type for MySQL as it > > provides row level locking and transactions. Our largest client has about > 1 > > gig of data and averages 125 users. I've found that MySQL usua > > lly out performs MSSQL if you tune it properly and use good programming > > techniques. It is less integrated with Microsoft products though so if > your > > clients will be accessing the data via MS Office applications then MSSQL > will > > seem easier. We offer both platforms mainly because a lot of IT managers > are > > convinced that Microsoft solutions are the best even when benchmarks say > > different. > > > > I admit to dome degree I am one of the IT Managers - the it 'sounds to good > to > be true' syndrome I suppose. But I'm coming around. The decision will be for > MSSQL Server due to us using other MS products and the supporting of one > product, but I'm interested for future reference when it does become an > option > (probably other jobs). > > As an aside, stored procedures seem to be a big thing with some people, > namely > the MS people I encounter (the ASP.NET mantra of using stored procedures for > all > databases access and even processing tasks), yet people seem to get along > with > them fine, until recently, in MySQL. > > This makes me thing they may not be the holy grail people say they are...in > MySQL, until recently, all SQL must have been done at the code level rather > than > at the database server level - is that a major issue? Does it even provide > some > advantages? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Bet the Business
I've found, in my many years of app dev work with Oracle, MSSQL, and now a little MySQL, that stored procedures are a very mixed blessing. Depending on your application architecture, they be just what you want, a means of encapsulating a complicated, data-centric function. Database stored procedures are notiriously difficult to manage in terms of version management, mostly due to the advance of excellent GUIs for editing stored procedures directly in the DB (TOAD). The other major drawback is that unless your entire application in based in the database (e.g. Oracle's web toolkit) then it adds another language to your application, as well as another location for code. This obviously increases maintainance time/cost, which is acceptable sometimes, but not others. I am definately _for_ stored procedures. Especially in MySQL -- between SP and subqueries, most of the limiting features of MySQL are going away, and Oracle and MSSQL folks will have less and less ground from which to point fingers. Just remember that they are not a magic bullet, and try not to mix too much of your application and/or business logic directly in them. [stepping down from soap box] > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 06, 2004 10:01 AM > To: [EMAIL PROTECTED] > Subject: Re: Bet the Business > > > Quoting robert_rowe <[EMAIL PROTECTED]>: > > > > > I'm lead developer for a company that writes custom > software for the mining > > industry. We support MSSQL and MySQL. I've found that from > a programming > > aspect (VB + ADO) there is relatively little difference > between MSSQL and > > MySQL. There is some sight syntax differences and MySQL > versions < 5.0 do not > > support stored procedures. We use the InnoDB table type for > MySQL as it > > provides row level locking and transactions. Our largest > client has about 1 > > gig of data and averages 125 users. I've found that MySQL usua > > lly out performs MSSQL if you tune it properly and use good > programming > > techniques. It is less integrated with Microsoft products > though so if your > > clients will be accessing the data via MS Office > applications then MSSQL will > > seem easier. We offer both platforms mainly because a lot > of IT managers are > > convinced that Microsoft solutions are the best even when > benchmarks say > > different. > > > > I admit to dome degree I am one of the IT Managers - the it > 'sounds to good to > be true' syndrome I suppose. But I'm coming around. The > decision will be for > MSSQL Server due to us using other MS products and the > supporting of one > product, but I'm interested for future reference when it does > become an option > (probably other jobs). > > As an aside, stored procedures seem to be a big thing with > some people, namely > the MS people I encounter (the ASP.NET mantra of using stored > procedures for all > databases access and even processing tasks), yet people seem > to get along with > them fine, until recently, in MySQL. > > This makes me thing they may not be the holy grail people say > they are...in > MySQL, until recently, all SQL must have been done at the > code level rather than > at the database server level - is that a major issue? Does it > even provide some > advantages? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Quoting robert_rowe <[EMAIL PROTECTED]>: > > I'm lead developer for a company that writes custom software for the mining > industry. We support MSSQL and MySQL. I've found that from a programming > aspect (VB + ADO) there is relatively little difference between MSSQL and > MySQL. There is some sight syntax differences and MySQL versions < 5.0 do not > support stored procedures. We use the InnoDB table type for MySQL as it > provides row level locking and transactions. Our largest client has about 1 > gig of data and averages 125 users. I've found that MySQL usua > lly out performs MSSQL if you tune it properly and use good programming > techniques. It is less integrated with Microsoft products though so if your > clients will be accessing the data via MS Office applications then MSSQL will > seem easier. We offer both platforms mainly because a lot of IT managers are > convinced that Microsoft solutions are the best even when benchmarks say > different. > I admit to dome degree I am one of the IT Managers - the it 'sounds to good to be true' syndrome I suppose. But I'm coming around. The decision will be for MSSQL Server due to us using other MS products and the supporting of one product, but I'm interested for future reference when it does become an option (probably other jobs). As an aside, stored procedures seem to be a big thing with some people, namely the MS people I encounter (the ASP.NET mantra of using stored procedures for all databases access and even processing tasks), yet people seem to get along with them fine, until recently, in MySQL. This makes me thing they may not be the holy grail people say they are...in MySQL, until recently, all SQL must have been done at the code level rather than at the database server level - is that a major issue? Does it even provide some advantages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
I'm lead developer for a company that writes custom software for the mining industry. We support MSSQL and MySQL. I've found that from a programming aspect (VB + ADO) there is relatively little difference between MSSQL and MySQL. There is some sight syntax differences and MySQL versions < 5.0 do not support stored procedures. We use the InnoDB table type for MySQL as it provides row level locking and transactions. Our largest client has about 1 gig of data and averages 125 users. I've found that MySQL usually out performs MSSQL if you tune it properly and use good programming techniques. It is less integrated with Microsoft products though so if your clients will be accessing the data via MS Office applications then MSSQL will seem easier. We offer both platforms mainly because a lot of IT managers are convinced that Microsoft solutions are the best even when benchmarks say different. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Ian ORourke said: > > In the near future we face making a decision for a database backend, > and the logical choice for us is MSSQL as we have one server already > for Great Plains - but it would seem MySQL is much cheaper. Perhaps. But supporting 2 database servers sounds like the most expensive option to me, so the question is if you can completely replace MS SQL server. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Ian O'Rourke wrote: I've been playing with MySQL for a bit now (and it is playing) and I'm using it for a number of personal sites (ie not many users, and only I really post stuff). I've also adopted it at work, and we use it to dump Lotus Domino information into for more structured reporting. All is good. In the near future we face making a decision for a database backend, and the logical choice for us is MSSQL as we have one server already for Great Plains - but it would seem MySQL is much cheaper. This would be a line of business we application for around 200 people. Obviously, the details are vague here, I was just wondering if anyone had any stories, personal ones, rather than the press announcements, of adopting MySQL for line of business, critical stuff - what it involved, how it went, what issues they faced in getting it accepted and so on. I'm genuinly interested. We have most of our data in MySQL now. Our main database has about 20 tables, the biggest one being 500,000 rows of telecommunications data. This database has about 30 concurrent users, all connecting via MS Access 2002. Access puts considerable load on databases, even with a small number of users. Actually the reason I got MySQL in the door to start with was because SQL Server was starting to barf on some tables that a lot of users had open at once. MySQL ( and InnoDB in particular ) handles Access quite well. Our sales database has 4 people connecting via Access, and 3 people connecting through the web server. We have a number of other special-purpose databases for importing customer's data that regularly get 1,000,000 rows of data dumped in them. I've been slowly migrating our existing SQL Server database to MySQL, and am basically waiting on stored procedure support before I move our billing data ( we have some pretty complicated queries which use views & stored procedures to calculate billing info, which I _could_ do in MySQL, but would rather not until I have stored procedures ). Our migration has gone without a hitch at all. The performance seems to be about twice that of SQL Server 7's, and more so in some cases where the query cache makes it up to 10x faster. We are also running our web site: http://www.nusconsulting.com.au ( simple but effective ) off the same server - it's a LAMP server. Anyway, I'd say go for it, but use the production version. I say this not because the 4.1.x line is unstable, but because if something _does_ go wrong, you don't want to get caught out using an officially unstable version. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bet the Business
I've been playing with MySQL for a bit now (and it is playing) and I'm using it for a number of personal sites (ie not many users, and only I really post stuff). I've also adopted it at work, and we use it to dump Lotus Domino information into for more structured reporting. All is good. In the near future we face making a decision for a database backend, and the logical choice for us is MSSQL as we have one server already for Great Plains - but it would seem MySQL is much cheaper. This would be a line of business we application for around 200 people. Obviously, the details are vague here, I was just wondering if anyone had any stories, personal ones, rather than the press announcements, of adopting MySQL for line of business, critical stuff - what it involved, how it went, what issues they faced in getting it accepted and so on. I'm genuinly interested. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
