Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
> On 2/8/16, Johannes <jotpe@posteo.de> wrote:
>> Am 08.02.2016 um 21:17 schrieb Vitaly Burovoy:
>>> On 2/8/16, Johannes <jotpe@posteo.de> wrote:
>>>> Am 08.02.2016 um 20:15 schrieb David G. Johnston:
>>>>> On Mon, Feb 8, 2016 at 12:05 PM, Johannes <jotpe@posteo.de> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> is there a best practice to share data between two select statements?
>>>>>>
>>>>>> Imaging following situation: I want to receive two result sets from
>>>>>> two
>>>>>> tables, referring to a specific id from table t0 AND I try not to
>>>>>> query
>>>>>> for that specific id a second time.
>>>>>>
>>>>>> Table t0 returns 1 row and table t1 returns multiple rows.
>>>>>>
>>>>>> begin;
>>>>>> select id, col1, col2, ... from t0 where id = (select max(id) from t0
>>>>>> where col1 = value1 and col2 = value2 and ...);
>>>>>> select col1 from t1 where t0_id = (select max(id) from t0 where col1 =
>>>>>> value1 and col2 = value2 and ...);
>>>>>> commit;
>>>
>>> Hmm. Could you clarify why you don't want to pass id from the first
>>> query to the second one:
>>>
>>> select col1 from t1 where t0_id = value_id_from_the_first_query
>>>
>>
>> Of course I could do that, but in that case I would not ask.
>>
>> I thougt there could be a better solution to execute all statements at
>> once.
>
> What the reason to execute all statements which return different
> columns at once?
>
>> Saving roundtrips,
>
> In most cases they are not so big. Getting a bunch of duplicated data
> is wasting you network bandwidth and don't increase speed.
In my and your example no duplicated data (result sets) is send over the
network. The server do not need to wait until the client snips out the
id and sends it id in the next query again. So the server can compute
the result set without external dependencies as fast as possible.
>> increase speed,
>
> Speed will be at least the same. In your case either you have to use
> more DDL (like CREATE TEMP TABLE) or get copied columns that leads
> more time to encode/decode and send it via network.
The time difference is small, yes.
My old variant with executing the first select, remember the returned id
value and paste it into the second query and execute it takes 32ms.
Your temp table variant need 29ms. Nice to see. That are 10% speed
improvement.
>> a more sophisticated solution,
>
> It usually depends on a task. Your case is simple enough and can't
> lead any sophisticated solution. =(
>
No problem.
>> learn something new...
>
> It makes sense. =)
>
>> Johannes
Good night.