RE: [sqr-users] suggestions for best practicewithinsertsinto temptable

["Turner, Ivan" <Ivan.Turner@qwest.com>]

In response to Tom Hins posting, if you're using an oracle db, it's
better to use the sqr commit instead of the db commit he indicates.

-----Original Message-----
From: sqr-users-bounces+ivan.turner=qwest.com@sqrug.org
[mailto:sqr-users-bounces+ivan.turner=qwest.com@sqrug.org] On Behalf Of
Hins, Thomas D. (HSC)
Sent: Thursday, September 30, 2004 11:25 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] suggestions for best practice withinsertsinto
temptable

just do a procedure like
begin-sql
commit;
End-sql
where you want to do a commit

Tom Hins
Information Technology - Application Solutions
University of Oklahoma Health Sciences Center
P.O. Box 26901
Oklahoma City, OK  73190
Phone:(405) 271-2262 Ext. 50213
Fax: (405) 271-2352
(The information transmitted is intended only for the person or entity
to which it is addressed and may contain confidential and/or privileged
material. If you are not the intended recipient of this message you are
hereby notified that any use, review, retransmission, dissemination,
distribution, reproduction or any action taken in reliance upon this
message is prohibited. If you received this in error, please contact the
sender and delete the material from any computer. Any views expressed in
this message are those of the individual sender and may not necessarily
reflect the views of the company.)



-----Original Message-----
From: sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org
[mailto:sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org]On Behalf Of
Kevin Laroche
Sent: Thursday, September 30, 2004 10:19 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] suggestions for best practice with insertsinto
temptable


Thank you Thomas for clarifying what SQR does in regards to commits, I
always thought
it commited at the end of the end-sql statement.
We are performing some complex selects and data qualifications usually
with a 3-4
table join to get the data to go into the temp table.
When I have tried views as the basis for reports it appears to have a
higher
overhead in the explain plan  and worse performance than a straight
multi join select.

I will try a counter on the commits.

Regards

Kevin LaRoche
Oracle Forms Developer

On Sep 30, "Hins, Thomas D. \(HSC\)" <Thomas-Hins@ouhsc.edu> wrote:
> 
> Okay, Oracle - within an SQR the commits are not performed until the
SQR closes 
(similar to being in SQL Plus and closing the window there is a comiit
that occurs), you 
can always program a commit at anytime using a counter or other method
and this would cut 
your rollback segments down.  I am not sure what the use of your temp
table is or if you 
are doing any data manipulation before inserting into the temp table but
what about 
replacing the temp table with a view?  Just a thought.
> Tom Hins
> Information Technology - Application Solutions
> University of Oklahoma Health Sciences Center
> P.O. Box 26901
> Oklahoma City, OK  73190
> Phone:(405) 271-2262 Ext. 50213
> Fax: (405) 271-2352
> (The information transmitted is intended only for the person or entity
to which it is 
addressed and may contain confidential and/or privileged material. If
you are not the 
intended recipient of this message you are hereby notified that any use,
review, 
retransmission, dissemination, distribution, reproduction or any action
taken in reliance 
upon this message is prohibited. If you received this in error, please
contact the sender 
and delete the material from any computer. Any views expressed in this
message are those 
of the individual sender and may not necessarily reflect the views of
the company.)
> 
> 
> 
> -----Original Message-----
> From: sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org
> [mailto:sqr-users-bounces+thomas-hins=ouhsc.edu@sqrug.org]On Behalf Of
> Kevin Laroche
> Sent: Thursday, September 30, 2004 8:59 AM
> To: sqr-users@sqrug.org
> Subject: [sqr-users] suggestions for best practice with inserts into
> temptable
> 
> 
> Hi to all
> We are using Brio 6.1 on windows 2000 clients with oracle 8i database
> 
> We have some program logic that worked fine for small reports but is
now
> causing an elderly underpowered database to stagger and collapse when
we try and do 
> 10,000 sequential inserts and commits.
> The reports query the database with begin-selects and then inserts a 5
or 6 column 
> record of varchars and numbers into a temp table to be accessed with
another report.
> 
> At the moment the insert is being done on a one by one records basis
and causes too 
many 
> rollback segments to be allocated.
> I would like to batch the records so that they can be inserted and
committed in lots of 
> 100 to 500 records.
> 
> Can I get your opinions as to whether I would be better off to put the
records into an 
> SQR array inside the report and do a commit from the report or should
I try a server 
side
> procedure?
> When does SQR cause a database commit. At the end of a
begin-sql....insert into... end-
sql
> or at the end of procedure, or the report???
> 
> Thanks
> 
> Kevin LaRoche
> Oracle Forms Developer
> Ottawa, Ontario
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> <a href='http://www.sqrug.org/mailman/listinfo/sqr-
users'>http://www.sqrug.org/mailman/listinfo/sqr-users</a>
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> <a href='http://www.sqrug.org/mailman/listinfo/sqr-
users'>http://www.sqrug.org/mailman/listinfo/sqr-users</a>
> 
> 

_______________________________________________
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

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