Code set #1
UPDATE ARLines
SET Keyoff = (Cast (InvNum as VarChar(10)))
WHERE ("Phone","PhnSeq","InvNum") IN
(SELECT Phone, PhnSeq, InvNum from ClearedARTrans)


Code set #2
UPDATE ARLines
SET Keyoff = (Cast (InvNum as VarChar(10)))
WHERE InvNum = 56789

As a note:
The field KeyOff is a Varchar of 10
The field InvNum is an integer and non in the database are greater than
100,000


Set # 1 hangs.  The result set from the last line is approx 15 records and
records to be updated are approx 40
Set #2 works fine albeit its only three records to update.

Why

Ben

What indexes and how many rows are there in those tables ?

--
Fernando Dias
[Team Elevate]

Ben


In addition to what Fernando asked how long did you give it to execute? Is ClearedARTrans another View? If so how long does a simple

SELECT * FROM ClearedARTrans

take? If its the first time its been opened the view has to be built before anything else can happen.

If as I suspect ClearedARTrans is a View then to obtain an execution plan (which will give you a good indication of how to make it faster) you'll need to run the sql that makes up the View as a straight query.

Roy Lambert [Team Elevate]

Ben,

If both tables have the same compound index of Phone, PhnSeq, InvNum, have you tried the following:
(Replace "IN" with "=")

Barry


Code set #1
UPDATE ARLines
SET Keyoff = (Cast (InvNum as VarChar(10)))
WHERE ("Phone","PhnSeq","InvNum") =
(SELECT Phone, PhnSeq, InvNum from ClearedARTrans)

The original table has approx 150 records
The result set is approx 15 records
Records to be updated are approx 40.
The view to open takes less than 2 seconds
An update without the cast statement will take 1-2 seconds
An update with the cast statement will hang the query.  I left it like that
for 10 minutes and it wont move.


<Barry> wrote in message
news:0DA39191-D897-4E07-BF15-B49E9A5D9D4F@news.elevatesoft.com...
> Ben,
>
> If both tables have the same compound index of Phone, PhnSeq, InvNum, have
> you tried the following:
> (Replace "IN" with "=")
>
> Barry
>
>
> Code set #1
> UPDATE ARLines
> SET Keyoff = (Cast (InvNum as VarChar(10)))
> WHERE ("Phone","PhnSeq","InvNum") =
> (SELECT Phone, PhnSeq, InvNum from ClearedARTrans)
>

Ben,

This makes no sense, unless it is trying to do a cross product join for some reason.
What does the Execution Plan say?
What happens if you add "Range 1 to 1"?

If none of these suggestions shed any light on the problem then...
.. this looks like a job for Tim.

Barry

Ben


I assume you're referring to this statement

UPDATE ARLines
SET Keyoff = (Cast (InvNum as VarChar(10)))
WHERE ("Phone","PhnSeq","InvNum") IN
(SELECT Phone, PhnSeq, InvNum from ClearedARTrans)

[WARNING] I'm guessing here [/WARNING]

If ClearedARTrans is the same or similar to ClearedAPTrans I think you may have generated an infinite loop. Tim will be able to say.

Assuming for the moment that the two views are similar and that both reference Keyoff  then each time you update Keyoff the view is rerun which causes the selection to be changed which causes the view to be rerun

There's an easy way to test this out. Create a temporary table using the view and then use that in the UPDATE query eg

CREATE TEMPORARY Table Fred FROM SELECT Phone, PhnSeq, InvNum from ClearedARTrans

UPDATE ARLines
SET Keyoff = (Cast (InvNum as VarChar(10)))
WHERE ("Phone","PhnSeq","InvNum") IN
(SELECT * FROM Fred)


Roy Lambert [Team Elevate]

ClearedAR and Cleared AP are 2 distinct views.  They just happen to have a
field named alike.  Im NOT trying to cross from one to the other.  The only
difference is that in AP the InvNum field is a VC and in AR InvNum is an
integer.  I just happen to be doing a similar operation on either file.
When I do it in AP (without the Cast since the InvNum and KeyOff fields are
VC) there is no problem.  When I do the same thing in AR with a CAST
statement its hanging.

Ben

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:30FA2C52-1BB5-416B-BE16-1DB80E9EADBD@news.elevatesoft.com...
> Ben
>
>
> I assume you're referring to this statement
>
> UPDATE ARLines
> SET Keyoff = (Cast (InvNum as VarChar(10)))
> WHERE ("Phone","PhnSeq","InvNum") IN
> (SELECT Phone, PhnSeq, InvNum from ClearedARTrans)
>
> [WARNING] I'm guessing here [/WARNING]
>
> If ClearedARTrans is the same or similar to ClearedAPTrans I think you may
> have generated an infinite loop. Tim will be able to say.
>
> Assuming for the moment that the two views are similar and that both
> reference Keyoff  then each time you update Keyoff the view is rerun which
> causes the selection to be changed which causes the view to be rerun
>
> There's an easy way to test this out. Create a temporary table using the
> view and then use that in the UPDATE query eg
>
> CREATE TEMPORARY Table Fred FROM SELECT Phone, PhnSeq, InvNum from
> ClearedARTrans
>
> UPDATE ARLines
> SET Keyoff = (Cast (InvNum as VarChar(10)))
> WHERE ("Phone","PhnSeq","InvNum") IN
> (SELECT * FROM Fred)
>
>
> Roy Lambert [Team Elevate]
>

I did some further testing on this issue and here are my findings:
There are distinct views. The name of the derived table (InvoiceTotals) is
the same in both views.  After running
several times I finally got an error as follows:

ElevateDB Error #700 An error was found in the statement at line 4 and
column 9
(ElevateDB Error #401 The table or view InvoiceTotals5 does not exist in
the schema Default)
Note the number it added to the derived table name.
This is very interesting.
I went ahead and changed the derived table name in the view and the error
went away.
I started executing the UPDATE SQL in the EDB Manager.  It ran fine but it
took 962 seconds to complete.
This is unacceptable.  Any suggestions ??  Perhaps Tim can get involved.
The actual ARLines table has 74000 records.  The view which opens in about
two seconds winds up with ten records.

Ben

"Ben" <arrow1432@verizon.net> wrote in message
news:2254557F-CC43-4EA5-9A15-E018609D2491@news.elevatesoft.com...
> ClearedAR and Cleared AP are 2 distinct views.  They just happen to have a
> field named alike.  Im NOT trying to cross from one to the other.  The
> only difference is that in AP the InvNum field is a VC and in AR InvNum is
> an integer.  I just happen to be doing a similar operation on either file.
> When I do it in AP (without the Cast since the InvNum and KeyOff fields
> are VC) there is no problem.  When I do the same thing in AR with a CAST
> statement its hanging.
>
> Ben
>
> "Roy Lambert" <roy@lybster.me.uk> wrote in message
> news:30FA2C52-1BB5-416B-BE16-1DB80E9EADBD@news.elevatesoft.com...
>> Ben
>>
>>
>> I assume you're referring to this statement
>>
>> UPDATE ARLines
>> SET Keyoff = (Cast (InvNum as VarChar(10)))
>> WHERE ("Phone","PhnSeq","InvNum") IN
>> (SELECT Phone, PhnSeq, InvNum from ClearedARTrans)
>>
>> [WARNING] I'm guessing here [/WARNING]
>>
>> If ClearedARTrans is the same or similar to ClearedAPTrans I think you
>> may have generated an infinite loop. Tim will be able to say.
>>
>> Assuming for the moment that the two views are similar and that both
>> reference Keyoff  then each time you update Keyoff the view is rerun
>> which causes the selection to be changed which causes the view to be
>> rerun
>>
>> There's an easy way to test this out. Create a temporary table using the
>> view and then use that in the UPDATE query eg
>>
>> CREATE TEMPORARY Table Fred FROM SELECT Phone, PhnSeq, InvNum from
>> ClearedARTrans
>>
>> UPDATE ARLines
>> SET Keyoff = (Cast (InvNum as VarChar(10)))
>> WHERE ("Phone","PhnSeq","InvNum") IN
>> (SELECT * FROM Fred)
>>
>>
>> Roy Lambert [Team Elevate]
>>
>
>

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]