After some more testing Ive come to the conclusion that the CAST statement
does not cause any issues.  I tried populating a new field in AR file with
74000 records using a cast statement that was applied to every record and
the execution time was very reasonable.  The problem is definitely with the
IN statement.  I tried adding a composite index for the three fields used in
the IN statement with no benefit whatsoever.  The view also executes in
reasonable time.  I do not see any delay in opening the view nor in
executing a SQL statement similar to the one that creates the view.  I there
fore dont think that any time should be wasted on that.  The only problem,
as stated above, is the excution of the IN statement.  And that seems to be
an EDB quirk or a problem.  If anyone can get brother Tim involved it might
be helpful.  Per your request Ive attached an execution plan for this query.

SQL Update (Executed by ElevateDB 2.11 Build 3)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

================================================================================


UPDATE "ARLines"
SET "Keyoff" = ("IX")
WHERE ("KeyOff" IS NULL OR "KeyOff" = ' ') AND (("Phone", "PhnSeq",
"InvNum")
IN (SELECT ALL "Phone" AS "Phone", "PhnSeq" AS "PhnSeq", "InvNum" AS
"InvNum"
FROM "ClearedARTrans" ORDER BY "Phone", "PhnSeq", "InvNum"))

Target Table
------------

ARLines: 74351 rows

Filtering
---------

The following filter condition was applied to the ARLines table:

(("Phone", "PhnSeq", "InvNum") IN (SELECT ALL "Phone" AS "Phone", "PhnSeq"
AS
"PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans" ORDER BY "Phone",
"PhnSeq"
"InvNum")) AND ("KeyOff" IS NULL OR "KeyOff" = ' ')

Row scan (ARLines): 74351 rows, 19.29MB estimated cost

Hint: Create index(es) (ARLines) on column(s) "KeyOff" for possible better
performance

Here is the execution plan for the original SQL Statement:

SQL Update (Executed by ElevateDB 2.11 Build 3)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

================================================================================


UPDATE "ARLines"
SET "Keyoff" = ("IX")
WHERE (("Phone", "PhnSeq", "InvNum") IN (SELECT ALL "Phone" AS "Phone",
"PhnSeq" AS "PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans" ORDER BY
"Phone", "PhnSeq", "InvNum"))

Target Table
------------

ARLines: 74351 rows

Filtering
---------

The following filter condition was applied to the ARLines table:

(("Phone", "PhnSeq", "InvNum") IN (SELECT ALL "Phone" AS "Phone", "PhnSeq"
AS
"PhnSeq", "InvNum" AS "InvNum" FROM "ClearedARTrans" ORDER BY "Phone",
"PhnSeq"
"InvNum"))

Row scan (ARLines): 74351 rows, 19.29MB estimated cost

This enforces my theory that that use of the IN statement is unacceptably
inefficient.

Any suggestions

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:3E8B1D4D-6D6F-4C34-B578-863BE9D09997@news.elevatesoft.com...
> Ben
>
>
> Since it complete can you ask for and post the execution plan? The
> execution plan for the view (as a query) may also be useful.
>
> Have you tried the idea of creating a temporary table and seeing what
> happens with that?
>
> Roy Lambert [Team Elevate]
>

Ben

<<I tried adding a composite index for the three fields used in the IN statement with no benefit whatsoever.>>

I presume this was in ARLines? Did you also add one for KeyOff? This is what the execution plan suggests.

Try doing that and if the performance is then acceptable great - if not keep reading.

The next part again comes with a warning - I'm guessing (we have asked Tim for a technical article on interpreting execution plans).

I don't think the problem at its root is the IN statement, its the fact that its being carried out via a row scan ie an inefficient mechanism compared to an index scan, and it can't do anything else because you can't index a view.

I'll suggest again create a temporary (or in-memory) table and use that not a view - you can use the view to create the temporary table if you want. You can add indices to a temporary table where you can't to a view.


Roy Lambert [Team Elevate]

From this discussion it seems to me that really is no efficient way to
accomplish this task via SQL.  I think the simplest way would be:
1.  Create View
2.  Execute Query for the detail
3.  Use this as a Master/Detail compbination
4.  Use code to step thru view and update the detail (query).

It doesnt really pay to invest any more time in this since it will be faster
by code.  I ques SQL has its limitations and old fashioned coded cannot be
replaced entirely by SQL

Ben
"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:1F340A19-B4AB-41C2-BCDB-816331ABFAD1@news.elevatesoft.com...
> Ben
>
> <<I tried adding a composite index for the three fields used in the IN
> statement with no benefit whatsoever.>>
>
> I presume this was in ARLines? Did you also add one for KeyOff? This is
> what the execution plan suggests.
>
> Try doing that and if the performance is then acceptable great - if not
> keep reading.
>
> The next part again comes with a warning - I'm guessing (we have asked Tim
> for a technical article on interpreting execution plans).
>
> I don't think the problem at its root is the IN statement, its the fact
> that its being carried out via a row scan ie an inefficient mechanism
> compared to an index scan, and it can't do anything else because you can't
> index a view.
>
> I'll suggest again create a temporary (or in-memory) table and use that
> not a view - you can use the view to create the temporary table if you
> want. You can add indices to a temporary table where you can't to a view.
>
>
> Roy Lambert [Team Elevate]
>

Ben

What happened when you replaced the "IN" with "=" as I suggested a couple of days ago? Any faster?
(I am assuming the 3 fields in the Select statement are part of a compound index)

Barry

This is the result

ElevateDB Error #1011 An error occurred with the query (SELECT ALL "Phone"
AS "Phone", "PhnSeq" AS "PhnSeq", "InvNum" AS "InvNum" FROM
"ClearedARTrans") (A scalar query can only return a single value)

Ben
<Barry> wrote in message
news:A65AFA9E-9CAC-4D5F-B7E5-0097799E533D@news.elevatesoft.com...
> Ben
>
> What happened when you replaced the "IN" with "=" as I suggested a couple
> of days ago? Any faster?
> (I am assuming the 3 fields in the Select statement are part of a compound
> index)
>
> Barry
>

Ben,

<< If anyone can get brother Tim involved it might be helpful.  Per your
request Ive attached an execution plan for this query. >>

This is going to require a support session to analyze.  You can request one
by emailing: support@elevatesoft.com.

Tim Young
Elevate Software
www.elevatesoft.com