„it’s obvious that the mechanism of detecting if data is obsolete
should be implemented on the server side“
Well, it already IS THERE on the server, you just have to ask it from you app
It look so obvious to me, trivial.
I call this mechanism „smart caching“, here it is:
Allways store creation and modification date of every object you read from
– creation and modification dates of all objects. Works from SQL2005 on
SELECT NAME , create_date , modify_date FROM sys . all_objects
First time your cache is empty, so you gather full data about object (e.g.
table: its columns, indexes, foreign keys, constraints, comments etc)
and store it in the cache, together with modify_date.
Next time you just check modify_date in sys.all_objects for just relevant ID’s
for that object (table, its constraints, etc). If modify_date is still the same,
display cached data.
If it is not the same, data is obsolete: gather full data about object again
(refresh object data).
If it is not in sys.all_objects at all, object was deleted, and remove it from
For SQL2000 you don’t have modify_date, so you allways have to gather full data.
That is way better than displying WRONG data.
Like many pro’s I don’t use wizard to create objects (e.g. procedures). I write
code directly and TOAD currently does’n see that objects were changed by sql and
diplays wrong, old version from obsoleted cache.
DBA that has so fragile production that can’t handle single SELECT, should not
give toad or similar tools to connect to their databases at all. Measure the
impact, don’t assume. But with smart caching, impact is really minimal,
virtually none. Without it’ impact is so low, that is insignificant percentage
comparing to production full load DB takes regularly. To get CORRECT data is
more important than super-quickly get WRONG data.
Even for sql2000 you can have some optimisations in checking for new and deleted
– check for new objects. Works from SQL2000 on
SELECT name , crdate FROM sysobjects