pgSQL 存储过程比单独执行SQL慢故障

最近执行一个存储过程，速度奇慢，将单独的SQL拉出来执行速度很快，找了多方原因，判断原因是参数传递的原因。

解决方法 ：定义一个新的变量，将参数的值 赋给变量，速度有所改善

 

 附全部SQL 

CREATE OR REPLACE FUNCTION "public"."lr_flowmatch1"("tmpfilename" text, "flowmonth" text)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare 
    mysql  text ;
        filename text;

begin 
    --开始数据清洗；
    filename = tmpfilename;
        --1、根据 ddm_product_aliasbase 表来匹配产品名称；(这一行放第1执行速度奇慢)
        mysql = 'update '||$1 || ' as flowMe  set productid=b.productid,
                         productidname=b.productidname,productcode= b.product_code,
                         productsize = b.productsize,product_uomid=b.uomid,
                         product_uomidname=b.uomidname
                         from ddm_product_aliasbase as b
                         where  flowMe.from_accountid = b.from_accountid     and flowMe.product_alias_name = b.product_alias_name
                         and flowMe.product_alias_size = b.product_alias_size
                         and flowMe.product_alias_uom = b.product_alias_uom ';
                         RAISE NOTICE 'SQL语句1为： %', mysql;
                        execute mysql  using filename;    
        --2 根据ddm_account_aliasbase表来更新客户to_accountid等 信息；
    mysql = 'update '||filename  ||' as  flowMe set
                 to_account_classificationcode    =    b.to_account_classificationcode    ,
                to_account_classificationcode2    =    b.to_account_classificationcode2    ,
                to_account_classificationcodename    =    b.to_account_classificationcodename    ,
                to_accountid    =    b.to_accountid    ,                to_accountidname    =    b.to_accountidname    ,
                to_cityid    =    b.to_cityid    ,                to_cityidname    =    b.to_cityidname    ,
                to_countyid    =    b.to_countyid    ,                to_countyidname    =    b.to_countyidname    ,
                to_provinceid    =    b.to_provinceid    ,                owningorganizationid    =    b.owningorganizationid    ,
                owningorganizationidname    =    b.owningorganizationidname
                from ddm_account_aliasbase as b
                where b.from_accountid = flowMe.from_accountid 
                and flowMe.to_account_alias_name=      b.to_account_alias_name ';
                RAISE NOTICE 'SQL语句2为： %', mysql;
            execute mysql  ;


    --2 
    mysql = 'update '||filename  ||' as  flowMe set
                 to_account_classificationcode    =    b.to_account_classificationcode    ,
                to_account_classificationcode2    =    b.to_account_classificationcode2    ,
                to_account_classificationcodename    =    b.to_account_classificationcodename    ,
                to_accountid    =    b.to_accountid    ,                to_accountidname    =    b.to_accountidname    ,
                to_cityid    =    b.to_cityid    ,                to_cityidname    =    b.to_cityidname    ,
                to_countyid    =    b.to_countyid    ,                to_countyidname    =    b.to_countyidname    ,
                to_provinceid    =    b.to_provinceid    ,                owningorganizationid    =    b.owningorganizationid    ,
                owningorganizationidname    =    b.owningorganizationidname
                from ddm_account_aliasbase as b
                where b.from_accountid = flowMe.from_accountid 
                and flowMe.to_account_alias_name=      b.to_account_alias_name ';
                RAISE NOTICE 'SQL语句2为： %', mysql;
            execute mysql  ;
    
    --3根据客户主数据 accountbase 来清选客户
         mysql = 'update '||filename  ||' as  flowMe set 
                     to_account_classificationcode    =    b.accountclassificationcode    ,
                    to_account_classificationcode2    =    b.accountclassificationcode2    ,
                    to_account_classificationcodename    =    b.accountclassificationcodename    ,
                    to_accountid    =    b.accountid    ,
                    to_accountidname    =    b.name    ,
                    to_cityid    =    b.cityid    ,
                    to_cityidname    =    b.cityidname    ,
                    to_countyid    =    b.countyid    ,
                    to_countyidname    =    b.countyidname    ,
                    to_provinceid    =    b.provinceid    ,
                    owningorganizationid    =    b.owningorganizationid    ,
                    owningorganizationidname    =    b.owningorganizationidname    
                    from accountbase as b
                    where 
                    flowMe.to_account_alias_name =  b.name 
                    and flowMe.to_accountid is null';
            RAISE NOTICE 'SQL语句3为： %', mysql;
            execute mysql  ;
    
    --4 
                    mysql = 'update '||filename  ||E' as  flowMe 
                    set  remark = left(cc.product_categoryidname,position(\'-\' in cc.product_categoryidname)-1)
                            from product as cc                where flowMe.productid = cc.productid';
                RAISE NOTICE 'SQL语句4为： %', mysql;
                execute mysql  ;
    
    --5
            mysql = 'update '||filename  ||' as  flowMe set        
            to_businessunitid = b.businessunitid,            to_businessunitidname = b.businessunitidname,
            to_ownerid            = b.ownerid,            to_owneridname        = b.owneridname,
            to_sales_regionidname = sales_regionidname     ,            to_sales_regionid     = b.sales_regionid
            from sales_region_accountbase as b
            where flowMe.to_accountid = b.accountid
            and flowMe.remark = b.product_categoryidname';
                RAISE NOTICE 'SQL语句5为： %', mysql;
            execute mysql  ;
    
    --6
            mysql = 'update '||filename  ||' as  flowMe set    
            to_businessunitid     = b.businessunitid,
            to_businessunitidname = b.businessunitidname,
            to_ownerid            = b.ownerid,
            to_owneridname        = b.owneridname,
            to_sales_regionidname = sales_regionidname     ,
            to_sales_regionid     = b.sales_regionid
            from sales_region_areabase as b
            where flowMe.to_countyid =b.countyid
            and flowMe.to_provinceid =b.provinceid
            and flowMe.to_cityid =b.cityid
            and flowMe.remark = b.product_categoryidname';
                    RAISE NOTICE 'SQL语句6为： %', mysql;
                        execute mysql  ;
    --7根据ddm_account_product_uom 来更新在大小计量单位
                    mysql = 'update '||filename  ||' as  flowMe set         conversion_qty = b.conversion_qty
                    from ddm_account_product_uom as b
                    where
                    flowMe.from_accountid = b.from_accountid
                    and flowMe.productid = b.productid
                    and flowMe.product_alias_uom = b.uomid2name';
                        RAISE NOTICE 'SQL语句7为： %', mysql;
                        execute mysql  ;
    
    --8 更新流向表的数量为转换后的数量 
            mysql = 'update '||filename  ||'  set confirm_qty = ori_saleto_qty * conversion_qty ';
                            RAISE NOTICE 'SQL语句8为： %', mysql;
                        execute mysql  ;
    
    return 0;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100