Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/10 1:29 PM, Tom Polak wrote:
> What I was really after was a quick comparison between the two. I did not
> create anything special, just the two tables. One table SQL generated the
> records for me. I did not tweak anything after installing either system.

That's not a valid test. Postgres is NOT intended to be used out of the box. The default parameters aren't useful.

> There was a primary key on the ID field of both tables, no indexes though
> in either system. The second table had 1 record in it. The hardware it
> is running on is fairly good, dual Xeon CPUs, 4 GB of RAM, Raid 5. Btw,
> the cost for MS SQL 2008 R2 is ~$14,000 for 2 cpus,
> http://www.cdw.com/shop/products/default.aspx?EDC=2167810 . That is why I
> am pursuing this. :)
>
> Here is the ASP.net code that I was running
> Dim starttime As Date = Date.Now
> Dim endtime As Date
> Dim reader As NpgsqlDataReader
> Dim output2 As String = ""
>
>
> Dim oConn As New
> NpgsqlConnection("Server=192.168.1.5;Port=5432;Userid=postgres;Password=12
> 345;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encodin
> g=UNICODE;Timeout=15;SslMode=Disable;Database=tomtemp")
> oConn.Open()
> Dim x As Integer = 0
> 'For x = 0 To 1000 'uncomment to insert records.
> 'Dim command As New NpgsqlCommand("insert into pgtemp1(name,
> address, city, state) values ('Tom"& x& "','123"& x& " main
> st','rockford',1) ", oConn) 'meant for loop to put in 1,000 records in
> pgtemp1 table
> 'Dim command As New NpgsqlCommand("insert into pgtemp2(statename,
> stateid, other) values ('Illinois',1,'This is a lot of fun') ", oConn)
> 'only sends 1 record into the table pgtemp2
> 'command.ExecuteNonQuery()
> 'Next

You still haven't done an ANALYZE sql statement after filling your tables with data. You should execute "analyze pgtemp1" and "analyze pgtemp2" before you do any performance tests. Otherwise your results are meaningless.

Craig

>
> 'join table and read 1000 rows.
> Dim command As New NpgsqlCommand("select
> name,address,city,state,statename,stateid,other from pgtemp1 left join
> pgtemp2 on state=stateid", oConn)
> reader = command.ExecuteReader()
> While reader.read()
> output2 += "<tr><td>"& reader("name")& "</td><td>"&
> reader("address")& "</td><td>"& reader("city")& "</td><td>"&
> reader("statename")& "</td><td>"& reader("other")& "</td></tr>"
> End While
> oConn.Close()
> readeroutput.text =
> "<table><tr><td>Name:</td><td>Address:</td><td>City:</td><td>State</td><td
>> Other</td></tr>"& output2& "</table>"
>
> endtime = Date.Now
> Dim runtime As String
> runtime = endtime.Subtract(starttime).TotalSeconds
> output.text = starttime.ToString& " "& runtime
>
> The SQL is a straight convert from MS SQL code. I did not tweak either
> system.
>
>> From EXPLAIN ANALYZE I can see the query ran much faster.
> "Nested Loop Left Join (cost=0.00..138.04 rows=1001 width=1298) (actual
> time=0.036..4.679 rows=1001 loops=1)"
> " Join Filter: (pgtemp1.state = pgtemp2.stateid)"
> " -> Seq Scan on pgtemp1 (cost=0.00..122.01 rows=1001 width=788)
> (actual time=0.010..0.764 rows=1001 loops=1)"
> " -> Materialize (cost=0.00..1.01 rows=1 width=510) (actual
> time=0.000..0.001 rows=1 loops=1001)"
> " -> Seq Scan on pgtemp2 (cost=0.00..1.01 rows=1 width=510)
> (actual time=0.006..0.008 rows=1 loops=1)"
> "Total runtime: 5.128 ms"
>
> The general question comes down to, can I expect decent perfomance from
> Postgresql compared to MSSQL. I was hoping that Postgresql 9.0 beat MSSQL
> 2000 since MS 2000 is over 10 years old.
>
> Thanks,
> Tom Polak
> Rockford Area Association of Realtors
> 815-395-6776 x203
>
> The information contained in this email message is intended only for the
> use of the individual or entity named. If the reader of this email is not
> the intended recipient or the employee or agent responsible for delivering
> it to the intended recipient, you are hereby notified that any
> dissemination, distribution or copying of this email is strictly
> prohibited. If you have received this email in error, please immediately
> notify us by telephone and reply email. Thank you.
>
> Although this email and any attachments are believed to be free of any
> viruses or other defects that might affect any computer system into which
> it is received and opened, it is the responsibility of the recipient to
> ensure that it is free of viruses, and the Rockford Area Association of
> Realtors hereby disclaims any liability for any loss or damage that
> results.
>
>
> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Andy Colson
> Sent: Tuesday, December 07, 2010 2:23 PM
> To: Kenneth Marshall
> Cc: Richard Broersma; Justin Pitts; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows
>
> On 12/7/2010 2:10 PM, Kenneth Marshall wrote:
>> On Tue, Dec 07, 2010 at 11:56:51AM -0800, Richard Broersma wrote:
>>> On Tue, Dec 7, 2010 at 11:43 AM, Andy Colson<andy(at)squeakycode(dot)net>
> wrote:
>>>
>>>> In PG the first statement you fire off (like an "insert into" for
> example)
>>>> will start a transaction. ?If you dont commit before you disconnect
> that
>>>> transaction will be rolled back. ?Even worse, if your program does not
>>>> commit, but keeps the connection to the db open, the transaction will
> stay
>>>> open too.
>>>
>>> Huh - is this new? I always thought that every statement was wrapped
>>> in its own transaction unless you explicitly start your own. So you
>>> shouldn't need to commit before closing a connection if you never
>>> opened a transaction to begin with.
>>>
>>>
>>> --
>>> Regards,
>>> Richard Broersma Jr.
>>>
>>
>> The default of autocommit unless explicitly starting a transaction with
>> BEGIN is the normal behavior that I have seen as well.
>>
>> Cheers,
>> Ken
>
> Crikey! You're right. I need to be more careful with my assumptions.
>
> I maintain that people need to be more careful with pg transactions.
> I've seen several posts about "idle in transaction". But its not as bad
> as I made out. My confusion comes from the library I use to hit PG,
> which fires off a "begin" for me, and if I dont explicitly commit, it
> gets rolled back.
>
> sorry, it was confused between framework and PG.
>
> -Andy
>