Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE
On 02/03/2015 11:09, hv...@users.sourceforge.net [firebird-support] wrote: a) if you really need to run hundreds or thousands queries per second against external database i'd say you have a problems with design\architecture, sorry That's exactly the sort of opinion based on real experience of trying it that I was after, thanks. b) you still don't read docs carefully: just run any query against external db using COMMON transaction and all your subsequent external queries against same external db will use the same external connection (for both COMMON and AUTONOMOUS tx). Until local transaction alive, of course. Ah! - you're saying that a workaround to AUTONOMOUS closing the connection is to perform a do-nothing COMMON statement first, and then do the AUTONOMOUS ones. Yes, one can deduce that from the documentation, but it hardly makes it clear that this is a deliberate feature! - and I must say it does rather look slightly more like an accident of implementation than a deliberate feature to me. -- Tim Ward
Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE
> ---In firebird-support@yahoogroups.com, wrote : > 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? a) if you really need to run hundreds or thousands queries per second against external database i'd say you have a problems with design\architecture, sorry b) you still don't read docs carefully: just run any query against external db using COMMON transaction and all your subsequent external queries against same external db will use the same external connection (for both COMMON and AUTONOMOUS tx). Until local transaction alive, of course. Regards, Vlad PS Real pooling of external connections could be implemented in fb3, but no promise so far
Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE
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,
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, 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
Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE
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
[firebird-support] Re: ON EXTERNAL DATA SOURCE
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
