sql - Improve Function performance in Postgresql -

-

following function creating temp table , populating data in it.

that temp table supposed displayed on click of button on website.

steps tried improve performance: 1. modified postgresql.conf file. 2. added index in temp table

create or replace function lastonemonth()   returns void $body$   declare   query1 text;   query2 text;   var_loop1 record;   dealername text;   cur_minus_2_month text;    var_sum_of_quantity numeric;   var_net_value numeric;   var_average_price_nsr numeric;    begin   execute 'drop table if exists lom cascade';   execute 'create temp table lom ( dealer_name text primary key , sum_of_quantity numeric, net_value numeric, average_price_nsr numeric)';   execute 'create unique index dealer_name_idx on lom (dealer_name)';    query1:= 'select distinct dealer customernotorderdb_temp month in ( to_char( now() - interval ''2 month'', ''yyyymm'') , to_char( now() - interval ''3 month'', ''yyyymm''))except select distinct dealer customernotorderdb_temp month in ( to_char( now() - interval ''1 month'', ''yyyymm''), to_char(now(), ''yyyymm'') )';   var_loop1 in execute (query1)   loop       dealername:= var_loop1.dealer;       insert lom(dealer_name) values(dealername);       execute 'select month customernotorderdb_temp dealer ''%'||dealername||'%'' , month in(to_char( now() - interval ''2 month'', ''yyyymm''))' cur_minus_2_month;        --raise notice 'cur_minus_2_month( % )', cur_minus_2_month;       if cur_minus_2_month not null                  execute 'select sum(saleqtypermt) customernotorderdb_temp dealer ''%'||dealername||'%'' , month in (to_char( now() - interval ''2 month'', ''yyyymm''),to_char( now() - interval ''3 month'', ''yyyymm''),to_char( now() - interval ''4 month'', ''yyyymm''),to_char( now() - interval ''5 month'', ''yyyymm''),to_char( now() - interval ''6 month'', ''yyyymm''),to_char( now() - interval ''7 month'', ''yyyymm''),to_char( now() -interval ''8 month'', ''yyyymm''),to_char( now() - interval ''9 month'', ''yyyymm''),to_char( now() - interval ''10 month'', ''yyyymm''),to_char( now() - interval ''11 month'', ''yyyymm''),to_char( now() - interval ''12 month'', ''yyyymm''),to_char( now() - interval ''13 month'', ''yyyymm''))' var_sum_of_quantity;           execute 'select sum(basic_value-rate_diff) customernotorderdb_temp dealer ''%'||dealername||'%'' , month in (to_char( now() - interval ''2 month'', ''yyyymm''),to_char( now() - interval ''3 month'', ''yyyymm''),to_char( now() - interval ''4 month'', ''yyyymm''),to_char( now() - interval ''5 month'', ''yyyymm''),to_char( now() - interval ''6 month'', ''yyyymm''),to_char( now() - interval ''7 month'', ''yyyymm''),to_char( now() -interval ''8 month'', ''yyyymm''),to_char( now() - interval ''9 month'', ''yyyymm''),to_char( now() - interval ''10 month'', ''yyyymm''),to_char( now() - interval ''11 month'', ''yyyymm''),to_char( now() - interval ''12 month'', ''yyyymm''),to_char( now() - interval ''13 month'', ''yyyymm''))' var_net_value;           execute 'select sum(avgpricensr) customernotorderdb_temp dealer ''%'||dealername||'%'' , month in (to_char( now() - interval ''2 month'', ''yyyymm''),to_char( now() - interval ''3 month'', ''yyyymm''),to_char( now() - interval ''4 month'', ''yyyymm''),to_char( now() - interval ''5 month'', ''yyyymm''),to_char( now() - interval ''6 month'', ''yyyymm''),to_char( now() - interval ''7 month'', ''yyyymm''),to_char( now() -interval ''8 month'', ''yyyymm''),to_char( now() - interval ''9 month'', ''yyyymm''),to_char( now() - interval ''10 month'', ''yyyymm''),to_char( now() - interval ''11 month'', ''yyyymm''),to_char( now() - interval ''12 month'', ''yyyymm''),to_char( now() - interval ''13 month'', ''yyyymm''))' var_average_price_nsr;           --raise notice 'a [ %   % ]', dealername,var_net_value;           update lom set sum_of_quantity=var_sum_of_quantity,                          net_value=var_net_value,                          average_price_nsr=var_average_price_nsr            dealer_name=var_loop1.dealer;        else           execute 'select sum(saleqtypermt) customernotorderdb_temp dealer ''%'||dealername||'%'' , month in (to_char( now() - interval ''3 month'', ''yyyymm''),to_char( now() - interval ''4 month'', ''yyyymm''),to_char( now() - interval ''5 month'', ''yyyymm''),to_char( now() - interval ''6 month'', ''yyyymm''),to_char( now() - interval ''7 month'', ''yyyymm''),to_char( now() -interval ''8 month'', ''yyyymm''),to_char( now() - interval ''9 month'', ''yyyymm''),to_char( now() - interval ''10 month'', ''yyyymm''),to_char( now() - interval ''11 month'', ''yyyymm''),to_char( now() - interval ''12 month'', ''yyyymm''),to_char( now() - interval ''13 month'', ''yyyymm''),to_char( now() - interval ''14 month'', ''yyyymm''))' var_sum_of_quantity;           execute 'select sum(basic_value-rate_diff) customernotorderdb_temp dealer ''%'||dealername||'%'' , month in (to_char( now() - interval ''3 month'', ''yyyymm''),to_char( now() - interval ''4 month'', ''yyyymm''),to_char( now() - interval ''5 month'', ''yyyymm''),to_char( now() - interval ''6 month'', ''yyyymm''),to_char( now() - interval ''7 month'', ''yyyymm''),to_char( now() -interval ''8 month'', ''yyyymm''),to_char( now() - interval ''9 month'', ''yyyymm''),to_char( now() - interval ''10 month'', ''yyyymm''),to_char( now() - interval ''11 month'', ''yyyymm''),to_char( now() - interval ''12 month'', ''yyyymm''),to_char( now() - interval ''13 month'', ''yyyymm''),to_char( now() - interval ''14 month'', ''yyyymm''))' var_net_value;           execute 'select sum(avgpricensr) customernotorderdb_temp dealer ''%'||dealername||'%'' , month in (to_char( now() - interval ''3 month'', ''yyyymm''),to_char( now() - interval ''4 month'', ''yyyymm''),to_char( now() - interval ''5 month'', ''yyyymm''),to_char( now() - interval ''6 month'', ''yyyymm''),to_char( now() - interval ''7 month'', ''yyyymm''),to_char( now() -interval ''8 month'', ''yyyymm''),to_char( now() - interval ''9 month'', ''yyyymm''),to_char( now() - interval ''10 month'', ''yyyymm''),to_char( now() - interval ''11 month'', ''yyyymm''),to_char( now() - interval ''12 month'', ''yyyymm''),to_char( now() - interval ''13 month'', ''yyyymm''),to_char( now() - interval ''14 month'', ''yyyymm''))' var_average_price_nsr;           --raise notice 'b [ %  % ]', dealername,var_net_value;           update lom set sum_of_quantity=var_sum_of_quantity,net_value=var_net_value,average_price_nsr=var_average_price_nsr dealer_name=var_loop1.dealer;       end if;           end loop; end; $body$   language plpgsql volatile

first of all, looks you're storing months strings , manually doing date comparisons strings. that'll slow down. postgres date types or integers instead?

for other things, can't tell without knowing database layout , statistics, here's basic method optimizing queries:

  1. open new connection using psql or something.
  2. set enable_seqscan = off, disables seq scan in query planner (unless necessary) session.
  3. run explain before queries see ones being forced scan large tables. add indexes if see seq scans.

Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more