1

I'll keep the scenario simple:

  • There is an app, and there is a database.
  • In the database, there is a table with a large amount of records
  • I feed the app these records regularly

Now, one reload can imply some of these changes: i. New records in set ii. Updated records in set iii. Deleted records in set

Finally, for records that remain the same I can make use of a diferent process that works as much as 100 times faster than reading the table through ODBC.

Knowing this, I am trying to reduce the reloading time by using the ODBC as little as possible and midway through my algorithm I encounter a question that I have not been able to solve.

**How much faster is a select count(*) where [condition] opposed to select columnName where [condition]

Why? Because just by reloading the new and updated records, I can calculate whether there have been deletions or not by counting records depending on [condition]. Then, if there have been deletions, I'll have to read from the database those that remain. Nornally, the amount of new and updated records will be a small portion of the whole set. If a count is considerably faster than a normal select, I might want to use it as a fast check, since deleting records is not a common activity.

7
  • 1
    Which DBMS are you using? But in general the count(*) will be faster as it will only return a single row whereas the other query will return many rows
    – user1822
    Apr 13, 2017 at 10:22
  • I honestly do not know just yet. All I know is that it will be located in a datalake and that I will mosy likely be asked to use Impala. My guess is that it will be an Oracle (MariaDB).
    – Feillen
    Apr 13, 2017 at 10:55
  • Anyway, is that the only difference? The amount of rows returned (and its content ofc)? Is there no performance difference apart from the "building" of more rows?
    – Feillen
    Apr 13, 2017 at 10:57
  • I consider that a huge difference... (Also MariaDB and Oracle are very different database systems)
    – user1822
    Apr 13, 2017 at 11:44
  • Is that so? Newby me thought it didn't really make that big a difference in query time. Thank you very much! (Also, I do not know what crossed my mind to link Oracle and MariaDB, sry about that)
    – Feillen
    Apr 13, 2017 at 11:57

1 Answer 1

3

It really depends on a database you use while executing the query and gathered statistics.

  1. Oracle DB. If stats have already been gathered , the first query would return the amount of rows in 0.00001 sec. If have not been, then it will be faster in five more times than while executing the second query as it is retrieving large amount of rows instead of just one.
  2. For MS SQL will be almost the same thing.
  3. For MySQL or MariaDB it depends on the engine. But I assume these databases always try caching the value of amount of rows as the optimizer will do queries faster.

In addition, columns in WHERE clause and in COUNT() do matter as well. They could be indexes or not and etc. There are lot of criteria considering perfomance between the queries but in the most cases the first one is recommended for using to get amount of rows. I could tell you more precisely if I had certain example.

2
  • Nice and detailed explanation :D just to top it all, there would not happen to be a kind of formula to compare the results depending on the parameters that you point out, right? Or I just have to try the different options to know? (I'm not being lazy, I just like to work on paper)
    – Feillen
    Apr 14, 2017 at 10:54
  • I think it will be a lot of parameters in a function depending on a situation... Too heavy to write Apr 14, 2017 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.