[sqlite] Full Table Read
Is there a faster way to read an entire table other then: select * from table Not that is is slow, just curious. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
On Fri, 13 Feb 2009 16:33:38 -0500, Nathan Biggs wrote in General Discussion of SQLite Database : >Is there a faster way to read an entire table other then: > >select * from table; It is the fastest SQL way. >Not that is is slow, just curious. The speed very much depends on what you do with the output. If it scrolls over a terminal, the terminal will be the bottleneck. It should be very fast if you redirect the output to /dev/null. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
Hello! В сообщении от Saturday 14 February 2009 00:33:38 Nathan Biggs написал(а): > Is there a faster way to read an entire table other then: > > select * from table > > Not that is is slow, just curious. On linux you can do dd if=database.db of=/dev/null bs=1M and after perform "select ..." This trick does put your db to OS file cache and all read operations will be extremely fast. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
We are actually using Windows XP for an OS. (I know, lots of limitations, but that is what we have to use). Alexey Pechnikov wrote: > Hello! > > В сообщении от Saturday 14 February 2009 00:33:38 Nathan Biggs написал(а): > >> Is there a faster way to read an entire table other then: >> >> select * from table >> >> Not that is is slow, just curious. >> > > On linux you can do > dd if=database.db of=/dev/null bs=1M > and after perform "select ..." > > This trick does put your db to OS file cache and all read operations will be > extremely fast. > > Best regards, Alexey. > ___ > 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] Full Table Read
> On linux you can do > dd if=database.db of=/dev/null bs=1M > and after perform "select ..." = From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs Subject: Re: [sqlite] Full Table Read We are actually using Windows XP for an OS. (I know, lots of limitations, but that is what we have to use). = I think the windows equivalent would be: copy /b database.db nul In either case, we're assuming your database fits into available ram-cached virtual space. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
Is that just creating an in-memory database then? I'm not familiar with a copy to a null location. Griggs, Donald wrote: > > On linux you can do > >> dd if=database.db of=/dev/null bs=1M >> and after perform "select ..." >> > > = > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs > Subject: Re: [sqlite] Full Table Read > > We are actually using Windows XP for an OS. (I know, lots of > limitations, but that is what we have to use). > > = > > I think the windows equivalent would be: >copy /b database.db nul > > In either case, we're assuming your database fits into available > ram-cached virtual space. > ___ > 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] Full Table Read
Hello! В сообщении от Monday 16 February 2009 17:18:04 Nathan Biggs написал(а): > Is that just creating an in-memory database then? I'm not familiar with > a copy to a null location. Writes to in-memory database is not saved to disk. If you only want to increase read speed when ram-cache of your database file can help to you. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
Griggs, Donald wrote: > > On linux you can do > >> dd if=database.db of=/dev/null bs=1M >> and after perform "select ..." >> > > = > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs > Subject: Re: [sqlite] Full Table Read > > We are actually using Windows XP for an OS. (I know, lots of > limitations, but that is what we have to use). > > = > > I think the windows equivalent would be: >copy /b database.db nul > > In either case, we're assuming your database fits into available > ram-cached virtual space. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > = Biggs wrote: Is that just creating an in-memory database then? I'm not familiar with a copy to a null location. = Nothing quite that fancy. It's just attempting to get most or all of your database into the read-cache of the operating system. (I probably was not accurate earlier when I wrote "virtual space" since I don't know how the read-cache is implemented.) Your performance, after a successful loading into cache, should be about that of a ram-based database -- without the data integrity and synchronization problems a ramdisk-based database can cause. You didn't mention any "order by" clause on your "select *". If you *do* require a an ordering of the output then an index, of course, could possibly make a large difference. Instead of the copy to a nul device, you could also try running a vacuum command -- since it can "defragment" a table who's portions are spread throughout the file. All this assumes that the time to perform the copy or vacuum is tolerable in your application. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
> > > Griggs, Donald wrote: > >> > On linux you can do >> >> >>> dd if=database.db of=/dev/null bs=1M >>> and after perform "select ..." >>> >>> >> = >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs >> Subject: Re: [sqlite] Full Table Read >> >> We are actually using Windows XP for an OS. (I know, lots of >> limitations, but that is what we have to use). >> >> = >> >> I think the windows equivalent would be: >>copy /b database.db nul >> >> In either case, we're assuming your database fits into available >> ram-cached virtual space. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > = > Biggs wrote: > > Is that just creating an in-memory database then? I'm not familiar with > a copy to a null location. > = > > Nothing quite that fancy. It's just attempting to get most or all of > your database into the read-cache of the operating system. (I probably > was not accurate earlier when I wrote "virtual space" since I don't know > how the read-cache is implemented.) > > Your performance, after a successful loading into cache, should be about > that of a ram-based database -- without the data integrity and > synchronization problems a ramdisk-based database can cause. > > You didn't mention any "order by" clause on your "select *". If you > *do* require a an ordering of the output then an index, of course, could > possibly make a large difference. > > Instead of the copy to a nul device, you could also try running a vacuum > command -- since it can "defragment" a table who's portions are spread > throughout the file. > > All this assumes that the time to perform the copy or vacuum is > tolerable in your application. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > Thanks for the detailed information. The real reason I was asking is because I created a custom function in C++ with pre-allocated memory to hold query results which are just arrays that hold counts. Anyway, I noticed that the function ran faster when I did not use a where statement in the sql even though it was returning a lot more data. My guess is this is due to how sqlite needs to create temporary buffers to get the specific data you requested. If you don't request specific data, maybe it just reads from the table itself and bypasses the additional memory allocations. I'm not sure that my database will fit into the cache, its about 100MB in size. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
