From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Heikki Linnakangas <heikki@enterprisedb.com>
To: Robert Bernabe <robert_bernabe@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)
Date: Fri, 28 Dec 2007 09:05:36 -0600
Message-ID: <4774BC60.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <4767917E.9050206@enterprisedb.com>
References: <712109.35451.qm@web51610.mail.re2.yahoo.com>
<4767917E.9050206@enterprisedb.com>
>>> On Tue, Dec 18, 2007 at 3:23 AM, in message
<4767917E.9050206@enterprisedb.com>, Heikki Linnakangas
<heikki@enterprisedb.com> wrote:
> Robert Bernabe wrote:
>> In a nutshell it seems that MS SQL allows bad T-SQL code by optimizing and
>> ignoring redundant/useless from and where clauses in an update statement
>> whereas plpgsql will execute exactly what the code is asking it to do...
>>
>> We had several update instances in the T-SQL code that looked like this :
>>
>> update "_tbl_tmp2"
>> set "LongBackPeriod" = (select count ("EPeriod") from "_tbl_tmp1" where
> "_tbl_tmp1"."Row" = "_tbl_tmp2"."Row");
>> --------------------------------------------------
>> from "_tbl_tmp2" tmp2, "_tbl_tmp1" tmp1
>> where tmp2."Row" = tmp1."Row";
>> ---------------------------------------------------
>
> I'm sure MS SQL doesn't ignore those lines, but does
> use a more clever plan.
Actually, this is what happens in the absence of a standard --
allowing a FROM clause on an UPDATE statement is an extension to
the standard. MS SQL Server and PostgreSQL have both added such an
extension with identical syntax and differing semantics. MS SQL
Server allows you to declare the updated table in the FROM clause
so that you can alias it; the first reference to the updated table
in the FROM clause is not taken as a separate reference, so the
above is interpreted exactly the same as:
update "_tbl_tmp2"
set "LongBackPeriod" = (select count ("EPeriod") from "_tbl_tmp1" where
_tbl_tmp1"."Row" = "_tbl_tmp2"."Row")
from "_tbl_tmp1" tmp1
where "_tbl_tmp2"."Row" = tmp1."Row"
PostgreSQL sees tmp2 as a second, independent reference to the
updated table. This can be another big "gotcha" in migration.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, heikki@enterprisedb.com, robert_bernabe@yahoo.com
Subject: Re: Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)
In-Reply-To: <4774BC60.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox