Re: SQL optimize for only writes

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>>