[CodeButterfly]

Looking for efficient sql to use for an CDbCacheDependency that can handle new records, modified records, or deleted records. I've been using sql such as:

SELECT MAX(modified) FROM post

However, I realized that although this handles creations and modifications, it doesn't handle deleted records.
Came across this forum post, CDbCacheDependency subsequvent calls, which discusses using AVG instead of max which sounds pretty decent (almost effectively creating a hash of the date values):

SELECT AVG(UNIX_TIMESTAMP(last_updated)) FROM post

However, wondering if this is the best method or are there alternatives?

[redguy]

in mysql you can get table last modyfication timestamp:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

it will cover any changes, including deletions. I do not know about such mechanism in other dbms...

[CodeButterfly]

redguy, on 13 November 2012 - 02:22 AM, said:

in mysql you can get table last modyfication timestamp:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

it will cover any changes, including deletions. I do not know about such mechanism in other dbms...

Thanks that looks promising except I'm reading this only works for MyISAM tables and not InnoDB (which I am using).

[redguy]

CodeButterfly, on 13 November 2012 - 04:10 AM, said:

Thanks that looks promising except I'm reading this only works for MyISAM tables and not InnoDB (which I am using).

could be... I was using it only for MyISAM tables

[softark]

Hi Codebutterfly and redguy,

One thing I'm not very sure is the performance hit of AVG function.
I guess MAX might not be very heavy when you have an index on the column, but what about for AVG? I'm afraid that DBMS has to read all the records to calculate the average value.

[CodeButterfly]

That's a good point. For my particular use case there will probably only be in the hundreds of records max so probably not that big a deal. Do you have any suggestions for an alternative? Two other thoughts I've had is:

1. make a special cache dependency table where values are updated whenever relevant data changes. Similar to ASP.NET's SqlCacheDependency implementation. See Using SQL Cache Dependencies (C#):

Understanding Notification and Polling said:

With polling, the database must be configured to include a table named AspNet_SqlCacheTablesForChangeNotification that has three columns - tableName, notificationCreated, and changeId. This table contains a row for each table that has data that might need to be used in a SQL cache dependency in the web application. The tableName column specifies the name of the table while notificationCreated indicates the date and time the row was added to the table. The changeId column is of type int and has an initial value of 0. Its value is incremented with each modification to the table.

In addition to the AspNet_SqlCacheTablesForChangeNotification table, the database also needs to include triggers on each of the tables that may appear in a SQL cache dependency. These triggers are executed whenever a row is inserted, updated, or deleted and increment the table s changeId value in AspNet_SqlCacheTablesForChangeNotification.

2. use something like:

SELECT CONCAT(CAST(MAX(modified) AS CHAR), COUNT(*)) FROM post

If a record is added the max modified will change (as well as the count), if a record is deleted the count will change, if a record is added and deleted at nearly the same time the count will stay the same but the max modified will change. Not sure how the performance of this would compare to using AVG.

[softark]

CodeButterfly, on 14 November 2012 - 01:43 PM, said:

2. use something like:

SELECT CONCAT(CAST(MAX(modified) AS CHAR), COUNT(*)) FROM post

Wow, nice.

[redguy]

CodeButterfly, on 14 November 2012 - 01:43 PM, said:

That's a good point. For my particular use case there will probably only be in the hundreds of records max so probably not that big a deal. Do you have any suggestions for an alternative? Two other thoughts I've had is:

1. make a special cache dependency table where values are updated whenever relevant data changes. Similar to ASP.NET's SqlCacheDependency implementation. See Using SQL Cache Dependencies (C#):




2. use something like:

SELECT CONCAT(CAST(MAX(modified) AS CHAR), COUNT(*)) FROM post

If a record is added the max modified will change (as well as the count), if a record is deleted the count will change, if a record is added and deleted at nearly the same time the count will stay the same but the max modified will change. Not sure how the performance of this would compare to using AVG.

the second is quite nice, but I would go with the first one: create table last_table_modyfications (table_name varchar primary key, last_modyfication datetime) and put in that table record for every table that should be tracked, like ('post', now()) and then update that table with db triggers on after insert,update,delete. this will be fast (it is simple hash table), relayable and as most db engines have at least sql triggers - should work everywhere.
In mysql you could create such table as HEAP table so it will be stored in memory (but you should then handle missing table record in triggers because on server restart all data will be gone)!

[softark]

@redguy
Sounds quite a reasonable solution. Thanks.

[VINAY Kr. SHARMA]

It could be:

-- PostgreSQL
SELECT
    	 CAST(MAX(id) AS TEXT) || CAST(COUNT(*) AS TEXT) || CAST(MAX(created_on) AS TEXT) || CAST(MAX(modified_on) AS TEXT) AS "dependency"
FROM Post;

-- MySQL
SELECT
    	CONCAT(MAX(id), COUNT(*), MAX(created_on), MAX(modified_on)) AS `dependency`
FROM Post;