|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
| Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
May 20th, 2003, 04:54 PM
|
||||
|
||||
|
MySQL -vs- Firebird SQL
Does anyone know the difference between these 2 databases? I'm particularly interested in features it has, performance and support by PHP. If I'm not mistaken one can connect to firebird by using the InterBase functions ?!?
Thanks for any information/links/whatever! 
__________________
There are 10 types of people in this world - those who understand binary and those who don't... PHP | MySQL | DevShed Forum Search | Google Search
|
|
#2
May 20th, 2003, 05:41 PM
|
||||
|
||||
|
Yes - you would use the ibase functions to connect to, insert and gather your results from PHP.
As for the difference between the two, Firebird is extremely feature rich (mysql isn't even in the same ballpark). Switching from MySQL to Firebird is a big leap forward. This is the full set of docs in pdf format (10 or so megs): http://www.ibphoenix.com/downloads/60All.zip
|
|
#3
May 22nd, 2003, 09:50 AM
|
|||
|
|||
|
Wouldn't some of those features slow down the queries for simple things? I mean maybe I am wrong... but even having the ability to add in triggers and other nice features like that, in theory, would at least slow down simple queries that were not even tied into triggers right? Maybe I am wrong on the select side but it seems on the insert or update side there would have to be some more things going on no?
|
|
#4
May 22nd, 2003, 05:52 PM
|
||||
|
||||
|
Quote:
Well, only if you are using them. There are numerous advantages to having features as stored procedures, triggers and events available. To give you an example, I am currently working on a large application that will be compatible with three different databases (FB, PG, MySQL); the application needs to keep logs of all database INSERT and DELETE statements. MySQL forces me to do this on the application side, which in turn creates quite a bit of extra work: Code:
/*------------------------------------
Insert Data Into Table
------------------------------------*/
"INSERT INTO products VALUES('','etc','etc','etc')";
/*------------------------------------
Insert Data Into Log Table
------------------------------------*/
"INSERT INTO logs VALUES('the log data')";
Whereas with db's that support triggers and such can fire off an event when the one INSERT statement (the first one above) is executed and thus execute the stored SQL code, effectively inserting the log record into the log table. BTW - sql code is faster than application code. So now my library files may look like this: Code:
object.mysql_product_engine.rfile (1500 lines of application code) object.fb_product_engine.rfile (1000 lines of application code) object.pg_product_engine.rfile (1000 line of application code) For me, this creates a little problem; the code varies quite a bit and in the end; makes it harder for me to create my documentation files - example: Developer Docs - FB and PG Product Engines Developer Docs - MySQL Product Engine As opposed to: Developer Docs - Product Engines (would cover all three) ------------------------------------------ Maybe Rycamor or Rod will bless us with there database knowledge and give us some better examples...
__________________
~ Joe Penn
|
|
#5
May 22nd, 2003, 06:33 PM
|
|||
|
|||
|
well in theory you could abstract all the database calls and just pass the database calls to a DB object which would then do the db stuff based on the database you are dealing with and if it is MySQL write out the insert and delete statements... but yes I see what you mean.
I had read that just having the features would slow it down even if you did not use them which seems a little silly but at the same time not. I certainly agree, in your case here, that having triggers helps the developer a lot. Now I never actually tested any of this, I was just listening to what others have said. I have always used MySQL on PC type servers and Oracle on the boxes that cost 250k.
|
|
#6
May 22nd, 2003, 06:45 PM
|
||||
|
||||
|
Quote:
A: Yes. Otherwise you wouldn't have coded it. Q: If the trigger doesn't do this essential work what does? A: The application does. The problem with the application handling the work done by the trigger is that this involves more roundtrip traffic across the network as the program does a series of discrete steps. Compare this to a single call across the network which fires off a series of server-side actions. The same amount of work is getting done but the trigger method is more efficient. You can also gain a performance benefit from the database server already having preparsed the trigger. The server will already know what the most efficient query plan is. If you allow the application to handle the trigger step via an SQL call, you can incur the overhead of the server having to parse the command and determine a query plan. Another advantage of triggers is that it prevents a user from bypassing business logic by using an ad-hoc query. If you do an insert from the command-line, the trigger and its logic gets run. If the trigger logic is in an application, then these steps don't get run by SQL commands outside the application. Stored procedures (SP's) offer many of the same benefits. I do a lot of fat client programming. If you keep your forms lightweight by putting the business logic server-side in an SP, you can change your business logic in one SP and every application gets it. If an application tries to submit bad data, the SP will raise an error that is returned to the client explaining what is wrong. You've made a "code change" without having to push X number of client applications out to workstations. Some people use SP's as an added layer of security. The DBA's don't give users or programmers access to the tables directly. They just give access to the SP's which forces the associated code to be run whenever the data is manipulated. You can also make your application code very portable using SP's. Even if SQL syntax is different in Oracle, DB2 and Firebird, as long as I put the SQL in a SP with the same name on each database, my application code doesn't have to change. The good thing about databases like Firebird is that I never have to use any of these features. But when certain problems arise, they're nice things to have in my toolbox.
|
|
#7
May 22nd, 2003, 06:58 PM
|
||||
|
||||
|
Lol - I will now change my previous reply a little
<me>Erasing last line of text in my last reply</me> <me>Writing the new line: Maybe Rycamor, Rod or dcaillouet (nick named dman) will bless us with there database knowledge and give us some better examples... </me>
|
|
#8
May 22nd, 2003, 10:51 PM
|
|||
|
|||
|
Kudos to dcaillouet for a nice clear explanation.
I will only add that there can be performance benefits to using stored procedures. There's not always a performance downside: Stored procedures are usually compiled, cached functions, so one SP can often perform a group of related queries faster than if you 1. begin a transaction, 2. execute your queries, and 3. commit the transaction. And, as dcaillouet mentioned, those queries are all happening in one environment, atomically, rather than travelling to the SQL system from the app environment. So, when performing a complex operation, you make one DB call from your application environment rather than 4 or five, for example. In fact, even if you just use a stored procedure to handle one query, that might be faster than a standard application SQL query, because the SQL query plan itself can be cached. Also, as to the question of database features, it's funny to me how often programmers immediately raise the red flag about performance. The amount of application code you save by having advanced logical features is amazing, once you get the hang of it. There are just some things that make so much more sense in a relational query mode. (not everything, I understand). For example views, procedures, CHECK constraints, and domains-- when you get the feel for these, you will be amazed at the coolness factor. These go a long, looooong way toward making a database management system feel more "object-oriented". Each of these allows for a powerful type of encapsulation, and even a sort of inheritance, if you learn to play with them. (And the future looks even better. With the more advanced RDBMS systems that www.thethirdmanifesto.com talks about, inheritance and abstract datatypes will be built into the system from the ground up). So don't view these features as "trade-offs to performance". View them as ways to accomplish more with less work. Programmer performance is worth far more than system performance ;-).
__________________
The real n-tier system: FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL Amazon wishlist -- rycamor (at) gmail.com
|
|
#9
May 22nd, 2003, 11:24 PM
|
||||
|
||||
|
Quote:
Fortunately a lot of the systems I'm currently working on are fairly small (10's of gigs). On these I'm always willing to give up a few milliseconds of performance to have the constraints, checks, domains and other server-side objects that make database programming more robust. I like Firebird simply because it runs on all the platforms I program on and does everything I need really well. Also I find it easy to maintain. Fortunately for me, with release 8.1, DB2 has become a lot easier to work with. Still, I'd rather work with Firebird. Last edited by dcaillouet : May 23rd, 2003 at 12:00 AM.
|
|
#10
May 25th, 2003, 01:36 AM
|
|||
|
|||
|
Licencing
Of cause the main advantage of Firebird is the lack of any Licencing considerations if you are using it comercially.
I run local copies on all sites, and replicate from the master site, without having to worry
|
|
#11
May 25th, 2003, 02:09 AM
|
|||
|
|||
|
Let me add my two bits into this debate
Comparing MySQL with Firebird is like comparing dBaseIII with Oracle... True MySQL offers great speed and has a wider market share, but it is suitable only for Web-Applications and single user applications... Let me compare the features MySQL Firebird ---------------------- -------------------- SQL-92 compliance very small subset most of it Relational Integrity new in V4.0 Yes Stored Procedures No(plan for V 5.0) Yes Triggers No(plan for V 5.0) Yes As for Performance, MySQL will outperform Firebird on almost all tests on local machine...... On a Network Server a properly designed Firebird Server will outperform MySQL on any Test and the network-Traffic generated by Mysql will be many times higher than Firebird So you decide the one you need according to your needs .....
|
|
#12
May 25th, 2003, 02:12 AM
|
|||
|
|||
|
Whoops!!!! I see this forum reformats my reply.. so the feature comparison is as follows
MySQL ---------------------- SQL-92 compliance ----- very small subset Relational Integrity ----- new in V4.0 Stored Procedures ----- No(plan for V 5.0) Triggers ----- No(plan for V 5.0) Firebird ---------------------- SQL-92 compliance ---- most of it Relational Integrity ---- Yes Stored Procedures ----- Yes Triggers ----- Yes
|
|
#13
May 28th, 2003, 06:41 AM
|
||||
|
||||
|
PostgreSQL
---------------------- SQL-92 compliance ---- most of it Relational Integrity ---- Yes Stored Procedures ----- Yes Triggers ----- Yes What are differences between Firebird and PostgreSQL? Why are there many people still using MySQL? What is the criteria to select one amongst three of them?
|
|
#14
May 28th, 2003, 09:43 AM
|
|||||
|
|||||
|
Quote:
Quote:
http://www.mysql.com/doc/en/Features.html http://www.postgresql.org/users-lounge/features.html http://firebird.sourceforge.net/ind...id=ib6_overview http://firebird.sourceforge.net/ind...ib6_newfeatures http://firebird.sourceforge.net/ind...id=ib6_techspec Quote:
http://www.borland.com/interbase/pdf/ib7_feaben.pdf
|
|
#15
May 28th, 2003, 11:55 AM
|
||||
|
||||
|
Thanks a lot for detail explanation.
I think I should fully test Firebird SQL and PostgreSQL as soon as possible so that I could persue my boss to change from MySQL.
__________________
------------------------------------------ Perl Kids Kiss Perl Stanley ------------------------------------------
|
|
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > MySQL -vs- Firebird SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
