Re: [sqlite] How to determine player's leaderboard rank efficiently?

[Alessandro Marzocchi]

1) Create data (now I use a script in python to create 10 millions rows
with random data)

test.py:

import random
import sqlite3
db=sqlite3.connect('players.db')
c=db.cursor()
c.execute("BEGIN")
c.execute("CREATE TABLE player(name PRIMARY KEY, score REAL, rank INTEGER)")
for i in range(1000):
  if i and not i%10:
print i
  c.execute("INSERT INTO player VALUES ('Player%d',%f,
NULL)"%(i,random.random()))
c.commit()

$ time python test.py > create.sql
real5m29.157s
user5m22.137s
sys0m5.457s

2) Create index on score as suggested by you
$ time echo "CREATE INDEX playerScore ON PLAYER (score);" | sqlite3
players.db

real0m44.660s
user0m39.179s
sys0m3.435s

3) Also create index on rank (for now containing all nulls)
$ time echo "CREATE INDEX playerRank ON PLAYER (rank);" | sqlite3
players.db

real0m42.639s
user0m38.537s
sys0m2.787s

4) Calculate rank of players
$ time echo "CREATE TABLE playerCalculatedRank AS SELECT(name) FROM PLAYER
ORDER BY score;" | sqlite3 players.db

real1m22.784s
user0m44.507s
sys0m37.144s

5) Index name
$ time echo "CREATE INDEX playerRankName ON playerCalculatedRank (name);" |
sqlite3 players.db

real0m45.452s
user0m39.530s
sys0m4.129s

6) Associate rank back to players
$ time echo "UPDATE player SET RANK=(SELECT rowid FROM playerCalculatedRank
WHERE playerCalculatedRank.name=player.name);" | sqlite3 players.db

real1m26.919s
user0m51.850s
sys0m9.398s

7) Now you can query efficiently scores

SELECT * FROM player ORDER BY SCORE limit 10;
Player2257196|0.0|1
Player3581965|0.0|2
Player5899654|0.0|3
Player6830263|0.0|4
Player7539953|0.0|5
Player813208|1.0e-06|6
Player1197941|1.0e-06|7
Player5692742|1.0e-06|8
Player6457603|1.0e-06|9
Player7434030|1.0e-06|10

$ time echo "SELECT * FROM player WHERE rank>1516116 ORDER BY rank LIMIT
10;" | sqlite3 players.db
Player607250|0.151696|1516117
Player1727261|0.151696|1516118
Player1951394|0.151696|1516119
Player2423122|0.151696|1516120
Player2601434|0.151696|1516121
Player3854947|0.151696|1516122
Player4468451|0.151696|1516123
Player6382469|0.151696|1516124
Player7191942|0.151696|1516125
Player8168866|0.151696|1516126

real0m0.004s
user0m0.002s
sys0m0.003s

For doing 4+5+6 you could/should use temp table


2014-08-23 17:03 GMT+02:00 Tom :

>
> Hi,
>
> I have a table of players each with a score. I need to do 2 things:
>
> 1. select top n players sorted on score - this is easy, just select stuff
> order by score desc limit n. For efficiency, use an index on score. The top
> guy is rank 1, the next guy is rank 2, etc. I only need to do this once a
> day, so it doesn't have to be super fast.
>
> 2. determine the rank of an arbitrary player p. This is harder. I need to
> do
> it many times. I presently select count(*) + 1 where score is > score of
> player p.
>
> The problem is that #2 is too slow when I have 1M players in the table -
> around 80 ms (with an index on score). I need something much faster -  a
> few
> ms is OK.
>
> I tried writing rank into the table as part of the leaderboard processing
> by
> doing a select order by score desc with an update for each row, but this is
> too slow (even using begin/commit). Would take around 5 hours for 1M
> players, but I need something that would take just minutes.
>
> Would appreciate any ideas.
>
> Thanks
>
> Tom
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/How-to-determine-player-s-leaderboard-rank-efficiently-tp77445.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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] How to determine player's leaderboard rank efficiently?

[Tom]

Hi,

I have a table of players each with a score. I need to do 2 things:

1. select top n players sorted on score - this is easy, just select stuff
order by score desc limit n. For efficiency, use an index on score. The top
guy is rank 1, the next guy is rank 2, etc. I only need to do this once a
day, so it doesn't have to be super fast.

2. determine the rank of an arbitrary player p. This is harder. I need to do
it many times. I presently select count(*) + 1 where score is > score of
player p.

The problem is that #2 is too slow when I have 1M players in the table -
around 80 ms (with an index on score). I need something much faster -  a few
ms is OK.

I tried writing rank into the table as part of the leaderboard processing by
doing a select order by score desc with an update for each row, but this is
too slow (even using begin/commit). Would take around 5 hours for 1M
players, but I need something that would take just minutes. 

Would appreciate any ideas.

Thanks

Tom




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-determine-player-s-leaderboard-rank-efficiently-tp77445.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] -init file

[Keith Medcalf]

>> sqlite  can  be initialized using resource files. These can be combined
>>   with command line arguments to set up sqlite exactly the way  you
>> want
>>   it.  Initialization proceeds as follows:
>>
>>   o The defaults of
>>
>>
>>   mode= LIST
>>   separator   = "|"
>>   main prompt = "sqlite> "
>>   continue prompt = "   ...> "
>>
>>
>>
>> found on
>> http://www.linuxcommand.org/man_pages/sqlite31.html
>
>That man page is a little outdated.  It used SQLite version 3.0.8 and its
>date at the bottom is
>Mon Apr 15 23:49:17 2002  SQLITE(1)

Nonetheless, in no less than two places in the man page is it stated that the 
init file should contain dot-commands which will be processed by the shell 
after its standard initialization and before processing commands supplied via 
whatever stdin stream content it would, but for the init commands, process.

While the specifics of the dot-commands themselves may have changed, or there 
may be new ones or deprecated ones removed depending on version, the 
description of the processing is still accurate.




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

Re: [sqlite] -init file

[jose isaias cabrera]

"Giuseppe Costanzi" wrote...



thanks Simon,
stupidly I have used this,

sqlite  can  be initialized using resource files. These can be combined
  with command line arguments to set up sqlite exactly the way  you 
want

  it.  Initialization proceeds as follows:

  o The defaults of


  mode= LIST
  separator   = "|"
  main prompt = "sqlite> "
  continue prompt = "   ...> "



found on
http://www.linuxcommand.org/man_pages/sqlite31.html


That man page is a little outdated.  It used SQLite version 3.0.8 and its 
date at the bottom is

Mon Apr 15 23:49:17 2002  SQLITE(1)



regards
beppe

On Sat, Aug 23, 2014 at 6:35 PM, Simon Slavin  
wrote:


On 23 Aug 2014, at 5:28pm, Giuseppe Costanzi  
wrote:



my file, setconsole

headers = ON
mode= COLUMN
timer   = ON


Those commands are dot commands, not proper SQLite commands.  They need 
to have dots in front of them.  Also, having an equals sign there is 
incorrect.  Those lines should look more like


.headers ON
.mode COLUMN
.timer ON

If you are in doubt about a command try typing it into the SQLite shell 
tool and see if it does what you want or gives you an error message.


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] -init file

[Simon Slavin]

On 23 Aug 2014, at 5:49pm, Giuseppe Costanzi  wrote:

> thanks Simon,
> stupidly I have used this,

I can see how you made that mistake.  That way of showing defaults makes sense 
if you are reading many man pages one after another, but it's not the format 
that SQLite needs in the files.  I'm glad you got it working.  Post again if 
you think we can help.

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

Re: [sqlite] -init file

[Giuseppe Costanzi]

thanks Simon,
stupidly I have used this,

 sqlite  can  be initialized using resource files. These can be combined
   with command line arguments to set up sqlite exactly the way  you  want
   it.  Initialization proceeds as follows:

   o The defaults of


   mode= LIST
   separator   = "|"
   main prompt = "sqlite> "
   continue prompt = "   ...> "



 found on
http://www.linuxcommand.org/man_pages/sqlite31.html

regards
beppe

On Sat, Aug 23, 2014 at 6:35 PM, Simon Slavin  wrote:
>
> On 23 Aug 2014, at 5:28pm, Giuseppe Costanzi  
> wrote:
>
>> my file, setconsole
>>
>> headers = ON
>> mode= COLUMN
>> timer   = ON
>
> Those commands are dot commands, not proper SQLite commands.  They need to 
> have dots in front of them.  Also, having an equals sign there is incorrect.  
> Those lines should look more like
>
> .headers ON
> .mode COLUMN
> .timer ON
>
> If you are in doubt about a command try typing it into the SQLite shell tool 
> and see if it does what you want or gives you an error message.
>
> 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] Insert optimization

[Jean-Christophe Deschamps]

How to improve the write speed then.


I've found that in most cases using "chained" insertion speeds up 
things dramatically:


insert into mytable (x, y, z) values (1, 2, 3), (4, 5, 6), (7, 8, 9), ...

If you insert K rows at a time this way your total insert time can by 
cut by a significant factor based on K. Always wrap bulk inserts in 
transactions.


Also building index after insertion is beneficial.

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

Re: [sqlite] -init file

[Simon Slavin]

On 23 Aug 2014, at 5:28pm, Giuseppe Costanzi  wrote:

> my file, setconsole
> 
> headers = ON
> mode= COLUMN
> timer   = ON

Those commands are dot commands, not proper SQLite commands.  They need to have 
dots in front of them.  Also, having an equals sign there is incorrect.  Those 
lines should look more like

.headers ON
.mode COLUMN
.timer ON

If you are in doubt about a command try typing it into the SQLite shell tool 
and see if it does what you want or gives you an error message.

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

[sqlite] -init file

[Giuseppe Costanzi]

HI all,
I am trying to open an sqlite3  shell session passing a configuration
file with the option -init  but the console it doesn't shape

my file, setconsole

headers = ON
mode= COLUMN
timer   = ON

pep@hal9000:~/test/server$ sqlite3 -init setconsole test.db
SQLite version 3.7.13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from halflives;
1|421|530|519|37.8|37.8|6|518|Nessuna annotazione|1
2|420|530|519|35.0|37.0|2|517|Nessuna annotazione|1
3|264|530|519|4.0|8.0|7|522|Nessuna annotazione|1
4|264|526|519|-20.0|-20.0|1|517|Nessuna annotazione|1
5|264|526|519|20.0|25.0|1|514|Nessuna annotazione|1
6|105|529|520|-20.0|-20.0|2|522|Acidificare le urine|1
7|105|529|520|4.0|8.0|2|522|Acidificare le urine|1
8|105|529|520|20.0|25.0|2|521|Acidificare le urine|1
9|418|530|520|25.0|25.0|7|522|Nessuna annotazione|1
10|418|530|520|-20.0|-20.0|6|516|Nessuna annotazione|1
11|418|530|520|4.0|8.0|7|522|Nessuna annotazione|1
12|418|530|520|20.0|25.0|3|522|Nessuna annotazione|1
13|385|526|519|37.0|37.0|15|521|L'emivita biologiaca in realta' varia
tra le 8 e le 15 ore|1
14|385|526|520|20.0|25.0|2|522|Nessuna annotazione|1
15|385|526|520|-20.0|-20.0|3|524|Nessuna annotazione|1
16|385|526|520|4.0|8.0|7|522|Nessuna annotazione|1
17|385|526|520|20.0|25.0|2|522|Nessuna annotazione|1


suggests?

p.s.
I'm on debian 6

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

Re: [sqlite] Insert optimization

[Simon Slavin]

On 22 Aug 2014, at 7:08pm, Sherry Ummen  wrote:

>   We have been trying out couchbase lite which uses sqlite underneath.
> 
> I want to know how safe is use
> PRAGMA synchronous = OFF");
> "PRAGMA journal_mode = MEMORY"
> 
> I have been suggested not to use it but then its soo dangerous then why is it 
> thr in first place?
> 
> It says the databse might get corrupted so corrupted meaning it cannot be 
> fixed?

This combination of commands allows you to make changes to a SQLite database 
extremely quickly.  However, if your computer suffers a hardware fault or loses 
power while a change is being made to the database, the database file may be 
corrupted so badly that you cannot recover any information from it, even data 
which was entered in previous sessions.

So you can use that combination of PRAGMAs for temporary processing -- perhaps 
reading in a text file and producing some statistics from it.  But you should 
not use it for any database which is used for /keeping/ valuable information, 
or for any information that would need reconstruction by a human.

For safe and fast use of a SQLite database avoid PRAGMAs and concentrate on 
efficient use of transactions.

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

[sqlite] Insert optimization

[Sherry Ummen]

Hello,

   We have been trying out couchbase lite which uses sqlite underneath.

I want to know how safe is use
PRAGMA synchronous = OFF");
"PRAGMA journal_mode = MEMORY"

I have been suggested not to use it but then its soo dangerous then why is it 
thr in first place?

It says the databse might get corrupted so corrupted meaning it cannot be fixed?

How to improve the write speed then.


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

Re: [sqlite] Sqlite RTree nearest neighbour

[skywind mailing lists]

Hi Clemens,

thanks for the link!

Regards,
Hartwig

Am 22.08.2014 um 22:26 schrieb Clemens Ladisch :

> skywind mailing lists wrote:
>> I hoped that somebody already tried to implement a nearest neighbor
>> algorithm.
> 
> Typically, objects are not axis-aligned rectangles, and the R-tree is
> just an index based on the bounding boxes.  Computing the (nearest)
> distance would require the actual geometries.
> 
>> Is the format of the shadow tables somewhere documented or do I have
>> to analyze the source code?
> 
> rtree.c says:
> 
> ** Database Format of R-Tree Tables
> ** 
> **
> ** The data structure for a single virtual r-tree table is stored in three
> ** native SQLite tables declared as follows. In each case, the '%' character
> ** in the table name is replaced with the user-supplied name of the r-tree
> ** table.
> **
> **   CREATE TABLE %_node(nodeno INTEGER PRIMARY KEY, data BLOB)
> **   CREATE TABLE %_parent(nodeno INTEGER PRIMARY KEY, parentnode INTEGER)
> **   CREATE TABLE %_rowid(rowid INTEGER PRIMARY KEY, nodeno INTEGER)
> **
> ** The data for each node of the r-tree structure is stored in the %_node
> ** table. For each node that is not the root node of the r-tree, there is
> ** an entry in the %_parent table associating the node with its parent.
> ** And for each row of data in the table, there is an entry in the %_rowid
> ** table that maps from the entries rowid to the id of the node that it
> ** is stored on.
> **
> ** The root node of an r-tree always exists, even if the r-tree table is
> ** empty. The nodeno of the root node is always 1. All other nodes in the
> ** table must be the same size as the root node. The content of each node
> ** is formatted as follows:
> **
> **   1. If the node is the root node (node 1), then the first 2 bytes
> **  of the node contain the tree depth as a big-endian integer.
> **  For non-root nodes, the first 2 bytes are left unused.
> **
> **   2. The next 2 bytes contain the number of entries currently
> **  stored in the node.
> **
> **   3. The remainder of the node contains the node entries. Each entry
> **  consists of a single 8-byte integer followed by an even number
> **  of 4-byte coordinates. For leaf nodes the integer is the rowid
> **  of a record. For internal nodes it is the node number of a
> **  child page.
> 
> For a simple search algorithm, see .
> 
> 
> Regards,
> Clemens
> ___
> 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] Crash in sqlite3_mutex_try [Was: SQLite 3.8.6 beta]

[Jan Nijtmans]

2014-08-22 9:14 GMT+02:00 Jan Nijtmans :
> It looks like this is on its way to being corrected:
>
>
> However, I don't think this will work on Win95/98/NT

Fixed here:
 

Thanks! No more remarks!

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