On 30 Jun 2011, at 11:42, Simon Slavin wrote:
>
> On 30 Jun 2011, at 7:28pm, Simon Gornall wrote:
>
>> 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.
>
> Do a
>
> SELECT group_concat(rowid) WHERE …
>
> when you do your original fetch and when you want to do the check, and
> compare the two results. The answer returned is just one long string so all
> you need is a routine to compare the two strings and see if they're equal.
>
> SQLite is extremely efficient at this stuff, and you're unlikely to be able
> to improve on it for your purposes without writing your own database system
> from the ground up.
I'm not really sure I'm explaining myself well here. Let me try again:
I've got models (basically object-instances of a database row) and entities
(handle database operations + misc admin stuff for a table). The entity has a
cache of all the models that have previously been selected from its table.
When I perform a query, the following happens:
- create a prepared statement or fetch from the statement-cache if
possible
- execute statement with arguments
- step over each row returned:
- does the uuid (a standard column) match one in our cache ?
- yes: add the model from the cache to the
set-of-results
- no: create a new object, add to the cache, add object
to set-of-results
- return set-of-results
What I've just added is a cache-of-results associated with a hash of (the
statement and the arguments to that statement) within the entity. Now the above
sequence is modified to:
- create a prepared statement or fetch from the statement-cache if
possible
- check if cached-result already available
- yes: return cached-result
- no: execute statement with arguments
- step over each row returned:
- does the uuid (a standard column) match one
in our cache ?
- yes: add the model from the cache to
the set-of-results
- no: create a new object, add to the
cache, add object to set-of-results
- cache set-of-results as cached-result
- return set-of-results
Running some simple timings (fetch 1000 objects based on the same query
criteria each time), I have:
Test Case '-[testResultCache testResultsCache]' started.
1st pass (no caches) : (1000 models) 0.053308
2nd pass (Q cached) : (1000 models) 0.001687
3rd pass (Q,R cached) : (1000 models) 0.000019
4th pass (Q cached) : (1000 models) 0.002163
Test Case '-[testResultCache testResultsCache]' passed (0.074 seconds).
In this case, the passes are as follows:
- 1st pass : nothing in cache, create all objects afresh, return
set
- 2nd pass : prepared statement cached, objects all cached
- 3rd pass : prepared statement cached, objects all cached,
result-set cached
- 4th pass : change made to an object therefore need to update
that object, discard result-set and then same as 2nd-pass
So, the goal is to maximise the number of times the 3rd-pass style can be what
is actually being used. At the moment, if an entity gets *any* write operation,
I discard *all* entries in the result-cache for that object. What I'm trying to
do is only discard the entries in the cache (when the UPDATE takes place) that
are affected by that update. For that I need to know which columns the query is
dependent on (I already know the columns affected by the update).
I'm not trying to replace/re-implement SQLite, and I'm sure it's very
efficient. What I'm doing is simply avoiding calling it when I already have the
answer I need. What I'm trying to do is optimise the decision process for when
I can use those answers I already have. You can see in the above that the
timings are significantly faster for the 3rd pass. The application can do a
*lot* of queries :)
If it's not possible, then fine. I'll just modify the API to allow the clients
to optionally define the columns that a given query is dependent on. If they
provide that information, I can be clever about discarding cache entries. If
they don't I'll just discard everything as I currently do on an UPDATE. I was
just looking to see if there was a way to figure out the column dependencies
via inspection.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
