Re: [sqlite] Slow SELECT Statements in Large Database file

I was actually just reading through that. I may switch over to that kind of implementation. Thanks for the tip. -- Jonathan R. Haws Electrical Engineer Space Dynamics Laboratory (435) 797-4629 jh...@sdl.usu.edu From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Slow SELECT Statements in Large Database file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/29/2010 10:48 AM, Jonathan Haws wrote: > We have a whole ton of points (3600^2) and a single select returns a single > point - though I may modify the select to return the four corners of the box > corresponding to the point that was entered.

Re: [sqlite] Slow SELECT Statements in Large Database file

I agree, and for that reason we are keeping the DB on an SSD. Initial benchmarks show that we should be able to get the performance we need - I am just not getting it with my new database. I am sure I have something setup wrong, but maybe it is just the fact that the indexing has not taken

Re: [sqlite] Sqlite with VS 2008

Linda, I'm using the individual files to compile a static lib. Just create a new project as a static lib. Unzip the source files into the project folder and then add all the h and c files to the project. You'll have to delete some but, you can figure that out when you compile. SQLITE_CORE;

Re: [sqlite] Slow SELECT Statements in Large Database file

We have a whole ton of points (3600^2) and a single select returns a single point - though I may modify the select to return the four corners of the box corresponding to the point that was entered. We had a hash table implementation that did not work very well. The problem with it was that we

Re: [sqlite] Slow SELECT Statements in Large Database file

Yeah, I am letting it run and will check to see how things work once it is finished. I expect things to speed up quite a bit. -- Jonathan R. Haws Electrical Engineer Space Dynamics Laboratory (435) 797-4629 jh...@sdl.usu.edu From:

Re: [sqlite] Sqlite with VS 2008

I would suggest using the pre-compiled binary sqlite3.dll and just call into the DLL. You can easily create an import library to link with in your application by using the LIB /DEF command: lib /def:sqlite3.def That will create sqlite3.lib and sqlite3.exp. You can link against the

Re: [sqlite] Multilple tables with the same schema and prepare query performance

No I haven't yet measured it.. I was only in the process of designing the database layout... Given that my queries are very simple, it may be fine to do the prepare_query every time.. I will do some perf testing and reply back. On Fri, Oct 29, 2010 at 9:40 AM, Pavel Ivanov

Re: [sqlite] Multilple tables with the same schema and prepare query performance

> order of 10 to 100 of these tables. When doing operations on these tables, I > want to avoid having to do a prepare_query every time for performance > reasons. Did you measure your performance and find that prepare_query is a bottleneck? > Since the tables have exactly the same schema, in

Re: [sqlite] Slow SELECT Statements in Large Database file

On Fri, Oct 29, 2010 at 12:07 PM, Jonathan Haws wrote: > All, > > I am having some problems with a new database that I am trying to setup. > > This database is a large file (about 8.7 GB without indexing).  The problem > I am having is that SELECT statements are

Re: [sqlite] Slow SELECT Statements in Large Database file

Jonathan - 500 queries per second is 2ms per query. You'll have a hard time getting that kind of speed for random queries with any rotating media. Your database needs to be in memory - all of it, not just indexes - or on a flash drive. If your queries are not random but are somehow related, eg,

Re: [sqlite] Slow SELECT Statements in Large Database file

You should see a noticeable increase in speed with the index you show. You'll want to make sure your data has been inserted in dted_lat order or dted_lon order. That way all the matching records will be colocated on disk and in memory. Otherwise it's going to do a lot of paging to get your

Re: [sqlite] Multilple tables with the same schema and prepare query performance

I know the order of rows is possible and efficient if your query plan is using the special ROWID: SELECT ROWID, col1, col2 from Table ORDER BY ROWID. the ORDER BY is a no-op in this case (see http://www.sqlite.org/queryplanner.html) Say you iterate 10 rows, and later want to continue where you

[sqlite] Pages with all zeros

Hi List, I'm looking through some SQLite databases that are used by Google Chrome on Mac OSX 10.6.4 and find some cases where entire pages are filled with 0x00. I checked and there are no corresponding journal files. I also verified that Google Chrome was not running at the time. Would

Re: [sqlite] Slow SELECT Statements in Large Database file

On 29 Oct 2010, at 5:07pm, Jonathan Haws wrote: > SELECT dted_alt FROM Dted WHERE (dted_lat=%d AND dted_lon=%d); > > The numbers fed to dted_lat and dted_lon are typically on the order of > 37 and -111. > > What can we do to speed up our SELECT statements? Minutes is > unacceptable

Re: [sqlite] Multilple tables with the same schema and prepare query performance

On 29 Oct 2010, at 4:52pm, john Papier wrote: > The thing is, I need to keep a cursor to where in the > table I was last searching, so I can continue the search from where I left > off, which is why using multiple tables was preferable; i.e., i can track > the row_id, and then resume the search

[sqlite] Slow SELECT Statements in Large Database file

All, I am having some problems with a new database that I am trying to setup. This database is a large file (about 8.7 GB without indexing). The problem I am having is that SELECT statements are extremely slow. The goal is to get the database file up and running for an embedded application (we

[sqlite] WAL mode enhancement suggestion

About a week ago, I posted this inside a response on another thread. It may have gotten lost in the mix. Curious what folks think. Considering things like bulk deletes (and updates) potentially really growing a WAL file to be quite large along with having a system that is constantly running and

[sqlite] Multilple tables with the same schema and prepare query performance

Hi, I need to create multiple tables all having the same schema. The number/names of the tables will by dynamic. There would be somewhere in the order of 10 to 100 of these tables. When doing operations on these tables, I want to avoid having to do a prepare_query every time for performance

Re: [sqlite] Fw: sqlite3 support

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/28/2010 11:47 PM, lizhe wrote: > I am writing to enquire about a bug we found. http://www.chiark.greenend.org.uk/~sgtatham/bugs-cn.html > I would like get support for SQLite ? You will need to do what the page above says in order for

Re: [sqlite] Sqlite with VS 2008

Thanks for pointing out this project...I may be able to use this in my current effort. Download the amalgamation http://www.sqlite.org/sqlite-amalgamation-3_7_3.zip Download the windows binaries http://www.sqlite.org/sqlitedll-3_7_3.zip Extract the sqlite3.h from the amalgation and the

Re: [sqlite] Sqlite with VS 2008

> I am trying to use sqlite in the debea database library. > > http://debea.net/trac/wiki/CompileSvn > > It simply says compile sqlite3. > > I am however a VS2008 user. I cannot find a way to compile it so I can > reference the include and library directories. Can you help? > > Linda Rawson

[sqlite] Fw: sqlite3 support

- Original Message - From: lizhe To: d...@hwaci.com Cc: us...@sqlite.org Sent: Friday, October 29, 2010 2:41 PM Subject: sqlite3 support 您好: 我们使用的SQLITE3 数据库版本为3.6.23.1版本,在红帽编译器下运行,运行的硬件系统为LINUX(2.6版本),现在出现了一个问题,长时间运行后会出现查询数据库返回"The database disk image is malformed",

[sqlite] Sqlite with VS 2008

I am trying to use sqlite in the debea database library. http://debea.net/trac/wiki/CompileSvn It simply says compile sqlite3. I am however a VS2008 user. I cannot find a way to compile it so I can reference the include and library directories. Can you help? Linda Rawson Sensory Technology