Re: [sqlite] How to speed up database open

> > On Thu, Dec 11, 2014 at 10:58 AM, Paul wrote: > > > > > > > > I have yet to try and test if dropping stat tables worth the effort. > > > > > > > Most of the work is involved in loading sqlite_stat4. On the other hand, > > most of the benefit comes from sqlite_stat1. So

Re: [sqlite] How to speed up database open

> > On 2014/12/11 17:58, Paul wrote: > > > >> On 2014/12/11 13:51, Paul wrote: > >> I have yet to try and test if dropping stat tables worth the effort. Some > >> databases in fact can grow pretty big, up to few > >> hundred of megabytes// > > In that case maybe keep the Stat1 tables and

Re: [sqlite] How to speed up database open

On 2014/12/11 17:58, Paul wrote: On 2014/12/11 13:51, Paul wrote: I have yet to try and test if dropping stat tables worth the effort. Some databases in fact can grow pretty big, up to few hundred of megabytes// In that case maybe keep the Stat1 tables and there is also the option of

Re: [sqlite] How to speed up database open

> On Thu, Dec 11, 2014 at 10:58 AM, Paul wrote: > > > > > I have yet to try and test if dropping stat tables worth the effort. > > > > Most of the work is involved in loading sqlite_stat4. On the other hand, > most of the benefit comes from sqlite_stat1. So consider compiling

Re: [sqlite] How to speed up database open

On Thu, Dec 11, 2014 at 10:58 AM, Paul wrote: > > I have yet to try and test if dropping stat tables worth the effort. > Most of the work is involved in loading sqlite_stat4. On the other hand, most of the benefit comes from sqlite_stat1. So consider compiling without

Re: [sqlite] How to speed up database open

Hi Ryan, thanks for reply. > > On 2014/12/11 13:51, Paul wrote: > > In my specific case I need to open database as fast as possible. > > Usual working cycle: open -> select small data set -> close. > > It is irrelevant how much time it takes to open database when > > data is being added or

Re: [sqlite] How to speed up database open

On 2014/12/11 13:51, Paul wrote: In my specific case I need to open database as fast as possible. Usual working cycle: open -> select small data set -> close. It is irrelevant how much time it takes to open database when data is being added or updated, since it happens not too often. /Snipped

Re: [sqlite] How to speed up database open

PART 2 INSERT INTO sqlite_stat4 VALUES('LLL','LLL_idx','676 1 1','0 330 330','0 327 330',X'0408040253be558403a9'); INSERT INTO sqlite_stat4 VALUES('LLL','LLL_idx','676 1 1','0 661 661','0 655 661',X'04080402547bf6900b13'); INSERT INTO sqlite_stat4 VALUES('LLL','LLL_idx','1093 5 1','676 720

Re: [sqlite] How to speed up database open

Hello, Richard, thanks for quick reply. Unfortunately, no, there is no way. On our servers we have big number of entities that represent client data. Data for different clients can be read at any given point of time by clients and by a bunch of daemons responsible for different maintenance

Re: [sqlite] How to speed up database open

Hello, Simon. > > On 11 Dec 2014, at 11:51am, Paul wrote: > > > I understand, that having them is a must for a decent performance. > > In my specific case I have millions of individual database files. > > This is one, among other reasons that I can't keep them open all the

Re: [sqlite] How to speed up database open

On Thu, Dec 11, 2014 at 6:51 AM, Paul wrote: > > Hello. > > In my specific case I need to open database as fast as possible. > Usual working cycle: open -> select small data set -> close. > It is irrelevant how much time it takes to open database when > data is being added or

Re: [sqlite] How to speed up database open

On 11 Dec 2014, at 11:51am, Paul wrote: > I understand, that having them is a must for a decent performance. > In my specific case I have millions of individual database files. > This is one, among other reasons that I can't keep them open all the time. > Just too many of

[sqlite] How to speed up database open

Hello. In my specific case I need to open database as fast as possible. Usual working cycle: open -> select small data set -> close. It is irrelevant how much time it takes to open database when data is being added or updated, since it happens not too often. But for selects it's a different

Re: [sqlite] How to speed up a bulk import

---Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Trahair Sent: Monday, April 07, 2014 2:33 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to speed up a bulk import Hi I am using SQLite running under VB.net in a import ro

Re: [sqlite] How to speed up a bulk import

On 2014/04/07 20:33, J Trahair wrote: Hi I am using SQLite running under VB.net in a import routine of a series of csv files, but it seems to be slow, and I don't mean microseconds, I mean minutes. A typical INSERT might be: INSERT INTO AllSales (Source, MachineName, Location, UserPIN,

Re: [sqlite] How to speed up a bulk import

On 7 Apr 2014, at 7:33pm, J Trahair wrote: > Would having a field index or key help? Adding more indexes and keys make searches faster, at the expense of making the original INSERT slower. As a test, instead of executing the INSERT commands, write the

Re: [sqlite] How to speed up a bulk import

On Apr 7, 2014, at 8:33 PM, J Trahair wrote: > Any suggestions welcome. Thank you. One word: transaction. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] How to speed up a bulk import

Hi I am using SQLite running under VB.net in a import routine of a series of csv files, but it seems to be slow, and I don't mean microseconds, I mean minutes. A typical INSERT might be: INSERT INTO AllSales (Source, MachineName, Location, UserPIN, TariffName, CustomerID, DateOfSale,

Re: [sqlite] how to speed up this ?

> Select >H1.ID > from >HASH1 H1 > where >x1_y1 BETWEEN min11 AND max11 AND >x1_y2 BETWEEN min12 AND max12 AND >x1_y3 BETWEEN min13 AND max13 AND >x1_y4 BETWEEN min14 AND max14 AND >x1_y5 BETWEEN min15 AND max15; > > no it's not work at all !! without an rtree index

Re: [sqlite] how to speed up this ?

i do it.. but it's change nothing :( On 12/24/2010 3:47 PM, Simon Slavin wrote: > On 24 Dec 2010, at 8:17am, Vander Clock Stephane wrote: > >> I have a key like this >> >> 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i >> call node >> Node are integer comprise between 0

Re: [sqlite] how to speed up this ?

On 24 Dec 2010, at 12:47pm, Simon Slavin wrote: > Precalculate five sets of minimum and maximum bounds: > > min11 = max((<#randomnumber> % 255)-10,0) > max11 = min((<#randomnumber> % 255)+10,255) > > Then you can just seize the ten values you need from the table and use them > to make up

Re: [sqlite] how to speed up this ?

On 24 Dec 2010, at 8:17am, Vander Clock Stephane wrote: > I have a key like this > > 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i > call node > Node are integer comprise between 0 and 255 (bytes) You mean from 000 to 254. 255 breaks your system because you are using

Re: [sqlite] how to speed up this ?

can you gave me the name of a good SSD you advise me to buy ? i decide to make a try ! Thanks again stéphane On 12/24/2010 12:24 AM, John Drescher wrote: > On Thu, Dec 23, 2010 at 4:06 PM, Vander Clock Stephane > wrote: >> that very very much expensive :( how much you

Re: [sqlite] how to speed up this ?

> Can you describe what you're trying to do with that command ? of course ! I have a key like this 123-098-230-120-111 where (123), (098), (230), (120), (111) are what i call node Node are integer comprise between 0 and 255 (bytes) and i need to found "similare" key. A similar key is a key

Re: [sqlite] how to speed up this ?

hmmm, how spatial index can help me better than sqlite Rtree index ? i just need to do Select H1.ID from HASH1 H1 where x1_y1_min>= x and x1_y1_max<= y and x1_y2_min>= z and x1_y2_max<= w and x1_y3_min>= a and x1_y3_max<= b and x1_y4_min>= c and

Re: [sqlite] how to speed up this ?

On 23 Dec 2010, at 8:19pm, Vander Clock Stephane wrote: > in my test all the random are different ... Can you describe what you're trying to do with that command ? Or can anyone else explain to me what Stephane is trying to do ? I don't see the point of a construction like this Select

Re: [sqlite] how to speed up this ?

I'd be surprised if you run out of memory, updating a field, even with a 50,000,000 row table. Also, if you are working with real geospatial data such as Imagery or vector data, there are many applications that may be suited for these calculations. Spatialite is a Sqlite extension that has

Re: [sqlite] how to speed up this ?

On Thu, Dec 23, 2010 at 4:06 PM, Vander Clock Stephane wrote: > that very very much expensive :( how much you thing ? > $500 to $600 US. John ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] how to speed up this ?

that very very much expensive :( how much you thing ? On 12/23/2010 11:55 PM, John Drescher wrote: >> i m affraid so ... but what it's will be with 50 000 000 rows ? i don't >> have 100 gigabytes of memory :( > I would get a 256GB SSD. > > John > ___

Re: [sqlite] how to speed up this ?

i don't know but i quite sure not, because the cost to update all the row in the table Hash will be much much (much) more expensive ... and also this solution it's absolutely not multi thread :( On 12/23/2010 11:46 PM, stormtrooper wrote: > would it run faster if you add two columns to the Hast

Re: [sqlite] how to speed up this ?

> i m affraid so ... but what it's will be with 50 000 000 rows ? i don't > have 100 gigabytes of memory :( I would get a 256GB SSD. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to speed up this ?

would it run faster if you add two columns to the Hast table - randmax and randmin update Hash set randmax = max((<#randomnumber> % 255)-10,0) update Hash set randmin = min((<#randomnumber> % 255)+10,255) CREATE INDEX HASH_RMIN_IDX ON HASH (RANDMIN); CREATE INDEX HASH_RMAX_IDX ON HASH

Re: [sqlite] how to speed up this ?

> Right. So you have a database with 2 000 000 rows that is 1.8GB > So your first 1 000 000 rows takes up about 1GB. > And your test case with just 1 000 000 rows in runs really fast. > > So what is happening is that most of the first 1 000 000 rows fits in memory. > Once the database gets

Re: [sqlite] how to speed up this ?

On 23 Dec 2010, at 7:56pm, Vander Clock Stephane wrote: > Windows 2008 R2 with 8GB of memory. > > but actually i run the test on a beta server with only 1Gb of memory and > win2003 .. > > the database si with 2 000 000 rows is 1.8 GO Right. So you have a database with 2 000 000 rows that is

Re: [sqlite] how to speed up this ?

Windows 2008 R2 with 8GB of memory. but actually i run the test on a beta server with only 1Gb of memory and win2003 .. the database si with 2 000 000 rows is 1.8 GO thanks by advance ! stéphane On 12/23/2010 10:52 PM, Simon Slavin wrote: > On 23 Dec 2010, at 7:36pm, Vander Clock Stephane

Re: [sqlite] how to speed up this ?

On 23 Dec 2010, at 7:36pm, Vander Clock Stephane wrote: > when their is lower than 1 000 000 row it's return in lower than 10 ms > with more than 1 000 000 row it's return with around 350 ms :( > and i need more than 50 000 000 rows :( :( How much memory do you have in that computer ? What

Re: [sqlite] How to speed up a query between two tables?

On Tue, 2009-09-15 at 22:31 +1000, John Machin wrote: > On 15/09/2009 7:25 PM, Kermit Mei wrote: > > On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: > >> On 15/09/2009 4:47 PM, Kermit Mei wrote: > >>> > >>> sqlite> SELECT HomeDev.text, ZPhDev.id > >>>...> FROM ZPhDev > >>>...> INNER

Re: [sqlite] How to speed up a query between two tables?

On 15/09/2009 7:25 PM, Kermit Mei wrote: > On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: >> On 15/09/2009 4:47 PM, Kermit Mei wrote: >>> >>> sqlite> SELECT HomeDev.text, ZPhDev.id >>>...> FROM ZPhDev >>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) >> Are you sure that you

Re: [sqlite] How to speed up a query between two tables?

On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: > On 15/09/2009 4:47 PM, Kermit Mei wrote: > > Hello community! > > > > I have two tables: > > > > HomeDev(id, text, icon) > > > > and > > > > ZPhDev(id,HomeDevId) > > > > Now, I must usually access(read-only) the items: > > > > sqlite>

Re: [sqlite] How to speed up read-only databases?

Julian Bui <[EMAIL PROTECTED]> writes: > > Hey MikeW, > > The article you posted seems like something I might want to try. I am > currently using JDBC to embed sqlite in my java app. Do you know if there > are equivalent statements for java? > > Please let me know if you do. > > Thanks, >

Re: [sqlite] How to speed up read-only databases?

Hey MikeW, The article you posted seems like something I might want to try. I am currently using JDBC to embed sqlite in my java app. Do you know if there are equivalent statements for java? Please let me know if you do. Thanks, Julian On Mon, Oct 27, 2008 at 7:58 AM, MikeW <[EMAIL

Re: [sqlite] How to speed up read-only databases?

> How many memory has your embedded project? You can create a new > in-memory database and copy there your database data. > That´s what i am currently doing, but we are using too much memory this way, we are out of specs. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED]

Re: [sqlite] How to speed up read-only databases?

At 13:47 15/10/2008, you wrote: >Hi there, > >i am using a 120MB database in an embedded project (a DVD-ROM project) >and was wondering what I can do to speed up its reading using diverse >PRAGMA statements. >The database is locked, meaning that no data is being inserted or >deleted from it. I am

Re: [sqlite] How to speed up read-only databases?

> Speedup tip: > http://article.gmane.org/gmane.comp.db.sqlite.general/41990 > Hello Mike, first of all, thank you for your tips. Yes, i saw that posting, and i am already using it in my code. But thanks again, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED]

Re: [sqlite] How to speed up read-only databases?

Christophe Leske <[EMAIL PROTECTED]> writes: > How about the cache size? or does this only pertain to databases which > get inserts? > > Christophe Leske Speedup tip: http://article.gmane.org/gmane.comp.db.sqlite.general/41990 You may have seen it ! MikeW

Re: [sqlite] How to speed up read-only databases?

Regarding: ... my criteria for a lean and slim SQlite3 db access ... I can use in my DVD-ROM project. I don't know for sure, but I'd suspect that any time saved by using prepared statements would be vastly overwhelmed by even one extra DVD-rom seek. I imagine you VACUUM the database before

Re: [sqlite] How to speed up read-only databases?

> Or you could have a look at the Perl SQLite functionality: > http://search.cpan.org/~msergeant/DBD-SQLite-0.31/lib/DBD/SQLite.pm > > Yes, Perl is weird initially if you have only (say) written VB !! > Improves the résumé though ... and your ninja status ! > Perl is fine, no problem, but it

Re: [sqlite] How to speed up read-only databases?

Christophe Leske <[EMAIL PROTECTED]> writes: > > Yes, but I am using Adobe Director as a production environment. This is > a single threaded application, which also doesn´t allow for threaded > calls to a database. Plus, i got no access to the source code of the > so-called Xtra (=DLL) which

Re: [sqlite] How to speed up read-only databases?

Christophe Leske wrote: > John Stanton schrieb: > >>The sqlite3.exe program is set up as a utility and maintenance tool, not >>a production environment and is designed to that end. If you want >>maximum performance it is not the way to go; instead embed the Sqlite >>calls inside your

Re: [sqlite] How to speed up read-only databases?

John Stanton schrieb: > The sqlite3.exe program is set up as a utility and maintenance tool, not > a production environment and is designed to that end. If you want > maximum performance it is not the way to go; instead embed the Sqlite > calls inside your application and optimize access. If

Re: [sqlite] How to speed up read-only databases?

The sqlite3.exe program is set up as a utility and maintenance tool, not a production environment and is designed to that end. If you want maximum performance it is not the way to go; instead embed the Sqlite calls inside your application and optimize access. If you are performing ad-hoc DB

Re: [sqlite] How to speed up read-only databases?

John, thanks for your suggestions, but i am wondering if any of your suggestions can be applied to the sqlite3.exe command line application? > Prepare your statements only once and then use bind. Do not use > How is this done? Can the command line executable be modified in such a way? >

Re: [sqlite] How to speed up read-only databases?

Prepare your statements only once and then use bind. Do not use sqlite3_exec. Do not open and close the DB for each read, instead open once and let the cache work. Avoid row scans by defining indices. Use the new index selection functionality to force the use of the best index. Place

Re: [sqlite] How to speed up read-only databases?

> When you say 'sqlite.exe' I presume you are referring to 'sqlite3.exe' ? > (http://www.sqlite.org/sqlite.html) > Yes. And I am using v3.6.4. > Using the correct INDEX can speed queries up vastly, so if you can > identify how you are accessing the data, and then set that/those > columns as

Re: [sqlite] How to speed up read-only databases?

<[EMAIL PROTECTED]> writes: > > Hello Mike, > > Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? > The doc suggests this speeds things up ... > > I have that set, yes. > > You are not clear about which aspect is slow - are you already using > sqlite3_bind_x() and placeholders

Re: [sqlite] How to speed up read-only databases?

Hello Mike, Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? The doc suggests this speeds things up ... I have that set, yes. You are not clear about which aspect is slow - are you already using sqlite3_bind_x() and placeholders (?) in your SQL statements ? Doing a

Re: [sqlite] How to speed up read-only databases?

Christophe Leske <[EMAIL PROTECTED]> writes: > > Hi there, > > i am using a 120MB database in an embedded project (a DVD-ROM project) > and was wondering what I can do to speed up its reading using diverse > PRAGMA statements. > The database is locked, meaning that no data is being inserted

Re: [sqlite] How to speed up read-only databases?

That's a tricky one. Databases like random access, but DVD-ROM not so much :) Maybe delete indexes to force a full table scan? On Wed, Oct 15, 2008 at 5:47 AM, Christophe Leske <[EMAIL PROTECTED]> wrote: > Hi there, > > i am using a 120MB database in an embedded project (a DVD-ROM project) >

[sqlite] How to speed up read-only databases?

Hi there, i am using a 120MB database in an embedded project (a DVD-ROM project) and was wondering what I can do to speed up its reading using diverse PRAGMA statements. The database is locked, meaning that no data is being inserted or deleted from it. I am solely after speeding up its reading

Re: [sqlite] How to speed up my queries?

On Wed, Jun 4, 2008 at 7:12 AM, Christophe Leske <[EMAIL PROTECTED]> wrote: > Wilson, Ron P schrieb: > > I'm not a guru yet, but I think you are not using the latlon index in > > your query. Perhaps if you index on lat and lon separately your query > > will use those indices. I think the lines

Re: [sqlite] How to speed up my queries?

Hi Christophe, Regarding: What I find to be weird is that just ONE index seems to yield the same results as several fields indexed: Perhaps you're using this already, but prefixing your SELECT with "EXPLAIN QUERY PLAN" will quickly identify exactly which, if any indicies are used. It's a

Re: [sqlite] How to speed up my queries?

> class_dds has a maximum value of 6, so there where-clause "class_dds<11" > is totally unecessary - if i ditch this part, the response time is > coming down to 900ms from 2700ms for my request. > I will now time again. > Some new timings - i basically got it. What I find to be weird is that

Re: [sqlite] How to speed up my queries?

Christophe Leske schrieb: >> Question, have you tried an index on class_dds, longitude_DDS, and >> latitude_DDS? >> >> CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS); >> >> Since all three fields are used in the query, I am curious if that would >> help in any way. >>

Re: [sqlite] How to speed up my queries?

> Question, have you tried an index on class_dds, longitude_DDS, and > latitude_DDS? > > CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS); > > Since all three fields are used in the query, I am curious if that would > help in any way. > Doesn´t do anything, there is

Re: [sqlite] How to speed up my queries?

Christophe Leske wrote: > There is virtually no difference in using indices or not in my query. > > I also tried to reformulate my statement in order not to use BETWEEN but > a sandwiched > and < statement: > > SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN > 6.765103 and

Re: [sqlite] How to speed up my queries?

Federico Granata schrieb: > can you post those rows with > .mode insert > so I can do a fast try ? > INSERT INTO table VALUES('Pietraporzio',5,-1,7.032936,44.345913); INSERT INTO table VALUES('Sambuco',5,-1,7.081367,44.33763); INSERT INTO table VALUES('Le Pra',6,-1,6.88,44.316667); INSERT

Re: [sqlite] How to speed up my queries?

can you post those rows with .mode insert so I can do a fast try ? Tnx. -- [image: Just A Little Bit Of Geekness] Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). 2008/6/4 Christophe Leske

Re: [sqlite] How to speed up my queries?

There is virtually no difference in using indices or not in my query. I also tried to reformulate my statement in order not to use BETWEEN but a sandwiched > and < statement: SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN

Re: [sqlite] How to speed up my queries?

> Can you give me some row of your db (also fake data are ok) so I try to > populate a db with 840k row and test your query on my machine ... > You can either take these rows here: Pietraporzio|5|-1|7.032936|44.345913 Sambuco|5|-1|7.081367|44.33763 Le Pra|6|-1|6.88|44.316667

Re: [sqlite] How to speed up my queries?

> > Can someone tell me what kind of performance one is to expect from a > 40Mb Sqlite database like the one I have? > if you put it on a floppy and throw it out of the window it fall at 9.8 m/s ... Can you give me some row of your db (also fake data are ok) so I try to populate a db with 840k

Re: [sqlite] How to speed up my queries?

Wilson, Ron P schrieb: > I'm not a guru yet, but I think you are not using the latlon index in > your query. Perhaps if you index on lat and lon separately your query > will use those indices. I think the lines below indicate using the > indices on class_dds and rowid. > Thanks to everyone

Re: [sqlite] How to speed up my queries?

On 6/3/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote: > > > > We have a city database that is being queried regurlarly depending on > > the lat/long position of the viewport in order to show city names and > > labels. > > > > SQLite has

Re: [sqlite] How to speed up my queries?

On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote: > > We have a city database that is being queried regurlarly depending on > the lat/long position of the viewport in order to show city names and > labels. SQLite has an optional R-Tree engine. The R-Tree is a new addition and has not

Re: [sqlite] How to speed up my queries?

> -- Even if you only go down to 1'-by-1' granularity, you've divided the > world into 64,800 blocks. Assuming that your 840K cities are all over the > globe, and that about 70% of Earth is covered by water, that means that only > about 20,000 blocks would actually have cities in them. But with

Re: [sqlite] How to speed up my queries?

On Tue, Jun 3, 2008 at 1:27 PM, Christophe Leske <[EMAIL PROTECTED]> wrote: > Hi, > > i am a new member of this list and interested in speeding up my sqlite > queries. > > I am using SQlite in a 3d environment which is close to Google Earth or > Nasa WorldWind. > > We have a city database that is

Re: [sqlite] How to speed up my queries?

On Tue, Jun 03, 2008 at 07:56:11PM +0200, Christophe Leske scratched on the wall: > A typical query that causes problems would be: > > SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN > 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and > 44.424779) ORDER BY

Re: [sqlite] How to speed up my queries?

On Jun 3, 2008, at 7:27 PM, Christophe Leske wrote: > i am a new member of this list and interested in speeding up my > sqlite queries. There are no magic bullets, but "The SQLite Query Optimizer Overview" is a good read: http://www.sqlite.org/optoverview.html As well as "Query Plans":

Re: [sqlite] How to speed up my queries?

Leske Sent: Tuesday, June 03, 2008 1:56 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] How to speed up my queries? Wilson, Ron P schrieb: > Hi Christophe, > > 1. Please give us an example query. SELECT * FROM Cities where > LONGITUDE_DDS=? AND LATITUDE_DDS=

Re: [sqlite] How to speed up my queries?

Discussion of SQLite Database Subject: Re: [sqlite] How to speed up my queries? Wilson, Ron P schrieb: > Hi Christophe, > > 1. Please give us an example query. SELECT * FROM Cities where > LONGITUDE_DDS=? AND LATITUDE_DDS=? > 2. Paste in the EXPLAIN results from the command

Re: [sqlite] How to speed up my queries?

Wilson, Ron P schrieb: > Hi Christophe, > > 1. Please give us an example query. SELECT * FROM Cities where > LONGITUDE_DDS=? AND LATITUDE_DDS=? > 2. Paste in the EXPLAIN results from the command line tool. > 3. Is the database file local or are you accessing it over a network? > Hi, the

Re: [sqlite] How to speed up my queries?

Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske Sent: Tuesday, June 03, 2008 1:27 PM To: General Discussion of SQLite Database Subject: [sqlite] How to speed up my queries? Hi, i am a new member of this list

[sqlite] How to speed up my queries?

Hi, i am a new member of this list and interested in speeding up my sqlite queries. I am using SQlite in a 3d environment which is close to Google Earth or Nasa WorldWind. We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to

Re: [sqlite] how to speed up copy all the data in file db to memory db?

VACUUM ought to work on a :memory: database, I would think. Give it a try and see what happens. --- PY <[EMAIL PROTECTED]> wrote: > Do you have any idea for my question 2?? > > 2. How to release the unused page in the memory database? just like the > > vacuum in the file database.

Re: [sqlite] how to speed up copy all the data in file db to memory db?

Thanks. I will try it. Do you have any idea for my question 2?? 2. How to release the unused page in the memory database? just like the vacuum in the file database. Thanks. On 12/24/06, Joe Wilson <[EMAIL PROTECTED]> wrote: This mailing list thread may be helpful:

Re: [sqlite] how to speed up copy all the data in file db to memory db?

This mailing list thread may be helpful: http://www.mail-archive.com/sqlite-users@sqlite.org/msg15902.html --- PY <[EMAIL PROTECTED]> wrote: > I am using a memory database in an embedded device with sqlite_3.3.5. > For performance consideration, all the database access are running in the > SDRAM.

[sqlite] how to speed up copy all the data in file db to memory db?

Hi All, I am using a memory database in an embedded device with sqlite_3.3.5. For performance consideration, all the database access are running in the SDRAM. I need to perform both CopyFileDBToMemDB and CopyMemDBToFileDB with high performace and less memory used. My current solution is create

Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?

PY wrote: In fact, I use sqlite in a embedded device. So the memroy problem is really critical to us. For profermance improving, all of the database running in a in-memory database. Hmm, I am not 100% sure, but I think queries like select distinct x from foo or select distinct x from foo

Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?

Dear Chandrashekar, Sorry, I don't understand what is the precompiled query. Would you please help me to descript that? Thanks for your great help. VK On 9/19/06, Chandrashekar H S <[EMAIL PROTECTED]> wrote: Hi Are u using precompiled queries? If not try using it... On 9/19/06, PY

Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?

Hi all, Thanks for your suggestion. In fact, I use sqlite in a embedded device. So the memroy problem is really critical to us. For profermance improving, all of the database running in a in-memory database. For my case, is that will improve the query performance if I create the temp table of

Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?

Hi, I had a similar problem at a larger scale. One trick is to create a temporary table from where you can fetch the rows quickly: Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT); create index idx_foo on foo(x); insert into foo(x) values('text001'); : : insert into

Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?

PY uttered: Hi All, I have a problem about LIMIT & OFFSET profermance. Due to the limitation of memory, I could not get all of the query result at a time. In our soluction, we use the LIMIT and OFFSET to avoid the problem of memory issue. we observed the performance of LIMIT & OFFSET, it looks

Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?

On 9/18/06, PY <[EMAIL PROTECTED]> wrote: Hi All, I have a problem about LIMIT & OFFSET profermance. Is there any chance you can use an index on your select? If it has an index it might be able to calculate the position of the offset and not have to read the entire result set. This is just a

Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?

Hi Are u using precompiled queries? If not try using it... On 9/19/06, PY <[EMAIL PROTECTED]> wrote: Hi All, I have a problem about LIMIT & OFFSET profermance. Due to the limitation of memory, I could not get all of the query result at a time. In our soluction, we use the LIMIT and OFFSET to

[sqlite] How to speed up the performance in LIMIT & OFFSET?

Hi All, I have a problem about LIMIT & OFFSET profermance. Due to the limitation of memory, I could not get all of the query result at a time. In our soluction, we use the LIMIT and OFFSET to avoid the problem of memory issue. we observed the performance of LIMIT & OFFSET, it looks like a liner

Re: [sqlite] how to speed up da insertion?

On 5/9/06, Will <[EMAIL PROTECTED]> wrote: I insert like followings : rc = sqlite3_prepare(m_pDB, szSQL, -1, , 0); sqlite3_bind_int64(pStmt, 1, m_pTBDataDBData->lTime); ... rc = sqlite3_step(pStmt); rc =sqlite3_finalize(pStmt); Putting your insert statements inside a transaction will

[sqlite] how to speed up da insertion?

I insert like followings : rc = sqlite3_prepare(m_pDB, szSQL, -1, , 0); sqlite3_bind_int64(pStmt, 1, m_pTBDataDBData->lTime); ... rc = sqlite3_step(pStmt); rc =sqlite3_finalize(pStmt); I feel this is not the best way for insertion. I insert over 400 times per second. I'm beginer

Re: [sqlite] How to speed up create index on temp database?

Jay Sprenkle wrote: On 11/10/05, Huang Tao <[EMAIL PROTECTED]> wrote: Hello: I run sqlite in embedded system which use nand flash. So I have to reduce write count. Save index in master database will cause much write. I try to dynamic create index on temp database. But the speed is not very

RE: [sqlite] How to speed up create index on temp database?

> > Can you move your temp tables to in an in-memory(RAM) database? In the package I posted earlier (the uSQLiteServer), the example database supplied with the system shows the use of memory tables used in conjunction with disk based tables, and how you can set up the memory tables from data

Re: [sqlite] How to speed up create index on temp database?

On 11/10/05, Huang Tao <[EMAIL PROTECTED]> wrote: > Hello: > > I run sqlite in embedded system which use nand flash. So I have to > reduce write count. Save index in master database will cause much > write. I try to dynamic create index on temp database. But the speed > is not very well. >

[sqlite] How to speed up create index on temp database?

Hello: I run sqlite in embedded system which use nand flash. So I have to reduce write count. Save index in master database will cause much write. I try to dynamic create index on temp database. But the speed is not very well. example: create table employee (id int primary key not null, name);

  1   2   >