XML Feeds
COUNT(*) vs OPEN FETCH vs WHERE EXISTS
Golden Rule - If you don't care about how many records there are, but only that only *one* exists... then do not count all the records. Instead use a method that check to see if one record exists and exits the check.
Did some research over the weekend. I think you will be able to see from the examples below how what is easy to do and typical, can actually hurt a lot. I took a large table, and performed three methods of counting for records more than 0.
I learned a new one over the weekend that uses an EXISTS clause. I really like this version because it is clearly understood what it is doing whereas the FETCH method is somewhat ambiguous. Sometimes you may find that the count(*) method seems just as fast the “where exists” method, this is misleading more often than not. The number of consistent gets performed against the database can be dramatically increased using the count(*) method as compared to the “where exists” method. Where the data is located in the table can blur the difference detected between the two methods as well. The "where exists" will stop scanning as soon as it finds a row -- so when we search for the first row -- it does SIGNIFICANTLY less work. When we look for the last row -- it does about the same amount of work (tiny bit more, it has the "overhead" of scanning DUAL as well) as the typically used SELECT COUNT(*) method.
On average -- the where exists will significantly outperform the count(*). So I like to think of it in terms of this, instead of using the trial and error method to determine which method is okay to use *this* time… instead get used to using a single method that you will behave consistently regardless of the situation. Code ends up being efficient, consistent, and it requires less of your development time in the end.
It should also be noted that SQL Trace will further demonstrate the clear advantages of one method over another. Two methods that operate with the same SQL timing are not necessarily equal when it comes to performance. We must also consider the impact to the database overall, since this can impact other parallel executing processes.
Permalink