What is the fastest query to check for an existence of a row
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).
Re: What is the fastest query to check for an existence of a row
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).
Re: What is the fastest query to check for an existence of a row
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 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).
