Quick Procedures

[Joe Boyle]

One other thought, do you see a similar delay when running multiple inserts
directly in SQL when using similar data to that entered by screen users?

Regards, Joe.

-----Original Message-----
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Joe Boyle
Sent: 09 December 2005 18:23
To: 'John Stires'; powerh-l at lists.sowder.com
Subject: RE: Quick Procedures


There appears to be no checks for duplicates so indexes/keys can't be unique
( if there are any ) for this table.

If RDB is anything like RMS, there could be lots of secondary level record
pointers that have to be created when duplicates are allowed, which is all
time consuming.   

What is the table DDL syntax and how many rows are there in it ? Perhaps
full table scans are being done where there are unique columns involved.

Could it be that there are triggers involved, which are slowing up the DB
response time ?

Regards, Joe. 


________________________________________
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Johnson, Harold A EDUC:EX
Sent: 09 December 2005 17:26
To: 'John Stires'; powerh-l at lists.sowder.com
Subject: RE: Quick Procedures

The first thing to do would be to check the access into the various
tables/views to see if there is any sequential access going on.    Use
"$DEFINE RDMS$DEBUG_FLAGS SO" to setup the display, then run this process
and log the access display as it comes up on the screen.    Look for
"sequential" or "[0:0]" which will indicate a problem. 
 
Also, run the rdb monitor to check for locks  "$RMU/SHOW STAT sql$database",
then use commands D, I, A to get to the lock screen.
 
 
good luck
-----Original Message-----
From: powerh-l-bounces+harold.a.johnson=gov.bc.ca at lists.sowder.com
[mailto:powerh-l-bounces+harold.a.johnson=gov.bc.ca at lists.sowder.com]On
Behalf Of John Stires
Sent: 2005 December 9 9:15 AM
To: powerh-l at lists.sowder.com
Subject: Quick Procedures
I am working on a rather complex screen.  Those working on this screen
before me have pretty well disabled and/or misused all of the default
procedures so there is very little left to Powerhouse to handle on its own.
 
My question is this, there is a PUSH UPDATE in one of the designer
procedures.  When this happens, does it automatically initial the PREUPDATE
first and then go to the UPDATE procedure or does it go straight to the
UPDATE procedure bypassing the PREUPDATE.\We are having big time response
problems with this program, a very good likely hood this is caused by
deadlocks.  A user update to this screen is now 45 minutes to 1 1/2 hours. 
Not very good.
 
All of the code that should be in the PREUPDATE procedure is currently
located in the UPDATE procedure.  There are PUTs sprinkled through all of
this code making this a time consuming process.  I ! am looking to reduce
the time spent in the UPDATE procedure by moving much of this code to the
PREUPDATE procedure.  I am very sure there are many other things to be done
as well.
 
The second issue with this screen is how it handles the detail file
updates.  The screen defines a cluster, but uses temp items, named after the
fields in the real record.  The file statement looks like:
 
file OBLIGATION_TRANS in CARS_DB designer noitems need all &
  transaction query for query, process &
  transaction update for update
with the field statements looking like:
 
cluster at 19,1 occurs 5
field T_REMAIN_AMT                      &
      label " "                         &
      hidden nochange                   &
      pic "^^^,^^^,^^^.^^" bwz signif 4
field T_POSTBACK_BALANCE                &
      upshift required                  &
      values "Y","N"
field T_DTL_REASON_CODE                 &
      hidden required upshift
field T_DTL_REASON_DESC                 &
      display
field T_TRANS_AMT                       &
      entry if T_POSTBACK_BALANCE = "N" &
      pic "^^^,^^^,^^^.^^" bwz input scale 2 signif 4
cluster
 
In the UPDATE procedure, there is a FOR structure using 5 as its limit.  The
users get "reasonable response time", 5 minutes, if they only enter up to 5
entries in these fields.  When they enter more than 5, their response time
goes to over 45 minutes.
 
Needles to say I am under some serious pressure here to make some big
improvements.
 
We are running on VMS 6.2, Powerhouse vers! ion 7.10e6, and DEC RDB, I think
version 6.1.
 
Thanks for any good impute,
 
John Stires


-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.