On 30 Jun 2011, at 11:06, Igor Tandetnik wrote:
> On 6/30/2011 1:43 PM, Simon Gornall wrote:
>> Well, perhaps I'm missing something, but if the statement for which I'm
>> caching results was something like
>>
>> SELECT * FROM tableName WHERE columnName = zzz;
>>
>> I'd only want to clear the results cache when statements like:
>>
>> UPDATE tableName set columnName = xxx where id=yyy;
>>
>> were processed. I'd specifically *not* want to clear the results-cache for
>> the above statement when I got:
>>
>> UPDATE tableName set otherColumnName = xxx where id = yyy;
>>
>> ....because changing values in the column 'otherColumnName' can't affect the
>> results-list for a query only dependent on the column 'columnName'.
>
> What do you mean, can't affect? The SELECT statement is returning the
> values from otherColumnName in the resultset. If changing those doesn't
> affect it, I don't know what does.
I don't care if an update is made to the *value* of 'otherColumnName'. I only
care if the set-of-objects-that-would-be-returned could differ, not the
properties of those objects.
The code implements an object-relational mapping API. Assuming I'm re-fetching
the values for a SELECT that has happened previously, I already have the
objects cached in RAM. If an update has taken place on that object in-between
the first SELECT and this second SELECT, two things have occurred during that
update:
- the change was made to the in-memory cached object
- the change was added to the change-list for that object
... when the transaction commits, I use the change-list to write out the
changes for that object to the database.
When a SELECT happens, I check to see if the object UUID matches that in the
cache, if it doesn't, a new object is created and added to the cache. If it
does, I don't bother creating a new object, I return the same one from the
cache. This is a significant time advantage (~50x) because of the time taken to
create new objects. What I'm trying to also do is remove the requirement to
round-trip to the database *even when* there is a variadic WHERE clause in the
SQL statement being executed. I can do this by caching the results of the
previous statements (as a set of objects) and invalidating that cache entry
when the results might change.
So, I don't care if the object values have changed (I already have those
changes in the RAM versions of the objects). What I care about is if the list
of objects that would be returned by the query is now different. Hence my
comments above.
Cheers
Simon
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
