Hi All,
Thanks for all the help here. Sorry for the late update but we've found our problem and fixed it already. Prior to looking at the translated code more intently, I wanted to make sure that our environmental settings were acceptable and the various emails from members have confirmed that...

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";
---------------------------------------------------

In T-SQL, the performance is the same whether the last two lines are there or not...

In plpgsql, this is not the case the from and where clauses are not necessary and probably creates an internal (rather useless and time consuming) inner join in plpgsql which accounts for the original performance issue.

I'm happy (actually ecstatic) to report that Win2kPro + PG performance is slightly faster than Win2kPro + MSSQL/MSDE.

Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro + MSSQL/MSDE for our stored functions.

Thanks for all the help! Am a believer now.

Robert Bernabe wrote:
> I'm happy (actually ecstatic) to report that Win2kPro + PG performance
> is slightly faster than Win2kPro + MSSQL/MSDE.
>
> Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro +
> MSSQL/MSDE for our stored functions.
>
> Thanks for all the help! Am a believer now.

That's great news Robert - thanks for sharing!

Regards, Dave.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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";
> ---------------------------------------------------

Those lines are not totally useless from DB point of view. If there is
no rows that match the join, the WHERE clause will be false, and no rows
will be updated. So I'm sure MS SQL doesn't ignore those lines, but does
use a more clever plan. Perhaps it stops processing the join as soon as
is finds a match, while we perform the whole join, for example.

> In T-SQL, the performance is the same whether the last two lines are there or not...
>
> In plpgsql, this is not the case the from and where clauses are not necessary and probably creates an internal (rather useless and time consuming) inner join in plpgsql which accounts for the original performance issue.

You can check the access plan with EXPLAIN.

> I'm happy (actually ecstatic) to report that Win2kPro + PG performance is slightly faster than Win2kPro + MSSQL/MSDE.
>
> Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro + MSSQL/MSDE for our stored functions.
>
> Thanks for all the help! Am a believer now.

Nice to hear .

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>>> 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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend