Re: [sqlite] Speeding up the simplest queries
"Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote: > >> You need FTS3: >> http://www.sqlite.org/cvstrac/wiki?p=FtsUsage >> It comes with the command line version of SQLite 3.6 > > You mean: it won't work using SQLite's module for TCL? I don't think there's going to be any problem (though I have never used TCL myself). FTS is accessed using public SQLite API, the same TCL binding you use for regular queries should work just fine. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On 9/22/08, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote: > > > select exists (select 1 from MyTable where condition) > > > > which will return a boolean result, and stop scanning the table after > > the first match is found. > > > Yes, thanks - that's right: it is partial solution indeed. "Partial" - > because in the case of non-existence it has to scan the table to the end > anyway. > > But still it's better than "count(*)". The bottom line is -- when using conditions, (WHERE constraints), indexes will (should) speed up your queries except when using LIKE constraints. In which case, looping through your result and counting the results will be fast. When not using condition, indexes don't help anyway, and count(*) involves a full table scan. If you don't want to do that, maintain a separate table with the count of rows for every table. > -- > pozdrawiam / regards > > > Zbigniew Baniewski > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote: > You need FTS3: > http://www.sqlite.org/cvstrac/wiki?p=FtsUsage > It comes with the command line version of SQLite 3.6 You mean: it won't work using SQLite's module for TCL? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
You need FTS3:
http://www.sqlite.org/cvstrac/wiki?p=FtsUsage
It comes with the command line version of SQLite 3.6
On Mon, Sep 22, 2008 at 8:43 PM, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote:
>
> > > although not always I want
> > > to fetch all that data. Sometimes I would just to count it.
> >
> > Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But
> > again, if you want to get a count of records, you are unlikely to be
> > able to do any better than a statement using count().
>
> I've got a feeling, you know very good SQLite's internals. How do you
> think:
> is it technically possible to implement much faster searching routine for
> all the LIKE queries?
>
> I'm asking, because I've got no idea presently, whether (or not) the limit
> is just the storage ("flat database file"). So, perhaps supposed different
> one's own procedure has to do about the same, as the built-in, and it'll
> take about the same time? I mean: perhaps different approach to the subject
> is just not possible just because of the limits forced by the storage?
> --
>pozdrawiam / regards
>
>Zbigniew Baniewski
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Seun Osewa
http://www.nairaland.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote:
> > although not always I want
> > to fetch all that data. Sometimes I would just to count it.
>
> Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But
> again, if you want to get a count of records, you are unlikely to be
> able to do any better than a statement using count().
I've got a feeling, you know very good SQLite's internals. How do you think:
is it technically possible to implement much faster searching routine for
all the LIKE queries?
I'm asking, because I've got no idea presently, whether (or not) the limit
is just the storage ("flat database file"). So, perhaps supposed different
one's own procedure has to do about the same, as the built-in, and it'll
take about the same time? I mean: perhaps different approach to the subject
is just not possible just because of the limits forced by the storage?
--
pozdrawiam / regards
Zbigniew Baniewski
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 07:07:33PM -0500, Jay A. Kreibich wrote: > It sounds like you need to take a more general approach to speeding > up your queries. If you've not yet looked at building appropriate > indexes, that seems like a good place to start. Right, proper indexing gives significant "boost" (for "sharp" conditions). I'm afraid, one has just to wait in the case of all the LIKE-s, unfortunately. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote: > select exists (select 1 from MyTable where condition) > > which will return a boolean result, and stop scanning the table after > the first match is found. Yes, thanks - that's right: it is partial solution indeed. "Partial" - because in the case of non-existence it has to scan the table to the end anyway. But still it's better than "count(*)". -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
>> I was using count(*) just to detect the presence of any record >> meeting given condition. Is it possible to make >> it fast _not_ using count(*)? I need just a "boolean result" of 1/0 >> (yes, there is at least one / there aren't any). >> > Add limit 1 to the query, so that it stops after finding the > first match. Actually, IIRC, "limit" only operates on the rows after a full table scan, so doesn't help. What you need instead is: select exists (select 1 from MyTable where condition) which will return a boolean result, and stop scanning the table after the first match is found. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
Zbigniew Baniewski wrote: > On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > > >> count(*) is an odd one... In most database systems it is extremely >> fast, but in SQLite it tends to be rather slow. >> > > I forgot the important thing: usually I was using count(*) just to detect > the presence of any record meeting given condition. Is it possible to make > it fast _not_ using count(*)? I need just a "boolean result" of 1/0 (yes, > there is at least one / there aren't any). > Add limit 1 to the query, so that it stops after finding the first match. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Mon, Sep 22, 2008 at 01:13:30AM +0200, Zbigniew Baniewski scratched on the wall: > On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > > > count(*) is an odd one... In most database systems it is extremely > > fast, but in SQLite it tends to be rather slow. > > [..] > > If you search the archives, you'll find many discussions on the best > > way to create a system table that keeps track of the number of rows > > in each table via triggers. > > The problem is, that it's not only about global number of records Oh, sure... I wasn't very clear about that. What I said about count(*) being slower in SQLite only applies to global table counts, such as "SELECT count(*) FROM ;" where there is no WHERE clause. Most database systems can optimize this type of query just use the internal table structures to return an answer almost instantly. As soon as you add a WHERE clause, then database systems are forced to do some kind of lookup via full table scan or index searches, however. There is no significant performance different in these types of situations. That said, assuming the WHERE clause can use an available index, indexes can be used to speed up a conditional search, including testing for the presence of a row (as described in your other email). > - I would > to have a possibility to quickly count number of records found by > conditional queries like: "select count(*) from table where ". From a performance standpoint, the "count(*)" part of this statement is mostly irrelevant. If you can speed up the WHERE condition, you should speed up any type of count(). > Partially it can be solved by moving the task to the application (fetch, > then count list size), although not always I want to fetch all that data. That's not going to be any faster, although the difference in SQLite vs a traditional network-based client/server system is going to be much less. Regardless, returning values is still going to take more resources and more time than just counting them. > It wasn't a problem, when I was counting several thousands of records - but, > as I can see, it will be inconvenient in the case of the larger table. It sounds like you need to take a more general approach to speeding up your queries. If you've not yet looked at building appropriate indexes, that seems like a good place to start. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
"Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The problem is, that it's not only about global number of records - I > would to have a possibility to quickly count number of records found > by conditional queries like: "select count(*) from table where > ". Partially it can be solved by moving the task to the > application (fetch, then count list size) It's unlikely to be any faster. count(*) does precisely the same thing internally, anyway. Depending on the nature of , you might be able use indexes to speed up the WHERE part. > although not always I want > to fetch all that data. Sometimes I would just to count it. Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But again, if you want to get a count of records, you are unlikely to be able to do any better than a statement using count(). -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > count(*) is an odd one... In most database systems it is extremely > fast, but in SQLite it tends to be rather slow. I forgot the important thing: usually I was using count(*) just to detect the presence of any record meeting given condition. Is it possible to make it fast _not_ using count(*)? I need just a "boolean result" of 1/0 (yes, there is at least one / there aren't any). -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > count(*) is an odd one... In most database systems it is extremely > fast, but in SQLite it tends to be rather slow. > [..] > If you search the archives, you'll find many discussions on the best > way to create a system table that keeps track of the number of rows > in each table via triggers. The problem is, that it's not only about global number of records - I would to have a possibility to quickly count number of records found by conditional queries like: "select count(*) from table where ". Partially it can be solved by moving the task to the application (fetch, then count list size), although not always I want to fetch all that data. Sometimes I would just to count it. It wasn't a problem, when I was counting several thousands of records - but, as I can see, it will be inconvenient in the case of the larger table. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
On Sun, Sep 21, 2008 at 11:32:18PM +0200, Zbigniew Baniewski scratched on the wall: > I've created a test database with almost one million records (about 20 fields > each). I noticed, that the simple query like "select count(*) from table" > takes about 10 seconds (the database file is of about 300 MB size). > > I'm wondering: is it the limit - or is it still possible to reduce the > response time? I mean the simplest queries here, mostly: "select * from", > "...where something=''", "...where something like '%that%'. Yes, I know: > indexing. Indexing will help with "something=" (in some cases) but not with "like '%that%'". Indexes aren't much use for something with a wildcard prefix. If you're doing a large amount of text searching and manipulating, you might look at the FTS modules to see if they fit your needs. Additionally, if you're on a desktop system with enough resources, you'll see a significant improvement by upping the cache size. In fact, if you can afford it, you can simply make the page cache large enough to hold the entire database. For standard 1K pages SQLite requires about 1.5K of RAM. So you'd need about 450MB to pull something the size of your test DB into RAM. That's not out of the question on most modern desktops, but usually isn't too practical for something like a phone or PDA. > Unfortunately, indexing won't have any effect on "count(*)". But > of course, indexing tips are welcome too (for SELECT ...). count(*) is an odd one... In most database systems it is extremely fast, but in SQLite it tends to be rather slow. Unlike most database systems, all SQLite built-in functions are implemented using the public API. As such, they don't have access to any specialized internal information about the SQLite data structures. The end result of all this is that count(*) actually does a full table scan, counting each individual row. Personally, I like the "honesty" of a system using the public API for internal works. It somehow justifies the completeness of the API, as it forces the developers to, as they say, "eat their own dogfood." Of course, I rarely find myself using something like count(*) in production code, so in this specific case I have the luxury of appreciating the motivations of the design and can disregard the practical performance issues. I realize not everyone is in that situation. If you search the archives, you'll find many discussions on the best way to create a system table that keeps track of the number of rows in each table via triggers. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Speeding up the simplest queries
I've created a test database with almost one million records (about 20 fields each). I noticed, that the simple query like "select count(*) from table" takes about 10 seconds (the database file is of about 300 MB size). I'm wondering: is it the limit - or is it still possible to reduce the response time? I mean the simplest queries here, mostly: "select * from", "...where something=''", "...where something like '%that%'. Yes, I know: indexing. Unfortunately, indexing won't have any effect on "count(*)". But of course, indexing tips are welcome too (for SELECT ...). Found a page http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html - but perhaps someone gathered some more tips? Maybe latest versions of SQLite (the page is from 2003) are giving some more options for tuning? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
