On 07/12/2010 7:43 PM, Andy Colson wrote:
On 12/7/2010 1:22 PM, Justin Pitts wrote:
Also, as a fair warning: mssql doesn't really care about
transactions, but
PG really does. Make sure all your code is properly starting and
commiting
transactions.
-Andy
I do not understand that statement. Can you explain it a bit better?
In mssql you can write code that connects to the db, fire off updates
and inserts, and then disconnects. I believe mssql will keep all your
changes, and the transaction stuff is done for you.
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.
As far as I know both MS SQL and and Postgres work just the same as
regards explicit and implicit (autocommit) transactions, only the
underlying storage/logging mechanisms are different.
Transactions shouldn't make ay real difference to the select/join
performance being complained about though. It's already stated that the
insert performance of postgres far exceeds SQL Server, which is my
experience also.
As already suggested, until we see the exact table definitions including
indexes etc. there's no real way to tell what the problem is. How many
rows are in the second table? It really shouldn't take that much time to
read 1000 rows unless you have a bizarrely slow hard disk.
It would be nice to eliminate any programmatic or driver influence too.
How does the SQL select execute in enterprise manager for mssql and psql
or pgadmin for postgres?
Cheers,
Gary.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
