db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Re: What is the fastest query to check for an existence of a row
Date Thu, 26 May 2016 00:38:23 GMT
Thank you Kristian.  That is exactly what I found as well but was waiting for the email to
show up so I could reply to my own.

Wow what a difference that made!  I keep scouring the documentation and find some new nugget
each time.

Thank you very much for taking the time to reply and confirm what I though I had found as
being best (after the question of course)

> On May 25, 2016, at 6:16 PM, Kristian Waagan <kristwaa@apache.org> wrote:
>
> Den 25.05.2016 23.12, skrev Bergquist, Brett:
>> I have a table with 80,640,704 rows.   My query currently is:
>>
>>
>>
>> SELECT
>>
>> COUNT(*) AS USE_COUNT
>>
>> FROM
>> PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
>> CBCE
>>
>> WHERE CBCE.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 768
>>
>> AND CBCE.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID != 2
>>
>>
>>
>> This query is going to return a count of 78,569.
>>
>>
>>
>> What I really want to know is if there is any row that satisfies the
>> condition.   I have logic that needs to run if there is such a row.   So
>> is there a more efficient query in Derby that will short circuit when it
>> find a row?
>
> Hi Brett,
>
> FETCH/OFFSET [1] comes to mind, i.e. going from
>
> SELECT COUNT(*)
> FROM CBCE
> WHERE ...
>
> to
>
> SELECT some_column_or_a constant
> FROM CBCE
> WHERE ...
> FETCH FIRST ROW ONLY
>
>
> Since there's no ORDER BY (or other relevant operations / clauses) in
> your query, that should hopefully cause Derby to stop processing and
> return once the first matching row is found.
> I say hopefully, because I haven't verified it :)
>
>
> Regards,
> --
> Kristian
>
>
> [1] https://db.apache.org/derby/docs/10.12/ref/rrefsqljoffsetfetch.html
>
>>
>>
>>
>> Thanks
>>
>>
>>
>> Brett
>>
>>
>> ------------------------------------------------------------------------
>> Canoga Perkins
>> 20600 Prairie Street
>> Chatsworth, CA 91311
>> (818) 718-6300
>>
>> This e-mail and any attached document(s) is confidential and is intended
>> only for the review of the party to whom it is addressed. If you have
>> received this transmission in error, please notify the sender
>> immediately and discard the original message and any attachment(s).
>


Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300

This e-mail and any attached document(s) is confidential and is intended only for the review
of the party to whom it is addressed. If you have received this transmission in error, please
notify the sender immediately and discard the original message and any attachment(s).

Mime
View raw message