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

[CRPence <CRPbottle@xxxxxxxxx>]

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.