Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE

[Tim Ward t...@telensa.com [firebird-support]]

Re (1), I somehow managed to miss that sentence - thanks.

Re (2), let me put the question another way.

Given that it's going to close the connection every time, rather than 
re-use it, if I'm wanting to do this hundreds or thousands of times from 
within a single stored procedure call in database A, will the overhead 
of reconnecting to database B hundreds or thousands of times mean that 
using WITH AUTONOMOUS TRANSACTION is likely to be an impractical design 
in performance terms? How many hundreds or thousands of times per second 
can a connection be set up and torn down?

Sure I could set up a pair of servers, write some code, and measure the 
performance. But I was hoping there might be someone who had used this 
feature who might be able to give general advice along the lines of 
either no chance, you don't want to do it like that, it'll run like a 
fast-asleep snail or we do that all the time, there's no performance 
issue in practice, setting up the connection is a fraction of the cost 
of running a statement that actually does anything.

-- 
Tim Ward

Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE

[Tim Ward t...@telensa.com [firebird-support]]

Yes, I did, of course, you will see that not only do I quote the 
documentation but also that my questions are on points that the 
documentation doesn't answer, which is why I asked them here.


On 27/02/2015 18:57, hv...@users.sourceforge.net [firebird-support] wrote:


Read the documentation first
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html

Regards,
Vlad




--
Tim Ward

Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE

[hv...@users.sourceforge.net [firebird-support]]

  Tim,

your question:

 (iii) I can't find any documentation of when the connection on database 
B get closed, and when and under what circumstances the transaction on 
database B gets committed and when and under what circumstances it gets 
rolled back


documentation 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-trans
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-trans
 
answer:
 WITH {AUTONOMOUS|COMMON} TRANSACTION ...
 Any new transactions started under the “COMMON” regime are committed or rolled 
back with the current transaction. 
 

 
Then you ask again:
 (ii) Each time the statement run in (b) completes the transaction in 
 database B will be committed, unless there was an error in which case it 
 will be rolled back
...
isn't it rather expensive to reopen the connection to database B every time?


Of course reopen connection is more expensive then reuse existing connection ;) 
But...

...read the docs
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-on-external
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-on-external
 

 Connection pooling: 
 
 External connections made by statements WITH COMMON TRANSACTION (the default) 
will remain open until the current transaction ends. They can be reused by 
subsequent calls to EXECUTE STATEMENT, but only if the connect string is 
exactly the same, including case. 
 External connections made by statements WITH AUTONOMOUS TRANSACTION are closed 
as soon as the statement has been executed. 
 Notice that statements WITH AUTONOMOUS TRANSACTION can and will reuse 
connections that were opened earlier by statements WITH COMMON TRANSACTION. If 
this happens, the reused connection will be left open after the statement has 
been executed. (It must be, because it has at least one uncommitted 
transaction!) 
 

So, please, read the documentation !

Regards,
Vlad


---In firebird-support@yahoogroups.com, tdw@... wrote :

 Yes, I did, of course, you will see that not only do I quote the documentation 
but also that my questions are on points that the documentation doesn't answer, 
which is why I asked them here.
 
 On 27/02/2015 18:57, hvlad@... mailto:hvlad@... [firebird-support] wrote:

   Read the documentation first
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
  
 Regards,
 Vlad
 
 
 -- 
Tim Ward

  

[firebird-support] Re: ON EXTERNAL DATA SOURCE

[hv...@users.sourceforge.net [firebird-support]]

Read the documentation first
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
  
Regards,
Vlad