Re: How to prevent jdbc from sending any results back to the client ?
От | Dave Cramer |
---|---|
Тема | Re: How to prevent jdbc from sending any results back to the client ? |
Дата | |
Msg-id | CADK3HH+JOqjLwxfk+bWQJ39b=jheBautfLW_t4vEsoctFZRXmg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: How to prevent jdbc from sending any results back to the client ? (Dimitris Karampinas <dkarampin@gmail.com>) |
Список | pgsql-jdbc |
On 21 April 2014 11:30, Dimitris Karampinas <dkarampin@gmail.com> wrote:
Thanks for your answers.I need to understand the semantics of the jdbc driver.My question is, what is the behaviour of executeQuery() ?At the beginning I thought it was a blocking call, meaning control comes back to the application right after the query has been fully executed at the server side.But my experiments show that once the server starts producing the first tuples (assume a big join between two tables) executeQuery returns and I can start iterating to the Resultset while the long query continues running at the backend.
The driver can do both. If it uses a cursor. IE you have setFetchSize() then you may get results back from a query before they are all processed.
Can someone verify this ?If this is the case, my next question is, what happens if I call .close() on the ResultSet and the query is still running. Does it get aborted ?
The query will not get aborted
FYI: I use a modified version of PostgreSQL. EXPLAIN ANALYSE and a couple of other tricks I tried don't always work properly.
Then all bets are off. Candidly this isn't a good way to profile postgreSQL. Running queries and analyzing the logs is a better way
Thank you-dkOn Mon, Apr 21, 2014 at 8:01 AM, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote:FWIW: the difference is noticeable, even on modern CPU types (this is an i7 4770):
bench=# EXPLAIN (ANALYZE,TIMING FALSE) SELECT aid,bid FROM pgbench_accounts;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..26394.00 rows=1000000 width=8) (actual rows=1000000 loops=1)
Planning time: 0.066 ms
Total runtime: 80.172 ms
(3 rows)
bench=# EXPLAIN (ANALYZE,TIMING) SELECT aid,bid FROM
pgbench_accounts;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Seq Scan on pgbench_accounts (cost=0.00..26394.00 rows=1000000 width=8) (actual time=0.010..98.167 rows=1000000 loops=1)
Planning time: 0.063 ms
Total runtime: 124.818 ms
(3 rows)
On 21/04/14 17:46, Mark Kirkwood wrote:One possible problem with using EXPLAIN ANALYZE is that the cost of
timing each step can artificially inflate the query time...however you
can avoid this by using the variant:
EXPLAIN (ANALYZE,TIMING FALSE) statement
Which still does the query, but skips timing each step (which I think
probably what you want). It still says how long the entire statement took.
regards
Mark
On 20/04/14 12:22, Dave Cramer wrote:Dimitris,
You would be better off running queries such as explain analyze which do
not return results, but do time the query. Every postgresql client
library will have to wait for the results. That is essentially the way
the protocol works
Dave
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On 19 April 2014 15:02, Sehrope Sarkuni <sehrope@jackdb.com
<mailto:sehrope@jackdb.com>> wrote:
The fetch size only comes into play if your are in a transaction.
You have to disable auto commit and set the fetch size before
executing your query. Otherwise the entire query result will be read
and buffered in memory.
An alternative is to run the command as an EXPLAIN ANALYZE[1]. The
server will then execute the entire operation but instead of sending
back the data it will send the query plan and runtime statistics.
[1]: http://www.postgresql.org/docs/9.3/static/sql-explain.html
Regards,
Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | http://www.jackdb.com/
On Apr 19, 2014, at 2:48 PM, Dimitris Karampinas
<dkarampin@gmail.com <mailto:dkarampin@gmail.com>> wrote:Hi,
I'm working on an academic project and I need to benchmark
PostgreSQL.
I'm intersted only about the performance of the DBMS itself and
I'm trying to keep things simple in my measurements.
Preferably I'd like to ignore the query results at the client side
but jdbc seems to return results even if I don't call next() on
the Resultset (is that true ?).
As a consequence, I can't measure acurately a per query execution
time since the time I get depends also on the time spent to send
the answer (or part of it) to the client.
setFetchSize(1) doesn't seem to help much.
Can I hack the driver and diminish the overhead explained above ?
Cheers,
Dimitris
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: Dimitris KarampinasДата:
Сообщение: Re: How to prevent jdbc from sending any results back to the client ?
Следующее
От: Jeff JanesДата:
Сообщение: Re: Postgres jdbc build broken with plain ant since e7c2c93