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. :)
