Re: How to prevent jdbc from sending any results back to the client ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 21 April 2014 11:30, Dimitris Karampinas <dkarampin(at)gmail(dot)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
> -dk
>
>
> On Mon, Apr 21, 2014 at 8:01 AM, Mark Kirkwood <
> mark(dot)kirkwood(at)catalyst(dot)net(dot)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(at)jackdb(dot)com
>>>> <mailto:sehrope(at)jackdb(dot)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(at)gmail(dot)com <mailto:dkarampin(at)gmail(dot)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
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>