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. 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 '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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Andy Colson Sent: Tuesday, December 07, 2010 2:23 PM To: Kenneth Marshall Cc: Richard Broersma; Justin Pitts; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
