Uli,

I didn't read your last post to the end, where you were showing what you were trying, I'm sorry.
Now i did.
I'm wondering if forcing EDB to create an index on AnforderungenID would make a difference.
Does it make any difference if you change it to :

....
execute immediate '
Update Leistungentest set Year = Year + 1 where AnforderungeniD in
(select AnforderungenID from TempAnforderungen ORDER BY AnforderungenID )
';
....



--
Fernando Dias
[Team Elevate]

Fernando,
> Does it make any difference if you change it to :
>
> ...
> execute immediate '
> Update Leistungentest set Year = Year + 1 where AnforderungeniD in
> (select AnforderungenID from TempAnforderungen ORDER BY AnforderungenID )
> ';

No, that doesn't make a difference.

Here a script that works very fast as long as the number of records to
change is not too big because of the usage of a cursor:

SCRIPT
BEGIN

DECLARE Result CURSOR FOR Stmt;
DECLARE CurrentID INTEGER;

PREPARE Stmt FROM
'select * from Leistungentest L
join Anforderungen A
on L.AnforderungenID = A.AnforderungenID
where Datum >= ?';
OPEN Result using date '2013-8-20';

FETCH FIRST FROM Result('LeistungenID') INTO CurrentID;
WHILE NOT EOF(Result) DO
  Execute Immediate 'Update Leistungentest set Year = Year + 1 where
LeistungenID = ?' using CurrentID;
  FETCH NEXT FROM Result('LeistungenID') INTO CurrentID;
END WHILE;

END

In this case the result cursor returns 193 rows and the whole script is
executed in 0.1 seconds.

Uli

Uli,

Thank you. I think the light bulb is getting a little brighter.

So the temp table "TempAnforderungen" is used to create a dataset that
is a 100% match of all the records to be updated. I think I'm getting
it.

Just curious... what happens if you test for EXISTS

UPDATE Leistungentest
SET Year = Year + 1
WHERE EXISTS (SELECT * FROM TempAnforderungen
             WHERE TempAnforderungen.AnforderungenID
                 = Leistungentest.AnforderungenID)


> Michael,
>
> > This has been a very interesting thread for me to watch. I'm still
> > learning how to translate my Microsoft SQL habits into ElevateDB
> > habits. Since ElevateDB does not allow inner join updates, would you
> > please provide an example of how you solve this problem using a
> > temporary table?
>
> Actually using a temporary table doesn't seem to be a real workaround:
>
> SCRIPT
> BEGIN
>
> BEGIN
> Execute Immediate 'Drop Table "TempAnforderungen"';
> EXCEPTION
> END;
>
> Execute Immediate 'Create Temporary Table "TempAnforderungen" as
>  select AnforderungenID, Datum from Anforderungen where datum >= ?
> with data' using date '2013-8-20';
>
> EXECUTE IMMEDIATE 'CREATE INDEX "Anforderungen_INX" ON
> "TempAnforderungen" ("AnforderungenID")';
>
> Execute Immediate 'Update Leistungentest set Year = Year + 1 where
> AnforderungeniD in (select AnforderungenID from TempAnforderungen)';
>
> END
>
> though it speeds up the execution time to about 4 seconds. But that's
> still way too slow:
>
> Target Table
> ------------
>
> Leistungentest: 244386 rows
>
> Filtering
> ---------
>
> The following filter condition was applied to the Leistungentest
> table:
>
> "AnforderungeniD" IN (SELECT ALL "AnforderungenID" AS
> "AnforderungenID" FROM "TempAnforderungen" ORDER BY "AnforderungenID")
>
> Row scan (LeistungenTest): 244386 rows, 57,8MB estimated cost
>
> ======================================================================
> ========== 193 row(s) updated in 3,744 secs
> ======================================================================
> ==========
>
> Uli



--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Uli,

Yeah, that's what I had in mind when I mentioned a script with cursors.
I can't think of anything better than that, as I can't think of any way to apply a 'live' filter on a column that is not in the base table.

--
Fernando Dias
[Team Elevate]

Michael,

> Just curious... what happens if you test for EXISTS

The result is the same: about 4 seconds.

But see my last answer to Fernando's post: by using a cursor I could
reduce it to 0.2 seconds.

Uli

Uli Becker wrote:

> Michael,
>
> > Just curious... what happens if you test for EXISTS
>
> The result is the same: about 4 seconds.
>
> But see my last answer to Fernando's post: by using a cursor I could
> reduce it to 0.2 seconds.
>
> Uli

Glad to hear you found a good solution. Thanks for including me in your
conversation.

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael,

> Glad to hear you found a good solution. Thanks for including me in your
> conversation.

You are welcome

Uli

Dear people:
I ran into the same exact situation a while back.  I worked with Tim on it
and arrived at no solution.  The way I do it is run 2 separate queries.
Connect qury 2 to query 1 with a datasource.  Then update query 1 if data in
query 2 qualifies - All via code.  There is no shortcut to this in EDB

Ben Sprei
"Fernando Dias" <fernandodiasAremovthis.easygate.com.pt> wrote in message
news:5969C719-6F81-49C8-8920-FC7706FBED77@news.elevatesoft.com...
> Uli,
>
> Yeah, that's what I had in mind when I mentioned a script with cursors.
> I can't think of anything better than that, as I can't think of any way to
> apply a 'live' filter on a column that is not in the base table.
>
> --
> Fernando Dias
> [Team Elevate]
>

Ben,

The solution you are describing seems to be equivalent to what Uli is doing, the difference being you are doing it with Delphi code and Uli with PSM/SQL - a good alternative for those who prefer to use navigational methods instead of SQL.

Discussions like this one are highly valuable - keeping these details in mind can save us a lot of time.
Thank you guys, mainly Uli for bringing this up and sharing your conclusions.

--
Fernando Dias
[Team Elevate]

Fernando,

> The solution you are describing seems to be equivalent to what Uli is
> doing, the difference being you are doing it with Delphi code and Uli
> with PSM/SQL - a good alternative for those who prefer to use
> navigational methods instead of SQL.

Thanks as well for your help - though I'm not sure if there isn't a way
to accomplish the task by a single statement. I'd like to wait for Tim
to chime in here.

Best regards Uli