[Tfug] more RDBMS

[Glen Pfeiffer]

On 10/22/2007 07:16 PM, Terence Rudkin wrote:
> On 10/22/07, Bexley Hall <bexley401 at yahoo.com> wrote:
>> I'm looking for an inexpensive (computationally)
>> way to see if a "database", or, specifically, a
>> certain *table* has been *changed* since the last
>> time I "noticed" it.
> But you can roll your own solution with a table 
> LAST_TAB_UPDATE, with two columns TABLE_NAME, LAST_CHANGE . 
> Then if updates are all done programmatically update both 
> tables, else if TRIGGERS are supported then on update or insert 
> update the table.
> 
That is precisely the custom solution I would implement if the 
database server does not offer a native solution. And I would 
prefer to use triggers, even though they are not portable across 
database platforms.

>> In a VERY LIGHT DUTY APPLICATION, how foolish is
>> it for clients to periodically issue queries of
>> the RDBMS like:
>>
>>   SELECT now;
>>
>> instead of tracking time "locally", themselves?
>> I.e., use the RDBMS as a time reference instead
>> of burdening the individual clients (which, in
>> this case, are kiosks) with the overhead of a
>> "real" time service?
>>
> the database should use the same time service as the client.  
> So an infrequent sanity check from client to server would be my 
> solution.
>
To answer the OP's question: In a light duty application, 
selecting the current time will not cause any performance issues 
on the database.

Why do you need to do this?

Is it so you can set the local time on the client? That certainly 
seems an awkward design and might not be a good idea.

However, if you are planning on using the time of the server so 
the client can tell whether to retrieve newer data, I am all for 
it. In fact, the client never need see the time the table was 
last updated. You could encapsulate all that logic on the 
database server.

Or maybe you would rather perform the logic from within the 
query? How about:

select
    *
from
    table_a
where
    now >= (
        select changed_on 
        from table_change_log 
        where table_name = 'table_a'
    )

Of course that assumes that there is always data in the table, 
because an empty result set implies there was no change in data. 

-- 
Glen