[sqlite] looking for suggestions to speed up SQL

[Dennis Cote]

Hi All,

I have a situation where populating data in a temp table takes a long time,
about 8 minutes, and I am wondering if anyone can offer any suggestions for
ways to speed it up.

I have the permanent table and associated index, shown below, which holds
tree structured component data. Each node is related to its parent by the
container_id field.

create table Component (
    component_id            integer primary key,
    component_kind_id       integer not null references Component_Kind,
    component_number        integer not null,   -- number of this component
                                                --  within its container
    container_id            integer references Component, -- component id of
container
    component_path          varchar,            -- complete path to
component
                                                --  ids seperated by slashes
    component_function_id   integer default 0 references Component_Function,
    unique (container_id, component_kind_id, component_number)
);

--index used to speed container lookups
create index Component_Container_Index on Component (container_id);

Some parts of my application need additional data. Specifically, the number
of sub components and peer components associated with each component. Since
this data is not needed by the main application, and can be recreated from
the component tree table, it is not stored in the component table to
minimize the size of the database.

Where this additional data is needed I create a temporary table, populate it
with the count data, and use a view to join the two tables. The temporary
table is defined as;

create temp table Component_Count (
    component_id    integer primary key references Component,
    sub_count       integer,  --count of subnodes ie nodes where parent = id
    peer_count      integer   --count of peers ie same parent and same kind
);

and I populate the temp table with the following SQL statement;

insert into Component_Count
    select sub.id, sub_count, peer_count
    from
        (select pid as id, count(nid) as sub_count
        from
            (select p.component_id as pid, n.component_id as nid
            from Component p left outer join Component n on p.component_id =
n.container_id)
        group by pid) as sub
    join
        (select nid as id, count(pid) as peer_count
        from
            (select n.component_id as nid, p.component_id as pid
            from Component n left outer join Component p
                on (n.component_id != p.component_id
                and n.container_id = p.container_id
                and n.component_kind_id = p.component_kind_id))
        group by id) as peer
    using (id);

This statement produces the correct result, but when the component table is
large, about 30,000 records, it takes quite a long time to execute, about 8
minutes.

So can anyone suggest another way to populate the table that might be
faster?

Thanks.