Nathan Biggs wrote: > I was hoping that someone could help me with optimizing this query. > Basically I need to return totals from an large database (> 1million > records in the table). There needs to be two different totals one by > "a" and one by "b" where "a" and "b" could have up to 100 rows each. So > totals for up to 200 distinct total values. > > The problem gets into the multiple where fields in the where clause. > There could be up to 9 different where parameters. > > Instead of creating multiple queries with indexes, and creating a ton of > indexes, I tried this approach. > > Basically I create a temp table to hold my totals. And using a trigger, > cause the totals to be updated. > > This work fantastic by the way! I was just seeing if there is a better > way to speed up the trigger. > With one statement in the trigger the query runs in about 30 seconds > with 1.3million records. > With two statements, the time increases to 50 seconds for 1.3 million > records. > > I really need to have 4 updates in the trigger and was hoping to keep > the time < 1 minute. > > Thanks for your help. > > Query > -------------------------------------- > replace into totals > select 0, 0, a, b, c > from table1 > > Trigger > ----------------------------------------- > CREATE TRIGGER sum_totals after insert on totals > BEGIN > update totals set cnt = cnt + 1, a = a + NEW.a where id = NEW.b; > update totals set cnt = cnt + 1, a = a + NEW.a where id = NEW.c; > END > > Totals table > --------------------------------------------- > create temp table totals(id integer primary key, cnt integer, a float, b > integer, c integer); > Begin Transaction; > insert into totals values (0, 0, 0.00, 0, 0,); > insert into totals values (1, 0, 0.00, 0, 0,); > insert into totals values (2, 0, 0.00, 0, 0); > etc... total of 500 rows > Commit; >
Nathan, Perhaps it's just me, but I didn't follow your description very well at all. :-) In the first paragraph you say you need two different totals, but at the end you create a table of totals and insert 500 rows. You have defined an insert trigger on this totals table which may updates two rows of the table (depending upon the values of the b and c fields inserted) after each insert into the table. It looks to me like you are trying to initialize the totals table with 500 rows of zero data, each with their own id number. Since each of these initial inserts has b and c set to zero, each one causes the first row (the one with id = 0) to be updated twice. Each update increments the cnt field of this row, so that after 500 rows are inserted, the cnt field in the first row will be 1000. Each update adds 0.0 to the initial value of 0.0 in field a of the first row, so it ends up with a final value of 0.0 after the 500 inserts. It looks to me you are then trying to execute the query to replace rows in the totals table. You select all 1M rows from table1. For each of those rows you replace the first row of the totals table (the one with id = 0). For each of these 1 M replacements your trigger fires and updates 0, 1, or 2 rows in the totals table (depending upon the values of b and c in each row of table1). If every row of table1 has values of b and c between 0 and 500, then each replacement of the first row of the totals table will update two rows in the totals table. Each of these updates will increment the cnt field in one of the rows, and add the value of a from the row in table1 to the running total value of a in the updated row of the totals table. When you are done you will have 500 rows in the totals table, each with a cnt of the number of times the row id matched field b or field c in table1, and the sum of all the field a values from table1 where the row's id matched field b or field c. Note the values in the first row are not valid because that row gets overwritten on each replacement. If this is correct, it seems to me there should be a much easier way to do this. Can you clarify? Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
