What is the fastest query to check for an existence of a row

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

What is the fastest query to check for an existence of a row

Bergquist, Brett-2

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?

 

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).
Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest query to check for an existence of a row

kristwaa
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).

Reply | Threaded
Open this post in threaded view
|

Re: What is the fastest query to check for an existence of a row

Bergquist, Brett-2
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 <[hidden email]> 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).