Re: [sqr-users] Thinking about trying something - looking forideas

[jwillson@dssltd.com]

Hi John. here is a collection of possibilties:
1)  In doing a report suite I benchmarked a single dynamic SQL 
retrieval versus
numerous individual retrievals and the individual ones were both faster and
easier to debug.  However, the elegance of the dynamic SQL made for a very
short SQR.
2)  In doing PS table retrievals I use dynamic SQL to load the most recent
version of a possibly underlying changing table so that I don't need to know
what it is until run time of the SQR with the dynamic SQL.   Works well in
large development projects where multiple people have control of individual
tables as opposed to 1 person having control of tables.
3)   SQR that writes SQR's based upon that dynamic SQL is possible as 
stated in
2).  Others have one that parses SQL Plus and builds an SQR.  Both still
require tweaking (Decode instruction, etc)
4)  Tree walking software for PS is tricky (as you have to do the matching you
are suggesting of parent child keys) and I have only gone down 7 levels in a
Department Security tree.   You may be able to find someone who has 
generalized
this.  Again, it might be better to tree represent say in a load array then to
do selects down through all the paths to the leaves and work off the load
array.
5)  Index tuning on the keys will help.

Just some possibilities.

John Willson
http://www.dssltd.com




Quoting john.tucker@oracle.com:

> I have a rather different question today.  I am thinking about 
> writing an SQR that would look for orphan data anywhere in a 
> PeopleSoft database, using PeopleTools tables to figure out what to 
> look at rather than using a static list of tables.  I have an idea or 
> two about how to go about this... but I wondered if anyone out here 
> had already written something similar and/or had some suggestions for 
> things to do/avoid.
>
> For example, there will be a lot of dynamic SQL getting processed in 
> this SQR.  Does that usually stop indexes from being used?  If so, is 
> there any way I can force indexes to be used?
>
> Also, would it be better to say "select [whatever] from [child] where 
> not exists ([whatever] in [parent])" or would it be better to write 
> two select statements, pulling all rows from the child table and 
> trying to match them with rows in the parent table in a separate 
> select statement?  Either way, I already know this thing is going to 
> take forever to run.
>
> I don't want you fine folks to do my work for me, but I was pretty 
> sure someone out there would have suggestions on how I can make this 
> a better, faster tool than the way I would have written it without 
> asking.
>
> -John T.
>
>
> _______________________________________________
> 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