performance - How can I make this big nasty query more efficient? -

-

this postgresql database. have view joining table 15 times create call levels, there doing coalesce function on 'levels'- little manipulation on field, well. pulling description field each of 15 levels. query became sluggishly slow. joining setheadert table multiple levels description field each level. can see have 3 description fields , taking long run. when had 2 took little bit wasn't bad. hope makes sense. code below. on how make more efficient appreciated.

select   subset_cls, prctr1,  case when prctr1 'pc%' split_part( overlay(prctr1 placing '00000' 1 2 ),'.',1) else prctr1 end pctrl2,  lvl01, desc01, lvl02, desc02   ( select src.sap_setnode.subset_cls subset_cls, src.sap_setheadert.description desc01, desc_02.description desc02, desc_03.description desc03, src.sap_setnode.set_name lvl01,  src.sap_setnode.subset_name lvl02,  setnode_1.subset_name lvl03,  setnode_2.subset_name lvl04,  setnode_3.subset_name lvl05,  setnode_4.subset_name lvl06,  setnode_5.subset_name lvl07,  setnode_6.subset_name lvl08,  setnode_7.subset_name lvl09,  setnode_8.subset_name lvl10,  setnode_9.subset_name lvl11,  setnode_10.subset_name lvl12,  setnode_11.subset_name lvl13,  setnode_12.subset_name lvl14,  setnode_13.subset_name lvl15, coalesce( setnode_13.subset_name, setnode_12.subset_name, setnode_11.subset_name, setnode_10.subset_name, setnode_9.subset_name, setnode_8.subset_name, setnode_7.subset_name, setnode_6.subset_name, setnode_5.subset_name, setnode_4.subset_name, setnode_3.subset_name, setnode_2.subset_name, setnode_1.subset_name, src.sap_setnode.subset_name, src.sap_setnode.set_name) prctr1  src.sap_setnode left join src.sap_setheadert  on src.sap_setheadert.set_name = src.sap_setnode.set_name  left join src.sap_setnode setnode_1 on src.sap_setnode.subset_name = setnode_1.set_name , src.sap_setnode.subset_cls = setnode_1.subset_cls  left join src.sap_setheadert desc_02  on desc_02.set_name = setnode_1.set_name  left join src.sap_setnode setnode_2 on setnode_1.subset_name = setnode_2.set_name  , setnode_1.subset_cls = setnode_2.subset_cls  left join src.sap_setheadert desc_03  on desc_03.set_name = setnode_2.set_name    left join src.sap_setnode setnode_3 on setnode_2.subset_name = setnode_3.set_name  , setnode_2.subset_cls = setnode_3.subset_cls    left join src.sap_setnode setnode_4 on setnode_3.subset_name = setnode_4.set_name  , setnode_3.subset_cls = setnode_4.subset_cls    left join src.sap_setnode setnode_5 on setnode_4.subset_name = setnode_5.set_name , setnode_4.subset_cls = setnode_5.subset_cls   left join src.sap_setnode setnode_6 on setnode_5.subset_name = setnode_6.set_name , setnode_5.subset_cls = setnode_6.subset_cls   left join src.sap_setnode setnode_7 on setnode_6.subset_name = setnode_7.set_name , setnode_6.subset_cls = setnode_7.subset_cls    left join src.sap_setnode setnode_8 on setnode_7.subset_name = setnode_8.set_name , setnode_7.subset_cls = setnode_8.subset_cls    left join src.sap_setnode setnode_9 on setnode_8.subset_name = setnode_9.set_name , setnode_8.subset_cls = setnode_9.subset_cls    left join src.sap_setnode setnode_10 on setnode_9.subset_name = setnode_10.set_name  , setnode_9.subset_cls = setnode_10.subset_cls   left join src.sap_setnode setnode_11 on setnode_10.subset_name = setnode_11.set_name  , setnode_10.subset_cls = setnode_11.subset_cls  left join src.sap_setnode setnode_12 on setnode_11.subset_name = setnode_12.set_name , setnode_11.subset_cls = setnode_12.subset_cls  left join src.sap_setnode setnode_13 on setnode_12.subset_name = setnode_13.set_name , setnode_12.subset_cls = setnode_13.subset_cls  group src.sap_setnode.subset_cls, src.sap_setheadert.description, desc_02.description,               desc_03.description, src.sap_setnode.set_name,  src.sap_setnode.subset_name, setnode_1.subset_name, setnode_2.subset_name,  setnode_3.subset_name, setnode_4.subset_name, setnode_5.subset_name,  setnode_6.subset_name, setnode_7.subset_name, setnode_8.subset_name,  setnode_9.subset_name, setnode_10.subset_name, setnode_11.subset_name,  setnode_12.subset_name, setnode_13.subset_name  having src.sap_setnode.subset_cls='0101' , src.sap_setnode.set_name='sisw.'   ||get_fy_part('year',       clock_timestamp())  order src.sap_setnode.set_name, src.sap_setnode.subset_name, setnode_1.subset_name,  setnode_2.subset_name, setnode_3.subset_name, setnode_4.subset_name, setnode_5.subset_name,  setnode_6.subset_name, setnode_7.subset_name  ) foo

if move filtering where clause there less rows joined. change this:

group ... having     src.sap_setnode.subset_cls = '0101' ,     src.sap_setnode.set_name = 'sisw.' || get_fy_part('year', clock_timestamp())

to

where     src.sap_setnode.subset_cls = '0101' ,     src.sap_setnode.set_name = 'sisw.' || get_fy_part('year', clock_timestamp()) group ...

Comments

Post a Comment

Popular posts from this blog

c++ - QTextObjectInterface with Qml TextEdit (QQuickTextEdit) -

-
i registred handler simple qtextobjectinterface , draws 10x10 red rectangle. when used qtextedit in normal qwidget app, worked. when used qquicktextedit (textedit qml component) in qt quick app, doesn't worked (nothing drawed, rectangle in textedit reserved, because when change cursor position, notice there something, empty space, nothing drawed. qtextobjectinterface intrinsicsize method called (that explains why see there empty space 10x10), drawobject method isn't. i did research , found problem here: qquicktextedit.cpp qt 5.3.0 sources (line 1821) qsgnode *qquicktextedit::updatepaintnode(qsgnode *oldnode, updatepaintnodedata *updatepaintnodedata) { . . . if (textframe->firstposition() > textframe->lastposition() && textframe->frameformat().position() != qtextframeformat::inflow) { updatenodetransform(node, d->document->documentlayout()->frameboundingrect(textframe).topleft()); c...
Read more

javascript - angular ng-required radio button not toggling required off in firefox 33, OK in chrome -

-
i trying toggle required attribute on radio button using angular show html5 popup. i cannot following code function correctly in firefox 33. function correctly in chrome. required property being toggled via checkbox value, in firefox, when set false radio button still acts required , shows html5 required popup. see plnkr below example doesn't work me in ff 33. <!doctype html> <html> <head> <script data-require="angular.js@*" data-semver="1.3.1" src="//code.angularjs.org/1.3.1/angular.js"></script> <link href="style.css" rel="stylesheet" /> <script src="script.js"></script> </head> <body ng-app="myapp"> <script> angular.module('myapp',[]) .controller('mycontroller',['$scope',function($scope) { $scope.foorequired=true; $scope.submitform = function() { $scope.submitmessage = ...
Read more

xcode - Swift Playground - Files are not readable -

-
Image
files not readable in swift playground . how make files readable? same code runs on xcode terminal app, fails on swift playground. demo code below. import foundation println("hello, world!") var fname:string = "/users/holyfield/desktop/com.apple.iconcomposer.plist" var fm:nsfilemanager = nsfilemanager.defaultmanager() if(fm.fileexistsatpath(fname)){ println("file exists") if(fm.isreadablefileatpath(fname)){ println("file readable") var fd:nsdata? = nsdata(contentsoffile: fname) println(fd?.length) let pl = nsdictionary(contentsoffile: fname) println(pl?.count) println(pl?.allkeys) }else{ println("file not readable") } } else{ println("file not exists") } sample images: i have thank nate cook first quick response , solid answer. just case share answer post, title misleading. see nate's answer here: https://stackoverf...
Read more