Re: SQL optimize for only writes

[CRPence <CRPbottle@xxxxxxxxx>]

> It does have a logical over it (select "unprocessed records" only)

  I have seen similar issues reported for when an [SQL]RPG program 
declared a VIEW in its F-spec.  An implicit open of that logical VIEW 
file performs a query, regardless that it might never be referenced.  If 
the VIEW is not used for non-SQL activity, it should either be left 
undeclared, or explicitly coded for USROPN to prevent its implicit open. 
 Sometimes in an attempt to generate proper "where used" information, 
the USROPN is not properly coded, causing worthless file open\close 
activity.

  If not so simple as that, then...

  Does the table scan occur in the same job as the insert?  Is the 
table scan actually of the file being inserted into?  Is the table scan 
being attributed to the same statement identified as the insert of values?

  The "table scan" almost has to be from activity separate from that 
INSERT statement which has no WHERE clause.  That is, an insert of 
literal values would not run a query, unless perhaps the target of the 
insert is a VIEW instead of a TABLE.  Even then the open of the VIEW by 
SQL should not be running the VIEW query for an INSERT, not even with 
the WITH CHECK OPTION specified.?  An SQL INSERT for literals would 
simply build an insert capable ODP, much the same as what occurs for row 
level access.

  If the INSERT is INTO a VIEW, then an obvious attempt to circumvent a 
table scan [if that were by any chance the origin for the scan] is to 
simply redirect the insert to the TABLE or a different VIEW which has no 
selection.  If somehow the origin was [validly] the VIEW as target of 
insert, then a good\selective key to eliminate the table scan caused by 
the WHERE clause would be the approach; again, I do not think any query 
would occur regardless of the WHERE.

Regards, Chuck

Walden H. Leverich wrote:
> A table scan SUCKS! It's SQL reading the entire table from top to
> bottom. I can think of no reason there would be a table scan on an
> insert. If there's a unique constraint there will also be an index to
> enforce the constraint so a table scan isn't needed there. Are there
> triggers on this table? Perhaps the trigger is doing something that
> requires the table scan?
>
> -Walden
>
> > Gqcy wrote:
> > I have a process that just inserts rows ( INSERT INTO <lib.file> 
> > (field1, field2, etc) VALUES(value1, value2, etc)
> >
> > things were great....   until I got like 400k records in the file,
> > then when I wanted to do a lot of writes at near the same time, 
> > performance died.
> >
> > I see something in the SQL monitor about doing a "table scan" 
> > before each INSERT.
> > How do I NOT do this? (if I can) 
> >  <<SNIP>>