More SP ODBC questions

[Paul Henson]

To head off the "you shouldn't be using that" recommendation ;), when 
I've got my consultant hat on the client gets what the client wants and 
they think this is the best infrastructure for their requirements :).

I was reviewing the underlying database details; it's not exactly a 
complicated database schema, there are only two tables with five columns 
each. Looks like there are eight unique SQL statements executed against 
the database; based on the primary key in the DDL on the documentation page:


INSERT INTO <table> VALUES (context, key, expiration, 1, value)

This one will either have a primary key conflict and fail or immediately 
succeed.


SELECT version, expires, value FROM <table> WHERE context=? AND id=? AND 
expires > ?

This one should immediately find a row based on the primary key, check 
the expires value, and return the row or nothing, or immediately not 
find the row.


UPDATE <table> SET value=?, version=version+1, expires=? WHERE context=? 
AND id=?

This one also should immediately find or not find the row based on the 
primary key.


DELETE FROM <table> WHERE context=? AND id=?

Again immediately find or not find using primary key.


UPDATE <table> SET expires=? WHERE context=? AND expires > ?

This one will need to do a full table scan looking for rows with the 
matching context value and check their expiration.


DELETE FROM <table> WHERE context=? AND expires <= ?

This one also does a full table scan for the context and expiration.


DELETE FROM <table> + WHERE expires <= ?

This one does a full table scan on the expires column.

DELETE FROM <table> WHERE context=?

Another full table scan on the context column.


Three of these queries require full table scans to complete. I'm curious 
why there is no recommendation to create indexes on:

context,expires
expires
context

to avoid this? The trade-off would be the additional overhead of 
maintaining the indexes versus the overhead of the full table scans. The 
best decision is somewhat based on the total row count which will vary 
per deployment, but I was just wondering if somebody had considered the 
load pattern and intentionally decided not to recommend these indexes or 
if the documentation is simply the minimum required to get it working 
and any further tuning or optimization is left as an exercise for the 
reader :).

Thanks…

-- 
Signet - The Art of Access
https://www.signet.id/