Re: Should I replace all CHAIN, SETLL, and READs to SQL

On 02 Nov 2012 12:46, Briggs, Trevor (TBriggs2) wrote:

Did you benchmark SELECT COUNT(*) FROM XXXX WHERE..... ?

I've found this much faster than SELECTing other ways for just
checking a record's existence.

When using predicates across all key columns that definitively select just one row via an established UNIQUE KEY or PRIMARY KEY constraint? Otherwise, or more generally for more generic query row-exists SELECT, ...

Why ask for the count of all rows matching some selection, unless the actual count was required? If the implementation of the query requires a full table scan, then instead of stopping after finding existence of just one matching row, the database must complete scanning every row to obtain that count. While the Storage Management can be very efficient to implement that, the potential for contention on disk access across the jobs on the system could negatively impact the overall throughput of the system.

Both an EXISTS predicate and the FETCH FIRST 1 ROW ONLY clause asks via the SQL that the database should stop searching after encountering the first matching row. If one could be positive that the query implementation will be with a known compatible INDEX which can be /probed/ for the existence of the row(s) and the database easily be able to know [all key values] or quickly count the rows [partial key] under a particular node [which as /random access/ that the data is effectively clustered\already-paged would be desirable]. But if one can presume so much, then selecting only [any of the key columns or] a literal value enables an index-only implementation [possibly same as the noted /fast/ COUNT(*) queries] for which no actual row data is accessed to conclude the query. With either of these non-aggregate queries, the implication is that the [sub]query will be performed as though the /hint/ OPTIMIZE FOR 1 ROW has been coded on the SQL request.