On 3/1/06 7:59 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
>> 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.
>
> Hi Marco,
>
> Thanks for the insight in the test suite.
>
> I hate the flame wars too and will shut up about this until you have
> that test suite/app ready. Right now as a first impression I can
> only say that 3 seconds in PostgreSQL for that last SELECT query for
> example seems impossibly high. There are only one million records in
> the biggest table. Intuitively I would think it should be close to
> "instantaneous" as a human perception, and 3 seconds is a "long wait"
> as a human perception.
>
> It really does sound like there was either something wrong with your
> PostgreSQL setup or you didn't properly use PostgreSQL's features
> that allow to improve this. We may dig into this once your test
> suite/app is ready.
Hi Marc,
I also think that these numbers are wrong.
SqlLite have made: 3 table JOIN + GROUP BY + SUM()
On million records in 0.5 second ?????
I have did query on ONE table with million records with GROUP BY it takes
100+ seconds for SqlLite on my Dual G5/2.0
---------------
Also I want underline, that SqlLite is SINGLE-USER engine in its heart.
When I have check its sources and algorithms, they
a) do not make record locks
b) they often do lazy search
c) they do lazy join it seems
This means that they can find only first triplet of join record and return
back. So IT LOOKS like query have finish. But this is not true obviously.
Not bad yes? :-)
But we cannot use such trick in Valentina for example. And I believe mySQL
and Postgre also do not use it and will never use. This trick can work while
this is single-user engine. But this will lead to problems with multi-user.
May be of course I am wrong. And they have really cool algorithms which I
read but do not catch because I am blind?! :-)
Frankly saying I more believe to my tests. Because I know where is really
hard things for dbs are.
About mySQL speed on that query...may be guys have forget to index filed?
--
Best regards,
Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc
Valentina - Joining Worlds of Information
http://www.paradigmasoft.com
[I feel the need: the need for speed]
_______________________________________________
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>
