Re: [firebird-support] Puzzled by difference in execution time

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 m...@lawinegevaar.nl
[firebird-support] :

> On 3-3-2018 17:43, setysvar setys...@gmail.com [firebird-support] wrote:
> > The execute blocks had similar similar content to:
> >
> > for select mt.PK
> > from  tt
> > join  mt on 
> > where mt.integerfield = 1 into :PK do
> > begin
> > suspend;
> > update 
> > 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 
> 
>
> ++
>
> 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
>
>
>
>

Re: [firebird-support] Puzzled by difference in execution time

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 3-3-2018 17:43, setysvar setys...@gmail.com [firebird-support] wrote:
> The execute blocks had similar similar content to:
> 
> for select mt.PK
> from  tt
> join  mt on 
> where mt.integerfield = 1 into :PK do
> begin
>     suspend;
>     update 
>     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