Re: [sqlite] I'm sorry to ask this, but I fear I must

[Kevin Benson]

2011/6/15 سليمان مصطفى 

> Hi, I'm sorry to ask this as I'm confident that it has been answered
> before. what does SQLITE_OPEN_PRIVATECACHE do exactly?
>
>
http://www.sqlite.org/c3ref/open.html

The 
SQLITE_OPEN_PRIVATECACHEflag
causes the database connection to not participate in shared
cache mode  even if it is enabled.
--
   --
  --
 --ô¿ô--
K e V i N

> I've searched the faq section as well as evey singe page I could think
> of with no success.
> Thank you in advance for your help.
> ___
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] I'm sorry to ask this, but I fear I must

[سليمان مصطفى]

Hi, I'm sorry to ask this as I'm confident that it has been answered
before. what does SQLITE_OPEN_PRIVATECACHE do exactly?

I've searched the faq section as well as evey singe page I could think
of with no success.
Thank you in advance for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto get string from pragma command

[Simon Slavin]

On 15 Jun 2011, at 5:08am, J Decker wrote:

> then it should return something other than SQLITE_DONE?
> 
> On Tue, Jun 14, 2011 at 7:24 PM, Simon Slavin  wrote:
>> 
>> On 15 Jun 2011, at 3:12am, J Decker wrote:
>> 
>>> How do I get the string after issuing a PRAGMA ?
>> 
>> Pretend you were issuing a SELECT command, and retrieve the table it would 
>> return.  Most pragmas just return a single-row single-column table.

I would assume that for PRAGMAs which return anything you'll see a SQLITE_ROW 
at some point.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto get string from pragma command

[J Decker]

then it should return something other than SQLITE_DONE?

On Tue, Jun 14, 2011 at 7:24 PM, Simon Slavin  wrote:
>
> On 15 Jun 2011, at 3:12am, J Decker wrote:
>
>> How do I get the string after issuing a PRAGMA ?
>
> Pretend you were issuing a SELECT command, and retrieve the table it would 
> return.  Most pragmas just return a single-row single-column table.
>
> Simon.
>
> ___
> 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] SQLITE run slow

[Simon Slavin]

On 15 Jun 2011, at 4:55am, Hoang Linh Duong wrote:

> It is a good idea to divide into a sector. I will try that. But according to
> your suggestion, using LIKE is not advisable, since if i create an index
> (category, sector), this index will not be applicable for LIKE.

I am not sure whether with both clauses (category and sector) the SQLite engine 
will correctly use the index.  But since it involves no change to your program 
you might try it, and see whether having the index in the database speeds up 
the search.  If it does not, you can simply delete the index.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE run slow

[Hoang Linh Duong]

It is a good idea to divide into a sector. I will try that. But according to
your suggestion, using LIKE is not advisable, since if i create an index
(category, sector), this index will not be applicable for LIKE.

Thanks.

On Wed, Jun 15, 2011 at 11:35 AM, Simon Slavin  wrote:

>
> On 15 Jun 2011, at 3:56am, Hoang Linh Duong wrote:
>
> > I have tried R-Tree, the execution time is even longer.
> >
> > For a trigger to calculate the distance, is it too much a burden if the
> > query is executed very frequently (the table Location is of more than
> 600k
> > records). I suspect that it may take even longer time if we need to do
> > update & query.
> >
> > After trying two ways to optimize (use standard query on 1 table, use
> > R-Tree), i still cannot improve execution time (still >1min).
> >
> > Is there any other ways?
>
> I would like to check that you are calculating the distance to different
> points most times.  So the example you gave is just one example, and the
> next time you do the calculation you will calculate the distance to a
> different place.
>
> After that I notice that you are interested only in the closest hundred
> points.  Since you have 600K records, you can reject most of them very
> quickly if they are a long way away from your target place.  So what you
> might do is divide up your area into sectors.  Make a table of sectors, and
> another table which says which sectors are nearby (in case the target is
> near the edge of a sector.  So for example if your sectors are
>
> ABCDE
> FGHIJ
> KLMNO
> PQRST
>
> Then if your target point is in sector D, then you only need to look for
> points in sectors CDEHIJ.  You can store a 'closeSectors' column in your
> Location TABLE like this
>
> nametypecategoryx   y   closeSectors
>ABFG
>ABCFGH
>BCDGHI
> …   …
>
> Every time you save a new row in Location, work out the sectors which are
> close to it (you could look them up in another table) and save the
> 'closeSectors' column too.  Then when you come to do your SELECT, add in
> this clause:
>
> ... WHERE category = 17 AND closeSectors LIKE '%C%' ...
>
> You can calculate the '%C%' first from another table with maximum and
> minimum X and Y coordinates in.
>
> For details on 'LIKE' expressions see half way down
>
> 
>
> Simon.
> ___
> 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] SQLITE run slow

[Simon Slavin]

On 15 Jun 2011, at 3:56am, Hoang Linh Duong wrote:

> I have tried R-Tree, the execution time is even longer.
> 
> For a trigger to calculate the distance, is it too much a burden if the
> query is executed very frequently (the table Location is of more than 600k
> records). I suspect that it may take even longer time if we need to do
> update & query.
> 
> After trying two ways to optimize (use standard query on 1 table, use
> R-Tree), i still cannot improve execution time (still >1min).
> 
> Is there any other ways?

I would like to check that you are calculating the distance to different points 
most times.  So the example you gave is just one example, and the next time you 
do the calculation you will calculate the distance to a different place.

After that I notice that you are interested only in the closest hundred points. 
 Since you have 600K records, you can reject most of them very quickly if they 
are a long way away from your target place.  So what you might do is divide up 
your area into sectors.  Make a table of sectors, and another table which says 
which sectors are nearby (in case the target is near the edge of a sector.  So 
for example if your sectors are

ABCDE
FGHIJ
KLMNO
PQRST

Then if your target point is in sector D, then you only need to look for points 
in sectors CDEHIJ.  You can store a 'closeSectors' column in your Location 
TABLE like this

nametypecategoryx   y   closeSectors
ABFG
ABCFGH
BCDGHI
…   …

Every time you save a new row in Location, work out the sectors which are close 
to it (you could look them up in another table) and save the 'closeSectors' 
column too.  Then when you come to do your SELECT, add in this clause:

... WHERE category = 17 AND closeSectors LIKE '%C%' ...

You can calculate the '%C%' first from another table with maximum and minimum X 
and Y coordinates in.

For details on 'LIKE' expressions see half way down



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE run slow

[Hoang Linh Duong]

I have tried R-Tree, the execution time is even longer.

For a trigger to calculate the distance, is it too much a burden if the
query is executed very frequently (the table Location is of more than 600k
records). I suspect that it may take even longer time if we need to do
update & query.

After trying two ways to optimize (use standard query on 1 table, use
R-Tree), i still cannot improve execution time (still >1min).

Is there any other ways?



On Tue, Jun 14, 2011 at 10:56 PM, Jim Morris  wrote:

> You might also consider a trigger to calculate the distance once on
> insert/update.  They you could use an index.
>
> On 6/14/2011 4:53 AM, Simon Slavin wrote:
> > On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote:
> >
> >> make sure you have an index on  category and distance, like
> >>
> >> create index MyIndex on Location  (category, distance)
> > Peter is calculating distance inside his SELECT, but I agree that an
> index on (category) is an excellent idea.
> >
> > The other thing is that this is a bit like RTREEs.  So Peter, you might
> like to read
> >
> > 
> >
> > I don't know if it's worth using rtrees in your particular example but
> you might find them useful.
> >
> > Simon.
> > ___
> > 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] Howto get string from pragma command

[Simon Slavin]

On 15 Jun 2011, at 3:12am, J Decker wrote:

> How do I get the string after issuing a PRAGMA ?

Pretend you were issuing a SELECT command, and retrieve the table it would 
return.  Most pragmas just return a single-row single-column table.

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Howto get string from pragma command

[J Decker]

How do I get the string after issuing a PRAGMA ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Small documentation error on PRAGMA page.

[Andrew Sigmund]

Looking up information for the user_version PRAGMA, I noticed that on the page
http://www.sqlite.org/pragma.html
the link to user_version,
http://www.sqlite.org/pragma.html#pragma_user_version ,
doesn't work -- it leaves me at the top of the page.

So I scroll to the bottom of the alphabetical list of PRAGMAs, and don't find 
user_version.
Then searched the page for "user_version" -- ahh, there I found it, with 
schema_version.

FWIW: The link to schema_version,
http://www.sqlite.org/pragma.html#pragma_schema_version ,
does work.

It'd be nice if someone could fix that, because it's rather confusing.

Thanks,
Andy

Confidentiality Notice.
This message may contain information that is confidential or otherwise 
protected from disclosure. If you are not the intended recipient, you are 
hereby notified that any use, disclosure, dissemination, distribution,  or 
copying  of this message, or any attachments, is strictly prohibited.  If you 
have received this message in error, please advise the sender by reply e-mail, 
and delete the message and any attachments.  Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parse VIEW fields definitions?

[Alexey Pechnikov]

The new PRAGMA or Virtual Table will be more useful. But I don't sure
about possibility of this.

2011/6/14 Nico Williams :
> On Tue, Jun 14, 2011 at 1:58 PM, Alexey Pechnikov
>  wrote:
>> As example, we have view:
>>
>> create view vtest as select name1 || ' ' || name2 as name from test;
>>
>> How to get the definition of "name" field (will be "name1 || ' ' ||
>> name2")? Of cource, the view can be more complex.
>
> Take Lemon and the parse.y file from SQLite3, and build a parser that
> does what you want?
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to parse VIEW fields definitions?

[Nico Williams]

On Tue, Jun 14, 2011 at 1:58 PM, Alexey Pechnikov
 wrote:
> As example, we have view:
>
> create view vtest as select name1 || ' ' || name2 as name from test;
>
> How to get the definition of "name" field (will be "name1 || ' ' ||
> name2")? Of cource, the view can be more complex.

Take Lemon and the parse.y file from SQLite3, and build a parser that
does what you want?

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to parse VIEW fields definitions?

[Alexey Pechnikov]

As example, we have view:

create view vtest as select name1 || ' ' || name2 as name from test;

How to get the definition of "name" field (will be "name1 || ' ' ||
name2")? Of cource, the view can be more complex.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite seems to hang while indexing

[Rense Corten]

Hi list,

I was trying to create an index on an integer column in a very large
table with over 400,000,000 rows on an Ubuntu server, 8-core opteron
2384, 32 Gb RAM. After a couple of days, the process was still running
and nothing seemed to happen. At first, sqlite uses a lot of cpu and
more and more  memory, until it reached about 90% memory use, after
which cpu usage drops. Also, I don't see the size of the database file
increasing, which I guess should happen while the index is created. I
increased the cache size to 2 but to no avail. Can it be that
this table is somehow too large to index? Or should I just wait
longer? If so, how, do I know? Any other ideas?

Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 1.0.73.0

[Harry Green]

OK, I think I misread that !

I was reading the navigation to SQLite.Net\bin\Designer as being a 
*workround* for the temporary absence of the design time installer, but 
on second glance I conclude that it is only describing how to do 
something that is not yet implemented.

So, what should I do in the meantime? I've now downloaded and installed 
version 1.0.66.0 and that seems to have made sqlite visible within 
Visual C# as a data connection. Should I stick with 1.0.66 at present, 
or can I install 1.0.73 over the top and try to use the 1.0.66 design 
time features with the 1.0.73 updates, or is that a recipe for 
incompatibility problems ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Backup and log files format

[Simon Slavin]

On 14 Jun 2011, at 4:13pm, Tahora H.Nazer wrote:

> thanks for your answer Simon, could you please tell me file extensions of DB 
> and also log files?

SQLite lets you pick your own file name, including the extension.  Many people 
use '.sqlite' but I have seen even '.db' or no extension at all.  SQLite does 
not care what you call the file at all: it will simply take what you type and 
use it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Backup and log files format

[Tahora H.Nazer]

thanks for your answer Simon, could you please tell me file extensions of DB 
and also log files?
--- On Tue, 6/14/11, Simon Slavin  wrote:

From: Simon Slavin 
Subject: Re: [sqlite] SQLite Backup and log files format
To: "General Discussion of SQLite Database" 
Date: Tuesday, June 14, 2011, 8:00 AM


On 14 Jun 2011, at 9:04am, Tahora H.Nazer wrote:

> what is the format of the file that this DB make after doing a backup 
> operation?

It is a normal SQLite database file, using the same format as a normal 
database.  You could delete your usual database file, put the backup file in 
its place, and everything will work as you would want.

> dos SQLite make log files containing info of DB? what is its format?

If you want to turn a database into something you can read, you might like to 
dump its contents as SQL commands.  You can do this with the command-line tool:

http://www.sqlite.org/sqlite.html

which you can download from

http://www.sqlite.org/download.html

Look for 'sqlite-shell'.

Simon.
___
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] SQLITE run slow

[Jim Morris]

You might also consider a trigger to calculate the distance once on 
insert/update.  They you could use an index.

On 6/14/2011 4:53 AM, Simon Slavin wrote:
> On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote:
>
>> make sure you have an index on  category and distance, like
>>
>> create index MyIndex on Location  (category, distance)
> Peter is calculating distance inside his SELECT, but I agree that an index on 
> (category) is an excellent idea.
>
> The other thing is that this is a bit like RTREEs.  So Peter, you might like 
> to read
>
> 
>
> I don't know if it's worth using rtrees in your particular example but you 
> might find them useful.
>
> Simon.
> ___
> 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] Implementing busy-waiting on SQLite

[BareFeetWare]

On 13/06/2011, at 6:49 PM, Ian Hardingham wrote:

> As was being discussed yesterday, I have four processes accessing the 
> same database file.  When they perform an sqlite action, I wish them to 
> block if the DB is not available.  SQLite does not block if it finds the 
> db busy or locked, it returns an error code.

Short answer:

Make sure each process accesses the SQLite database inside their own 
transaction. If the transaction contains a command that will change the 
database (eg delete, update or insert), then use "begin immediate" to start the 
transaction, so that it won't proceed unless it has unchallenged access to the 
database. For other transactions, which are making no changes (ie just select), 
use "begin deferred" (which is the default behavior for plain "begin").

The logic is that only one writing (update, delete or insert) transaction at a 
time will be given a reserved lock. Each writing transaction must wait their 
turn and will not start until they can have "immediate" reserved access. 
Meanwhile, multiple read-only (select) transactions can happily start and 
finish at will.

For more info on transaction types, see: 
http://www.sqlite.org/lang_transaction.html

Set the timeouts as long as is reasonable for the queue of other write 
transactions to finish.

Tom Brodhurst-Hill
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 1.0.73.0

[Drew Hohmann]

Here's how to get sqlite installed.
http://www.tsjensen.com/blog/2011/06/04/SQLite+On+Visual+Studio+2010+Setup+Instructions.aspx

I wished they would fix the current installer.  I was completely lost until the 
above blog was posted.

Drew



 > I'm a new user trying *sqlite-dotnet-x86-1007300.exe* at
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
 >
 > According to SQLite.NET Class Library Documentation bundled with the
 > download and the section "*Installing SQLite Visual Studio Design-Time
 > Support*" I should begin by navigating to *SQLite.Net\bin\Designer *and
 > use installer.exe, but the download has not created a Designer folder
 > nor is there an installer.exe anywhere else in the tree which the
 > download created.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple relations

[BareFeetWare]

On 14/06/2011, at 8:29 PM, Ian Hardingham wrote:

> Guys, I have another DB design question.

Have you implemented the normalisation we discussed before, yet, especially id 
integer primary key column for players? Otherwise, stop here.

> I have 100 - 1 clients connected to my server.

Is a client the same as a player, people and user? I'll assume so.

> Each client has a status.  Many clients are "watching" *one* other 
> client, which means that any change in that client's status must 
> immediately be sent to the watching clients.
> 
> Estimates of numbers:
> 
> - 70% of people online will be watching someone
> //
> - 50% of people will be watched by 0 clients
> - 40% of people will be watched by 1 client
> - 9% of people will be watched by 2 clients
> - 1% of people will be watched by 3 or more clients
> 
> Here's my attempt at a schema:
> 
> - because your status is changing often I would have a separate table, 
> clientStatusTable, which would hold client ids and their status

Does every player/client have a status (one and only one)? If so, you could 
just keep the status as a column in the player/client table. But a separate 
clientStatusTable would be OK, possibly immeasurably slower. In either case, I 
suggest making a separate status table and using foreign key references to it, 
rather than storing the text of the status repeatedly for each player.

> - I would have another table, clientWatchingTable, which would store 
> relations between clients and who they are watching.  I would probably 
> index on watched client because I would need to select everyone watching 
> a client often
> 
> Does this seem like a sane approach?

Yes, that seems like a good approach, as long as you've normalised the 
player/user/client table already. Then you'd have something like this:

create table status
(   id integer primary key not null
,   name text not null unique collate nocase
)
;
create table user
(   id integer primary key not null
,   name text not null unique collate nocase
,   email text collate nocase
,   key text
,   status integer references status(id)
,   other columns
)
;
create table clientWatchingTable
(   id integer primary key not null
,   watcher references user (id)
,   watching references user (id)
)
;
create index clientWatchingWatcherIndex on clientWatchingTable (watcher)
;

And you could get the name and statuses of all users being watched by a 
particular user by:

select  watchingUser.name
,   status.name as status
from clientWatchingTable
join user on watcher = user.id
join user as watchingUser on watching = user.id
join status on watchingUser.status = status.id
where watcher = ?
;

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Migration from GMDB to SQLite, any tips?

[Simon Slavin]

On 14 Jun 2011, at 12:32pm, michael munson wrote:

> I have some software written in C that current uses GMDB as its backend
> database but for a variety of reasons (speed and the fact that the GMDB
> routinely gets corrupted if the software crashes) I would like to migrate
> this to SQLite.
> 
> Are there any tips or documents anyone would recommend beyond the obvious?

See this page for absolutely vital information on column types

http://www.sqlite.org/datatype3.html

then the end of

http://www.sqlite.org/quickstart.html

for C code.  Download and play with the command-line tool before you start 
writing code.

http://www.sqlite.org/sqlite.html

Then … erm … see everything else on that site.  If you have a specific 
question, don't hesitate to come back.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Backup and log files format

[Simon Slavin]

On 14 Jun 2011, at 9:04am, Tahora H.Nazer wrote:

> what is the format of the file that this DB make after doing a backup 
> operation?

It is a normal SQLite database file, using the same format as a normal 
database.  You could delete your usual database file, put the backup file in 
its place, and everything will work as you would want.

> dos SQLite make log files containing info of DB? what is its format?

If you want to turn a database into something you can read, you might like to 
dump its contents as SQL commands.  You can do this with the command-line tool:

http://www.sqlite.org/sqlite.html

which you can download from

http://www.sqlite.org/download.html

Look for 'sqlite-shell'.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE run slow

[Simon Slavin]

On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote:

> make sure you have an index on  category and distance, like
> 
> create index MyIndex on Location  (category, distance)

Peter is calculating distance inside his SELECT, but I agree that an index on 
(category) is an excellent idea.

The other thing is that this is a bit like RTREEs.  So Peter, you might like to 
read



I don't know if it's worth using rtrees in your particular example but you 
might find them useful.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE run slow

[Hoang Linh Duong]

I do have Index for category, but im wondering how to create index
(category, distance) since distance is a new column created in a query.

Thanks.

On Tue, Jun 14, 2011 at 7:11 PM, Martin.Engelschalk <
engelsch...@codeswift.com> wrote:

> Hi,
>
> make sure you have an index on  category and distance, like
>
> create index MyIndex on Location  (category, distance)
>
> Because many records fulfill the category=17 condition, and if database
> size is an issue, an index only on distance might help also.
>
> Martin
>
>
> Am 14.06.2011 13:01, schrieb Hoang Linh Duong:
> > Hi all,
> >
> > I have one SQL as below:
> >
> > SELECT name, type, category, x, y, ((x-645529)*(x-645529) +
> > (y-1494293)*(y-1494293)) AS distance,
> > FROM Location
> > WHERE category=17
> > ORDER BY distance
> > LIMIT 100
> >
> > I run this SQL to retrieve data from my SQLITE database and it takes more
> > than 1 minutes. Noted that my program is running on Win CE 6.0 device
> (ARM
> > 532MHz, 128MB RAM). And there are 60 records in Location table, among
> > that 11 records of type=17.
> >
> > I am just wondering if there is any way to speed up the SQL. I appreciate
> > all advices and suggestions. Thank you!
> >
> > Regards,
> > Linh
> > ___
> > 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

[sqlite] Migration from GMDB to SQLite, any tips?

[michael munson]

I have some software written in C that current uses GMDB as its backend
database but for a variety of reasons (speed and the fact that the GMDB
routinely gets corrupted if the software crashes) I would like to migrate
this to SQLite.

Are there any tips or documents anyone would recommend beyond the obvious?


regards,
michael
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ATTACH DATABASE .tables .schema ?

[Amit Chaudhuri]

I'm a huge fan of the command line tool. At work we use it extensively when
prototyping.  We're probably amongst the less technically sophisticated of
users, so the ability to quickly script an approach using a few text files
is something we value hugely. It's a great way of weaning people off
spreadsheets in situations where data volumes stretch their capabilities.
But I sometimes wonder if it could be an even bigger plus with a little more
attention.  This seems to me like a worthy area for enhancement if it is
technically "easy."

If we had strong C skills I'd be inclined to have a go. [But I can say that
in full safety!]


On Mon, Jun 13, 2011 at 4:09 PM, Jay A. Kreibich  wrote:

> On Sun, Jun 12, 2011 at 05:29:32PM -0400, ap scratched on the wall:
> > When I attach additional DB's with ATTACH DATABASE how can I view the
> schema
> > or tables in the (non main) databases?
> >
> >
> >
> > ATTACH DATABASE 'C:\temp\test.db' AS T
> >
> > .schema  T
> >
> > .tables T
> >
> > Produce no results.
>
>   You can't.  The CLI dot-commands only show results for the
>  "main" and "temp" databases.
>
>   -j
>
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> 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] SQLITE run slow

[Martin.Engelschalk]

Hi,

make sure you have an index on  category and distance, like

create index MyIndex on Location  (category, distance)

Because many records fulfill the category=17 condition, and if database 
size is an issue, an index only on distance might help also.

Martin


Am 14.06.2011 13:01, schrieb Hoang Linh Duong:
> Hi all,
>
> I have one SQL as below:
>
> SELECT name, type, category, x, y, ((x-645529)*(x-645529) +
> (y-1494293)*(y-1494293)) AS distance,
> FROM Location
> WHERE category=17
> ORDER BY distance
> LIMIT 100
>
> I run this SQL to retrieve data from my SQLITE database and it takes more
> than 1 minutes. Noted that my program is running on Win CE 6.0 device (ARM
> 532MHz, 128MB RAM). And there are 60 records in Location table, among
> that 11 records of type=17.
>
> I am just wondering if there is any way to speed up the SQL. I appreciate
> all advices and suggestions. Thank you!
>
> Regards,
> Linh
> ___
> 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] SQLITE run slow

[Hoang Linh Duong]

Hi all,

I have one SQL as below:

SELECT name, type, category, x, y, ((x-645529)*(x-645529) +
(y-1494293)*(y-1494293)) AS distance,
FROM Location
WHERE category=17
ORDER BY distance
LIMIT 100

I run this SQL to retrieve data from my SQLITE database and it takes more
than 1 minutes. Noted that my program is running on Win CE 6.0 device (ARM
532MHz, 128MB RAM). And there are 60 records in Location table, among
that 11 records of type=17.

I am just wondering if there is any way to speed up the SQL. I appreciate
all advices and suggestions. Thank you!

Regards,
Linh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple relations

[Ian Hardingham]

Guys, I have another DB design question.

I have 100 - 1 clients connected to my server.

Each client has a status.  Many clients are "watching" *one* other 
client, which means that any change in that client's status must 
immediately be sent to the watching clients.

Estimates of numbers:

- 70% of people online will be watching someone
//
- 50% of people will be watched by 0 clients
- 40% of people will be watched by 1 client
- 9% of people will be watched by 2 clients
- 1% of people will be watched by 3 or more clients

Here's my attempt at a schema:

- because your status is changing often I would have a separate table, 
clientStatusTable, which would hold client ids and their status

- I would have another table, clientWatchingTable, which would store 
relations between clients and who they are watching.  I would probably 
index on watched client because I would need to select everyone watching 
a client often

Does this seem like a sane approach?

Thanks,
Ian


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Backup and log files format

[Tahora H.Nazer]

Hello
what is the format of the file that this DB make after doing a backup 
operation? is it a ".db" file? is it human-readable? 
when we want to restore it, should we make a new DB and put the backup file 
data in it or SQLite has the ability to run sql queries on archive files?(like 
a normal DB file which needs to be just opened)
dos SQLite make log files containing info of DB? what is its format?(.X)

thank you so much
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users