Which are the RDBMS that minimize the server roundtrips? Which RDBMS are better (in this area) than MS SQL?

Question

IMPORTANT NOTE: I recieved many answers and I thank you all. But all the answers are more comments than answers. My question is related on the number of roundtrips per RDBMS. An experienced person told me that MySQL has less roundtrips than Firebird. I would like that the answer stays in the same area. I agree that this is not the first thing to consider, there are many others (application design, network settings, protocol settings...), anyway I 'd like to recieve an answer to my question, not a comment. By the way I found the comments all very useful. Thanks.

When the latency is high ("when pinging the server takes time") the server roundtrips make the difference.

Now I don't want to focus on the roundtrips created in programming, but the roundtrips that occur "under the hood" in the DB engine+Protocol+DataAccessLayer.

I have been told that FireBird has more roundtrips than MySQL. But this is the only information I know.

I am currently supporting MS SQL but I'd like to change RDBMS, so to make a wise choice I would like to include also this point into "my RDBMS comparison feature matrix" to understand which is the best RDBMS to choose as an alternative to MS SQL.

So the bold sentence above would make me prefer MySQL to Firebird (for the roundtrips concept, not in general), but can anyone add informations?

And MS SQL where is it located? Is someone able to "rank" the roundtrip performance of the main RDBMS, or at least: MS SQL, MySql, Postegresql, Firebird (I am not interested in Oracle since it is not free, and if I have to change I would change to a free RDBMS).

Anyway MySql (as mentioned several times on stackoverflow) has a not clear future and a not 100% free license. So my final choice will probably dall on PostgreSQL or Firebird.

Additional info:

somehow you can answer my question by making a simple list like:

MSSQL:3;

MySQL:1;

Firebird:2;

Postgresql:2

(where 1 is good, 2 average, 3 bad). Of course if you can post some links where the roundtrips per RDBMSs are compared it would be great

Update:

I use Delphi and I plan to use DevArt DAC (UNIDAC), so somehow the "same" Data Access component is used, so if there are significant roundtrip differences they are due to the different RDBMS used.

Further update:

I have a 2 tier application (inserting a middle tier is not an option), so by choosing a RDBMS that is optimized "roundtrip-side" I have a chance to further improve the performance of the application. This kind of "optimization" is like "buy a faster internet connection" or "put more memory on the server" or "upgrade the server CPUs". Anyway also those "optimizations" are important.

Answer 1

Sometimes the "roundtrips" are also in the protocol or data access layer, not the "DB engine"

Answer 2

I can't give round trip details but i was in a very similar situation a while back when i was trying to find alternatives to MS SQL due to budgeting. myself and 4 others spent some time comparing MySQL, Postgres, and FireBird.

Having worked with MySQL for a long time we quickly ruled it out for most of our larger projects. The decision fell between Postgres and FireBird. One thing just starting off was the lack of popular support/documentation with FireBird in contrast to Postgres. Our bench tests always either had Postgres on top or on level with FireBird, never under. In terms of features; Postgres again answered our needs while FiredBird had us needing to come up with creative solutions.

Below is a feature comparison chart. i'll admit it is now a bit dated but still very helpful:

Here is also a long forum thread discussing the difference

Good luck!

Answer 3

Why are you concentrating on roundtrips? Normally they shouldn't affect your performance unless you had a very slow and unreliable network. For example, the difference between ODBC and OLEDB drivers for any database is nearly an order of magnitude in favor of OLEDB.

If you go to either MySQL or Firebird using ODBC instead of OLEDB/ADO.NET drivers you incur an overhead several orders of magnituted greater than the roundtrips you might save.

Answer 4

I will not rank the client-server DBMS's from the roundtrips side. There are a lot of options to make one DBMS the best (ask SQL Server to use the default cursor), and other the worse (create an Oracle cursor with nested datasets).

What you are looking for is, probably, the general approach, oriented on the trafic minimization and the independent work of a client from a server. That are the middle-tier data access libraries.

So, if your application is so sensitive to the trafic optimization, then look for such libraries like the DataAbstract, kbmMW or ThinDAC.

Answer 5

How your application is coded and how and when data are accessed and transferred have a much greater impact in slow connection or high latency situations than the db network protocol itself. Some database protocols may be tuned to better work in uncommon scenarios, i.e. increasing or decreasing the the data packet size. You may also encounter slow down at the TCP/IP layer itself, which could require TCP/IP tuning as well.

Answer 6

Until v2.1, Firebird certainly creates more traffic than MS SQL Server. I have a friend which developed a MSSQL C/S application here in Brazil where the db is hosted in a datacenter. The client apps runs from many stores directly connecting on server over VPN/Internet using end-user broadband connections (1Mbps, mostly) for 5+ years and no trouble with it. The distances involved range from few hundred to thousands of kilometers from datacenter.

After v2.1, I can't figure out if this remains true, because I haven't made a fair comparison since and Firebird's remote protocol had been changed to optimize network traffic on slow connections. More on FirebirdSQL site.

Can't say on PostGres ou MySQL, since I didn't used any.