On 11/02/2011, at 9:40 AM, Yuzem wrote: > Yes, in my application I have in the sidebar all those sections (movies, > years, tags, keywords, actors, directors, writers, etc...) and I count each > one, how many movies, how many years, etc... > It isn't very slow if I update only one item but the problem is when I have > to update the entire list, this uses a lot of cpu and takes some time and I > have to do it every time a movie is added. > http://3.bp.blogspot.com/_EbNPUgfUDXs/TIpif4U1III/AAAAAAAAA2A/YFSGOAdpOGA/s1600/nested-brosers.jpg > Here there is an old screenshot from the sidebar.
In that case, you should cache the counts in a separate table or two. That has a negligible overhead when you add a movie (which is infrequent), and basically no overhead when viewing (which is frequent). Do that with triggers (which are a good thing in this context), to change the relevant count when an insert, delete or update occurs in the "Movie People" table. Something like: begin immediate ; create table "Capacity Statistics" ( ID integer primary key unique references "Capacity" (ID) on delete cascade , Count integer not null ) ; insert into "Capacity Statistics" (ID, Count) select Capacity_ID, count(*) from "Movie People" group by Capacity_ID having Count > 0 ; create trigger "Movie People insert" on "Movie People" after insert begin insert or replace into "Capacity Statistics" (ID, Count) select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity Statistics" where ID = new.Capacity_ID) ; end ; create trigger "Movie People delete" on "Movie People" after insert begin insert or replace into "Capacity Statistics" (ID, Count) select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity Statistics" where ID = old.Capacity_ID) ; end ; create trigger "Movie People update" on "Movie People" after update of Capacity_ID begin insert or replace into "Capacity Statistics" (ID, Count) select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity Statistics" where ID = new.Capacity_ID) ; insert or replace into "Capacity Statistics" (ID, Count) select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity Statistics" where ID = old.Capacity_ID) ; end ; commit ; By contrast, I suggest it would be a bad idea to denormalize and partition your data (ie separate directors and writers tables) just for the sake of tracking the count. The solution above maintains a normalized database, gives you the flexibility to add or remove Capacities in data rather than having to change the schema, and better reflects the real data model. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
