Subject Re: [firebird-support] Puzzled by difference in execution time
Author Svein Erling Tysvær
Post date 2018-03-05T11:32:33Z
At first I just tried

UPDATE ...
SET  field1 = 'K', field2 = '-'
WHERE EXISTS(...

and changed to EXECUTE BLOCK after that. First (for testing) I used ROWS 1 (or ROWS 5) in the FOR SELECT and it was still time consuming, although it finished within a minute or so.

I've simply never thought of using MERGE when just updating and not inserting (and been too poor at using it otherwise), I'll see if it is possible to change it like this, though I don't have high hopes that it will be any quicker.

The columns returned is the PK of the table that is updated (an integer) and the number of rows updated so far (also integer). Each update should only update one row, so this is there just to be certain that the correct number of rows are updated.



2018-03-05 9:53 GMT+01:00 Mark Rotteveel mark@... [firebird-support] <firebird-support@yahoogroups.com>:
On 3-3-2018 17:43, setysvar setysvar@... [firebird-support] wrote:
> The execute blocks had similar similar content to:
>
> for select mt.PK
> from <temporary table> tt
> join <normal table> mt on <join criteria>
> where mt.integerfield = 1 into :PK do
> begin
>     suspend;
>     update <normal table>
>     set field1 = 'K', field2 = '-'
>     where pk = :pk;
>     MyCount = MyCount + rows_affected;
> end
>
> The join criteria were
>
> (slow)
> tt.VarChar40 = mt.VarChar40
>
> (quick)
> tt.int1 = mt.int1 and tt.int2 = mt.int2
>
> Running the select part without the update is quick in both cases.
>
> Can anyone enlighten me regarding what the cause of the performance
> difference may be? I'm simply puzzled and clueless...

What happens if you use MERGE instead of doing the updates 'manually',
or if you remove the SUSPEND statement? Which columns is the EXECUTE
BLOCK returning?

Mark
--
Mark Rotteveel


------------------------------ ------
Posted by: Mark Rotteveel <mark@...>
------------------------------ ------

++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/ resources/documents/

++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++
------------------------------ ------

Yahoo Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/ firebird-support/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/ firebird-support/join
    (Yahoo! ID required)

<*> To change settings via email:
    firebird-support-digest@ yahoogroups.com
    firebird-support-fullfeatured@ yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    firebird-support-unsubscribe@ yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/ us/yahoo/utos/terms/