Yii Framework Forum: Cdbcachedependency Effective Sql That Handles Modifications And Deletions? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Cdbcachedependency Effective Sql That Handles Modifications And Deletions? Rate Topic: -----

#1 User is offline   CodeButterfly 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 30-August 12

Posted 12 November 2012 - 06:51 PM

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?
0

#2 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 815
  • Joined: 02-July 10
  • Location:Central Poland

Posted 13 November 2012 - 02:22 AM

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...
red
1

#3 User is offline   CodeButterfly 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 30-August 12

Posted 13 November 2012 - 04:10 AM

View Postredguy, 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).
0

#4 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 815
  • Joined: 02-July 10
  • Location:Central Poland

Posted 13 November 2012 - 04:30 AM

View PostCodeButterfly, 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
red
0

#5 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,084
  • Joined: 16-February 11
  • Location:Japan

Posted 14 November 2012 - 02:28 AM

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.
0

#6 User is offline   CodeButterfly 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 30-August 12

Posted 14 November 2012 - 01:43 PM

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.
1

#7 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,084
  • Joined: 16-February 11
  • Location:Japan

Posted 14 November 2012 - 07:03 PM

View PostCodeButterfly, on 14 November 2012 - 01:43 PM, said:

2. use something like:
SELECT CONCAT(CAST(MAX(modified) AS CHAR), COUNT(*)) FROM post



Wow, nice. :D
0

#8 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 815
  • Joined: 02-July 10
  • Location:Central Poland

Posted 15 November 2012 - 02:16 AM

View PostCodeButterfly, 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)!
red
2

#9 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,084
  • Joined: 16-February 11
  • Location:Japan

Posted 15 November 2012 - 02:57 AM

@redguy
Sounds quite a reasonable solution. :) Thanks.
0

#10 User is offline   VINAY Kr. SHARMA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 151
  • Joined: 13-September 11
  • Location:Gurgaon

Posted 29 January 2013 - 05:45 AM

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;

VINAY Kr. SHARMA
@vinaykrsharma
Facebook ID: vinaykrsharma
Blog

Co-operation comes from friendship, friendship comes from trust, and trust comes from kind-heartedness.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users