Marc, these numbers are true.
Each "test" is comprised of 13 queries, 2 inserts (one insert
inserted a single record and the other insert inserted 1000 records),
a delete, and an update, for a total of 17 SQL statements sent 20
times to the same db.
In the db there are four tables: widgets, customers, purchase_orders,
and purchase_order_items. Each purchase_order has some number of
purchase_order_items on it. Each purchase_order_item is 1-10 of the
same widget. A purchase_order has a date, a customer id, and 1-4
purchase_order_items. The total data consists of 50 widgets, 250,000
customers, 500,000 purchase_orders, and 1,000,000 purchase_order_items.
These are the sql statements:
SELECT widget_id, quantity FROM Purchase_Order_Items WHERE quantity
between 4 and 6 ORDER BY quantity;
SELECT Purchase_Orders.purchase_order_id, customer_name,
Widgets.widget_id, widget_name FROM Purchase_Orders, Customers,
Purchase_Order_Items, Widgets WHERE order_date BETWEEN '2003-05-01'
AND '2003-06-01' AND Purchase_Orders.customer_id =
Customers.customer_id AND Purchase_Orders.purchase_order_id =
Purchase_Order_Items.purchase_order_id AND
Purchase_Order_Items.widget_id = Widgets.widget_id LIMIT 100;
SELECT order_date, SUM(quantity * widget_price)/100 as income FROM
Purchase_Orders, Purchase_Order_Items, Widgets WHERE order_date =
'2003-01-15' AND Purchase_Orders.purchase_order_id =
Purchase_Order_Items.purchase_order_id AND
Purchase_Order_Items.widget_id = Widgets.widget_id GROUP BY order_date;
INSERT INTO Purchase_Order_Items (purchase_order_item_id,
purchase_order_id, widget_id, quantity) VALUES (%i, 1, 1, 0);
UPDATE Purchase_Order_Items SET purchase_order_id=2, widget_id=2
WHERE quantity = 0;
DELETE FROM Purchase_Order_Items WHERE quantity = 0;
INSERT INTO Purchase_Order_Items (purchase_order_item_id,
purchase_order_id, widget_id, quantity) VALUES (%i, 1, 1, 0);
SELECT customer_name FROM Customers WHERE customer_id = 50000;
SELECT customer_id FROM Purchase_Orders WHERE order_date = '2003-01-15';
SELECT quantity FROM Purchase_Order_Items WHERE purchase_order_id =
64531 and widget_id = 11;
SELECT purchase_order_id FROM Purchase_Orders WHERE customer_id =
35675 and order_date between '2002-06-05' and '2002-06-06';
SELECT order_date, customer_name FROM Purchase_Orders, Customers
WHERE order_date > '2003-01-15' AND Purchase_Orders.customer_id =
Customers.customer_id LIMIT 10;
SELECT order_date as date, COUNT(Customers.customer_id) as customers
FROM Purchase_Orders, Customers WHERE order_date BETWEEN '2003-04-01'
AND '2003-05-01' AND Purchase_Orders.customer_id =
Customers.customer_id GROUP BY order_date;
SELECT customer_state as state, COUNT(Customers.customer_id) as
customers FROM Purchase_Orders, Customers WHERE order_date BETWEEN
'2003-04-01' AND '2003-05-01' AND Purchase_Orders.customer_id =
Customers.customer_id GROUP BY state;
SELECT widget_name, SUM(quantity) as count FROM Purchase_Order_Items,
Widgets WHERE quantity >8 AND Purchase_Order_Items.widget_id =
Widgets.widget_id GROUP BY widget_name ORDER BY widget_name;
SELECT customer_name FROM Customers WHERE customer_name LIKE 'z%'
LIMIT 250;
SELECT customer_name, customer_state FROM Customers WHERE
customer_name LIKE '%fr%' ORDER BY customer_state LIMIT 250;
SELECT widget_id, quantity FROM Purchase_Order_Items WHERE quantity
between 4 and 6 ORDER BY quantity;
SELECT Purchase_Orders.purchase_order_id, customer_name,
Widgets.widget_id, widget_name FROM Purchase_Orders, Customers,
Purchase_Order_Items, Widgets WHERE order_date BETWEEN '2003-05-01'
AND '2003-06-01' AND Purchase_Orders.customer_id =
Customers.customer_id AND Purchase_Orders.purchase_order_id =
Purchase_Order_Items.purchase_order_id AND
Purchase_Order_Items.widget_id = Widgets.widget_id LIMIT 100;
SELECT order_date, SUM(quantity * widget_price)/100 as income FROM
Purchase_Orders, Purchase_Order_Items, Widgets WHERE order_date =
'2003-01-15' AND Purchase_Orders.purchase_order_id =
Purchase_Order_Items.purchase_order_id AND
Purchase_Order_Items.widget_id = Widgets.widget_id GROUP BY order_date;
INSERT INTO Purchase_Order_Items (purchase_order_item_id,
purchase_order_id, widget_id, quantity) VALUES (%i, 1, 1, 0);
UPDATE Purchase_Order_Items SET purchase_order_id=2, widget_id=2
WHERE quantity = 0;
DELETE FROM Purchase_Order_Items WHERE quantity = 0;
INSERT INTO Purchase_Order_Items (purchase_order_item_id,
purchase_order_id, widget_id, quantity) VALUES (%i, 1, 1, 0);
SELECT customer_name FROM Customers WHERE customer_id = 50000;
SELECT customer_id FROM Purchase_Orders WHERE order_date = '2003-01-15';
SELECT quantity FROM Purchase_Order_Items WHERE purchase_order_id =
64531 and widget_id = 11;
SELECT purchase_order_id FROM Purchase_Orders WHERE customer_id =
35675 and order_date between '2002-06-05' and '2002-06-06';
SELECT order_date, customer_name FROM Purchase_Orders, Customers
WHERE order_date > '2003-01-15' AND Purchase_Orders.customer_id =
Customers.customer_id LIMIT 10;
SELECT order_date as date, COUNT(Customers.customer_id) as customers
FROM Purchase_Orders, Customers WHERE order_date BETWEEN '2003-04-01'
AND '2003-05-01' AND Purchase_Orders.customer_id =
Customers.customer_id GROUP BY order_date;
SELECT customer_state as state, COUNT(Customers.customer_id) as
customers FROM Purchase_Orders, Customers WHERE order_date BETWEEN
'2003-04-01' AND '2003-05-01' AND Purchase_Orders.customer_id =
Customers.customer_id GROUP BY state;
SELECT widget_name, SUM(quantity) as count FROM Purchase_Order_Items,
Widgets WHERE quantity >8 AND Purchase_Order_Items.widget_id =
Widgets.widget_id GROUP BY widget_name ORDER BY widget_name;
SELECT customer_name FROM Customers WHERE customer_name LIKE 'z%'
LIMIT 250;
SELECT customer_name, customer_state FROM Customers WHERE
customer_name LIKE '%fr%' ORDER BY customer_state LIMIT 250;
SELECT widget_id, quantity FROM Purchase_Order_Items WHERE quantity
between 4 and 6 ORDER BY quantity;
SELECT Purchase_Orders.purchase_order_id, customer_name,
Widgets.widget_id, widget_name FROM Purchase_Orders, Customers,
Purchase_Order_Items, Widgets WHERE order_date BETWEEN '2003-05-01'
AND '2003-06-01' AND Purchase_Orders.customer_id =
Customers.customer_id AND Purchase_Orders.purchase_order_id =
Purchase_Order_Items.purchase_order_id AND
Purchase_Order_Items.widget_id = Widgets.widget_id LIMIT 100;
SELECT order_date, SUM(quantity * widget_price)/100 as income FROM
Purchase_Orders, Purchase_Order_Items, Widgets WHERE order_date =
'2003-01-15' AND Purchase_Orders.purchase_order_id =
Purchase_Order_Items.purchase_order_id AND
Purchase_Order_Items.widget_id = Widgets.widget_id GROUP BY order_date;
INSERT INTO Purchase_Order_Items (purchase_order_item_id,
purchase_order_id, widget_id, quantity) VALUES (%i, 1, 1, 0);
UPDATE Purchase_Order_Items SET purchase_order_id=2, widget_id=2
WHERE quantity = 0;
DELETE FROM Purchase_Order_Items WHERE quantity = 0;
INSERT INTO Purchase_Order_Items (purchase_order_item_id,
purchase_order_id, widget_id, quantity) VALUES (%i, 1, 1, 0);
and these are times (in seconds) to complete the entire test:
PostgreSQL: 630
MySQL: 1299
REAL SQL Server: 347
we have individual time for each sql statement and for example this
query:
SELECT order_date, SUM(quantity * widget_price)/100 as income FROM
Purchase_Orders, Purchase_Order_Items, Widgets WHERE order_date =
'2003-01-15' AND Purchase_Orders.purchase_order_id =
Purchase_Order_Items.purchase_order_id AND
Purchase_Order_Items.widget_id = Widgets.widget_id GROUP BY order_date;
takes about 47 seconds on mySQL, 3 seconds on PostgreSQL and less
than half a second on REAL SQL Server.
I don't want to start a flame and to be really honest I hate raw
numbers, but I can say you that there will be an RB test suite/app
for the REAL SQL Server so you can try your own tests against other
databases and take your decision.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
On Mar 1, 2006, at 2:03 PM, Aliacta wrote:
Thanks to Brad, Ian, and Aaron for blogging about this.
The one thing that caught my attention of course is the statement
that REAL SQL Sever was
2x faster than PostgreSQL and 4x faster than MySQL across a suite
of tests.
I wonder who could possibly say this and keep a straight face
unless it was after those beer runs. ;-) These figures are simply
impossible which makes me curious about what exactly that "suite of
tests" did.
We all know the performance gap between PostgreSQL and MySQL can't
possibly be 200% so this impossibility can be extrapolated to the
performance gap with the REAL SQL Server. At the best the
measurement was just about the gap between RB's plugins. From an
RB centric point of view, this would still make sense though.
But then from an RB centric point of view, you ought to use the
best way to connect to PostgreSQL if there were an alternative to
the RB plugin <cough> pgSQL4RB <cough>.
And then I'm sure they didn't use the features of PostgreSQL either
for their tests. It wouldn't surprise me if I could run queries
that would return the same results as in the "test suite" and make
PostgreSQL spank the REAL SQL Server. But I wouldn't bet any money
on it either. ;-)
This is not to start another flame war on who has the biggest
peni... err, database. It's just that the figures given are simply
impossible. That's basically all I wanted to say.
The real figures should be so close to one another that different
"test suites" would come up with different performance rankings,
depending on who crafted said "test suites." The end results would
always be quite tight. 200 or 400% difference is ludicrous.
Cheers,
Marc
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
