RE: [sqr-users] Performance problems in SQR

["Peter Clark" <PGCLARK@vac-acc.gc.ca>]

These lines are in the wrong place. 

IF #i > 0
  LET $firsttime = 'Y'
  DO sorter($newloc, #i) 
 END-IF

Instead of after END-SELECT they should be in the body of your select.

BEGIN-PROCEDURE populate_array
 LET $P1 = UPPER($P1)
 LET $P2 = UPPER($P2)
 LET $new_parent = 'parent = ' || '''' ||$P2 || ''''
 LET $newloc = $P2
 IF $P1 != 'ALL'
  LET $sys_id ='and systemid = ' || '''' ||$P1 || '''' 
 ELSE
  LET $sys_id = '' 
 END-IF
 LET #i = 0

BEGIN-SELECT
location &location  
parent  &parent  
children &children 
 LET parents.location(#i) = &location
 LET parents.parent(#i) = &parent
 LET parents.children(#i) = &children
 IF #i > 0
  LET $firsttime = 'Y'
  DO sorter($newloc, #i) 
 END-IF
 LET #i = #i + 1

from maximo.lochierarchy where [$where] [$sys_id]   order by parent,
location 
END-SELECT
END-PROCEDURE !populate_array




>>> vadlamudisrinu@yahoo.com 2003/01/15 11:26:51 am >>>

Please find my SQL queries here:
I have been using Arrays and Sorting techniques. Basically my out put
like this:(Parent - Child)
Lcoation1           Description
AAAAA             DESCAAAAAAAAAAAAAAA
    BBBBB         DESCBBBBBBBBBBBBBBB
Please guide us how should I tune this query. I will apprciate youe
quick help
Thanks
Srinivas
 
BEGIN-PROCEDURE populate_array
 LET $P1 = UPPER($P1)
 LET $P2 = UPPER($P2)
 LET $new_parent = 'parent = ' || '''' ||$P2 || ''''
 LET $newloc = $P2
 IF $P1 != 'ALL'
  LET $sys_id ='and systemid = ' || '''' ||$P1 || '''' 
 ELSE
  LET $sys_id = '' 
 END-IF
 LET #i = 0

BEGIN-SELECT
location &location  
parent  &parent  
children &children 
 LET parents.location(#i) = &location
 LET parents.parent(#i) = &parent
 LET parents.children(#i) = &children
 LET #i = #i + 1
from maximo.lochierarchy where [$where] [$sys_id]   order by parent,
location 
END-SELECT
 IF #i > 0
  LET $firsttime = 'Y'
  DO sorter($newloc, #i) 
 END-IF
END-PROCEDURE !populate_array
=============Sort================================
BEGIN-PROCEDURE sorter($new_parent, #i)
 LET #rec_count = #i
 LET #counter = 0
 IF #indented = 0
  LET #indented = 1
 END-IF
 WHILE #counter < #rec_count
  LET $parent = parents.parent(#counter)
  LET $location = parents.location(#counter)
  LET $children = parents.children(#counter)
  IF  $parent = $new_parent
   IF #down = 0
    CLEAR-ARRAY NAME=backup
    POSITION    (+2, {col1})
    LET #wraplength = {width} - LENGTH($location)
    PRINT $location  (,{col1a})    BOLD WRAP #wraplength 5 
    LET $top_level = $location
   ELSE
    LET #indented = #down
    MULTIPLY {spacer} TIMES #indented    
    LET #wraplength = ({width} - #indented) - LENGTH($location)
    IF #wraplength < LENGTH($location)
     POSITION  (+1,{col1})
     LET #wraplength = (({width} - {col1}) + 2) - LENGTH($location)
     !LET $parent = backup.parent(#down)
!     PRINT $parent () BOLD BOX
     IF $children = 'Y' 
      LET $spacer = '+'
     ELSE
      LET $spacer = '-'
     END-IF
     STRING $parent $spacer $location BY ' ' INTO $full_line
     PRINT $full_line ()    BOLD BOX WRAP #wraplength 5 
!     PRINT $location  ()    BOLD BOX WRAP #wraplength 5
   ELSE
     POSITION (+1, #indented)
     PRINT $location  ()    BOLD WRAP #wraplength 5 
     !print 'SUB LOCATION' ()
    END-IF
   END-IF
    LET $locations_loc = 'location = ' || '''' ||$location || ''''
BEGIN-SELECT
description &new_desc
 PRINT &new_desc  (,+1)    WRAP #wraplength 5
 from maximo.locations where [$locations_loc]
END-SELECT
 
 "Knapp, Richard" <KnappR@umsystem.edu> wrote:
Generally, it takes a little more information than you are providing to
tune an SQR program.

However, as a first step you can put a display statement in your
driving select to see how long it takes to start returning rows.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu 


-----Original Message-----
From: vadlamudi srinivasa chowdary [mailto:vadlamudisrinu@yahoo.com] 
Sent: Tuesday, January 14, 2003 2:50 AM
To: sqr-users@sqrug.org 
Subject: [sqr-users] Performance problems in SQR



Could you please send me the performance techniques.

I have been usind arrays which fetches 8,000 records from oracle
datbase.

It is taking 4 hours yto display the output. Please help me reagrding
this.



I will appriciate your help.



Thanks

Srinivas





---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users 




---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users