Re: [sqlite] Looong sql queries (>9 seconds)
Wow! Thank you, all! Just adding the index dropped the query time down into the 3 second range. These two versions had a similiar effects: CREATE INDEX idx1 ON settings(rate, year, month); CREATE INDEX idx1 ON settings(rate); I'll experiment with this whole new world of INDEX and maybe I can get it down even further. Thank you! On Thu, Sep 18, 2008 at 3:31 PM, Jeffrey Becker <[EMAIL PROTECTED]>wrote: > Actually I think you can drop the index on just rate. > > On Thu, Sep 18, 2008 at 3:12 PM, John Stanton <[EMAIL PROTECTED]> wrote: > > I would get rid of your separate columns for day, month and year and use > > the Sqlite date format and use an index on it. > > > > chris wrote: > >> I'm at a loss and need some guidance. My queries are taking way longer > than I can use but I'm not sure what steps to take next. > >> > >> I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform > with 256M ram. I'm using Python to access the database. I've created a > very simple database file with two tables: > >> > >> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY > AUTOINCREMENT, year INTEGER, month INTEGER, day INTEGER, hour INTEGER, > minutes INTEGER, seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, > rate TEXT ) > >> > >> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY > AUTOINCREMENT, year INTEGER, month INTEGER, day INTEGER, hour INTEGER, > minutes INTEGER, seconds INTEGER, point TEXT ) > >> > >> The SETTINGS table has about 95,000 entries. > >> > >> This single query takes between 9 and 12 seconds to run: > >> SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate > FROM settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY > rate > >> > >> Is there something I can do to get that time down substantially? > >> > >> Thank you for any suggestions. > >> > >> > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looong sql queries (>9 seconds)
Actually I think you can drop the index on just rate. On Thu, Sep 18, 2008 at 3:12 PM, John Stanton <[EMAIL PROTECTED]> wrote: > I would get rid of your separate columns for day, month and year and use > the Sqlite date format and use an index on it. > > chris wrote: >> I'm at a loss and need some guidance. My queries are taking way longer than >> I can use but I'm not sure what steps to take next. >> >> I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with >> 256M ram. I'm using Python to access the database. I've created a very >> simple database file with two tables: >> >> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, >> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, >> seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT ) >> >> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, >> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, >> seconds INTEGER, point TEXT ) >> >> The SETTINGS table has about 95,000 entries. >> >> This single query takes between 9 and 12 seconds to run: >> SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM >> settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate >> >> Is there something I can do to get that time down substantially? >> >> Thank you for any suggestions. >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looong sql queries (>9 seconds)
I would get rid of your separate columns for day, month and year and use the Sqlite date format and use an index on it. chris wrote: > I'm at a loss and need some guidance. My queries are taking way longer than > I can use but I'm not sure what steps to take next. > > I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with > 256M ram. I'm using Python to access the database. I've created a very > simple database file with two tables: > > CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, > year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, > seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT ) > > CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, > year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, > seconds INTEGER, point TEXT ) > > The SETTINGS table has about 95,000 entries. > > This single query takes between 9 and 12 seconds to run: > SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM > settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate > > Is there something I can do to get that time down substantially? > > Thank you for any suggestions. > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looong sql queries (>9 seconds)
On Thu, Sep 18, 2008 at 11:58 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Sep 18, 2008, at 12:46 PM, chris wrote: > > I'm at a loss and need some guidance. My queries are taking way longer >> than I can use but I'm not sure what steps to take next. >> >> >> Try this and see if it helps: >> CREATE INDEX idx1 ON settings(rate, year, month); >> > Wow! Thanks, Jeffrey and Richard! Just adding the index dropped the query time down into the 3 second range. These two versions had a similiar effects: CREATE INDEX idx1 ON settings(rate, year, month); CREATE INDEX idx1 ON settings(rate); I'll experiment with this whole new world of INDEX and maybe I can get it down even further. Thank you! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looong sql queries (>9 seconds)
Try creating an index on settings(rate) On Thu, Sep 18, 2008 at 12:46 PM, chris <[EMAIL PROTECTED]> wrote: > I'm at a loss and need some guidance. My queries are taking way longer than > I can use but I'm not sure what steps to take next. > > I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with > 256M ram. I'm using Python to access the database. I've created a very > simple database file with two tables: > > CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, > year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, > seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT ) > > CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, > year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, > seconds INTEGER, point TEXT ) > > The SETTINGS table has about 95,000 entries. > > This single query takes between 9 and 12 seconds to run: > SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM > settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate > > Is there something I can do to get that time down substantially? > > Thank you for any suggestions. > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looong sql queries (>9 seconds)
On Sep 18, 2008, at 12:46 PM, chris wrote: > I'm at a loss and need some guidance. My queries are taking way > longer than I can use but I'm not sure what steps to take next. > > I'm using SQLite 3.6.2 on a small reasonable powerful embedded > platform with 256M ram. I'm using Python to access the database. > I've created a very simple database file with two tables: > > CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY > AUTOINCREMENT, year INTEGER, month INTEGER, day INTEGER, hour > INTEGER, minutes INTEGER, seconds INTEGER, curtemp TEXT, relhum > TEXT, watts TEXT, rate TEXT ) > > CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY > AUTOINCREMENT, year INTEGER, month INTEGER, day INTEGER, hour > INTEGER, minutes INTEGER, seconds INTEGER, point TEXT ) > > The SETTINGS table has about 95,000 entries. > > This single query takes between 9 and 12 seconds to run: > SELECT rate, day, month, rate * SUM(watts) / 12000 AS > TotalBillAtRate FROM settings WHERE (day <= 18) AND (month = 9) AND > (year = 2008) GROUP BY rate > > Is there something I can do to get that time down substantially? Try this and see if it helps: CREATE INDEX idx1 ON settings(rate, year, month); D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
