0

What should be another alternative from indexing, if I want to quickly know the length of the query first before executing a potential slow query (such as multiple regions with a long date range)?

Indexing query, Counting with subquery of query, Ways to make compress query

crs.execute(query) #from fdb cursor

if crs.fetchone() is None:
   #notify user there is no results
else:
   #do stuff

I want to notify user quickly if there is no results in query instantly/faster.

Ashish
  • 6,791
  • 3
  • 26
  • 48
Joey
  • 93
  • 1
  • 9
  • There is no other way: the query will need to produce at least one row before you can know there is a row, and it will need to have completed before you can know there are no results at all. – Mark Rotteveel Jul 17 '19 at 17:51
  • I'm not 100% sure of the internals of FDB, but in Firebird, queries are fetched in batches, so the query doesn't need to have produced all rows before you know there are rows. – Mark Rotteveel Jul 17 '19 at 17:52
  • So what do you want to know? Knowing "length of query" - total count of rows - or knowing if the query is empty or has at least one row? Those are different tasks. In latter case indeed just do a query and pick first row, if any. – Arioch 'The Jul 18 '19 at 08:54
  • @MarkRotteveel okay thanks for confirming. – Joey Jul 18 '19 at 15:11
  • @Arioch'The I actually have to do both: find if query is not empty and count the query results. My current method is slow, so I was just looking for a quicker method/option. Thanks for the response. – Joey Jul 18 '19 at 15:15
  • @Joey notice there is no exact solution to your latter wish: while the query is working some another query from another connection may be adding new rows, new data. `read committed` isolation, right? More so, you can run two queries, first would `select count(*) ...` and the second would actually fetch data. And there is even more chances extra data would be inserted and committed between those two queries. So, you can not complete data search before you complete it. Also, since FB can `select` from `stored procedures` fortunetelling "total count" before fetch EOF is yet harder – Arioch 'The Jul 19 '19 at 08:28
  • Also, it is very rare thing that human user can read (actually read) more than 100 rows of data in the very first 2-3 seconds. So, while insta-setting scrollbars length feels cute, but there is less of practical need for it. You know the query is not-empty by the fact of reading 1st row, the next milestone is reading "first page" of data rows. The rest can go in background, if it ever would be needed (there were requests form people, whose application was piping data from network for 15 minutes and then crashing out-of-memory, in the attempt to get & count ALL-DATA-TO-LAST-DROP) – Arioch 'The Jul 19 '19 at 08:31

0 Answers0