Re: [sqlite] Query critique

[Ian Hardingham]

Thanks Simon.

I have a further question on this topic.

I would like to find out where my user ranks amongst all scores - so I want to

SELECT name, score FROM scoreTable WHERE id=x

And then I wish to know how many rows occur before the one where user=myuser.

I can loop through them in code, but it seems like this is intermediate data 
SQLite might be able to get quicker?



On 18/10/2010 16:12, Simon Slavin wrote:
> On 18 Oct 2010, at 4:09pm, Ian Hardingham wrote:
>
>> I also want to add selecting the highest score, and adding it to the
>> results assuming it isn't already in there (ie unless it happens your or
>> a friends' score is the highest).  Could anyone advise me on how best to
>> do that?
> Can you not do this in whatever programming language you use ?  SQL is only a 
> database language.  It's not really intended for all these conditions and 
> calculations.  You will get faster results, and easier to debug text if you 
> can write it in whatever language you're using.
>
> Simon.

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

Re: [sqlite] Query critique

[Simon Slavin]

On 18 Oct 2010, at 4:09pm, Ian Hardingham wrote:

> I also want to add selecting the highest score, and adding it to the 
> results assuming it isn't already in there (ie unless it happens your or 
> a friends' score is the highest).  Could anyone advise me on how best to 
> do that?

Can you not do this in whatever programming language you use ?  SQL is only a 
database language.  It's not really intended for all these conditions and 
calculations.  You will get faster results, and easier to debug text if you can 
write it in whatever language you're using.

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

[sqlite] Query critique

[Ian Hardingham]

  Hey guys.  I'm kind of revisiting something I asked about before.  I 
have a high scores table, and a table of friends, and I wish to select 
for user x:

The score of x
The scores of all of x's friends

Ordered by score descending.

I am using this:

SELECT * FROM (SELECT * FROM cupPlayTable WHERE upper(name) = 
upper('Ian') AND missionId = upper('aa1') union all SELECT a.* FROM 
cupPlayTable a, friendTable b WHERE upper(b.player) = upper('Ian') AND 
upper(b.friend) = upper(a.name) AND missionId = upper('aa1')) ORDER BY 
score DESC

This works, although I would appreciate any critique on it.

I also want to add selecting the highest score, and adding it to the 
results assuming it isn't already in there (ie unless it happens your or 
a friends' score is the highest).  Could anyone advise me on how best to 
do that?

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

Re: [sqlite] Query critique

[Virgilio Alexandre Fornazin]

Calling UPPER() for each field/value while querying is a pain, if you can
assume the names are all in uppercase 
(forcing this in your program before passing them to SQlite), you could
speed up things a lot. But you need to 
analyze what´s happening inside SQlite to check if indices are used, etc.

SQlite command line app has a command that does this (does not remember if
it´s this:
".explain ON|OFFTurn output mode suitable for EXPLAIN on or off.\n")

'ANALYZE' SQlite command also performs some kind of prefetching of indexes
and loads results in a internal 
table, can help also to speed up.

Also, try to avoid JOINS like this

'SELECT a.* FROM globalRankingTable a, friendTable b WHERE upper(b.player) =
upper('?') AND upper(b.friend) = 
upper(a.name))'

It´s better to explicit use LEFT JOIN / INNER JOIN always.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: sábado, 10 de julho de 2010 17:39
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query critique

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 01:54 AM, Ian Hardingham wrote:
> And here is my query (written in a script language):

Are you actually getting correct answers from this?

>  upper('?') 

Unless your script language includes a SQL parser, that does not do what you
think it does.  It is seeing if the value matches upper cased question mark.
 It looks like you wanted to supply bound parameters so the question mark
should not be in quotes.

I'd also suggest using a better wrapper or scripting language since you are
providing three bindings but the query is only using one of them.  It should
complain.

> - yes I know I'm an idiot with the upper stuff, a refactor is needed on 
> that - I know all of my "name" fields should really be integers.

Or look into indices and collations.

In any event you should always post your exact actual query so we don't have
to figure out issues like the question marks are intentional or
transcription errors.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw42kcACgkQmOOfHg372QSgxQCfTtgfYFMZ9a4nHtbJn8gI7XEK
7wYAn3ghSgc3wPgl0bFGLq8oA2moTJcs
=aCUo
-END PGP SIGNATURE-
___
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] Query critique

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/09/2010 01:54 AM, Ian Hardingham wrote:
> And here is my query (written in a script language):

Are you actually getting correct answers from this?

>  upper('?') 

Unless your script language includes a SQL parser, that does not do what you
think it does.  It is seeing if the value matches upper cased question mark.
 It looks like you wanted to supply bound parameters so the question mark
should not be in quotes.

I'd also suggest using a better wrapper or scripting language since you are
providing three bindings but the query is only using one of them.  It should
complain.

> - yes I know I'm an idiot with the upper stuff, a refactor is needed on 
> that - I know all of my "name" fields should really be integers.

Or look into indices and collations.

In any event you should always post your exact actual query so we don't have
to figure out issues like the question marks are intentional or
transcription errors.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkw42kcACgkQmOOfHg372QSgxQCfTtgfYFMZ9a4nHtbJn8gI7XEK
7wYAn3ghSgc3wPgl0bFGLq8oA2moTJcs
=aCUo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query critique

[Ian Hardingham]

Hey guys.

I have a query which is very slow, and was wondering if there was any 
advice you guys had on it.

Here are two table definitions:

CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, 
record TEXT);

CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);

And here is my query (written in a script language):

db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE 
upper(name) = upper('?') OR id < ? union all SELECT a.* FROM 
globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') 
AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0, 
%client.username, %globId, %client.username);

The intention of this query is to, basically:

- get the top 10 scores in rankingTable
- get "my" score in rankingTable
- get all of my friends' scores in rankingTable

The query works, but is very slow.  This may just be because it's a 
complex query, but all feedback would be much appreciated.

For your extra information:

- globalRankingTable has several thousand rows
- friendTable has several thousand rows also, but each user will only 
have 0-10 friends (usually on the low part of that scale)
- yes I know I'm an idiot with the upper stuff, a refactor is needed on 
that - I know all of my "name" fields should really be integers.

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

Re: [sqlite] Query critique

[Tim Romano]

If you had the following tables


Table PEOPLERANKINGS(personid INTEGER PRIMARY KEY, personname TEXT, ranking)

Table FRIENDS(personid1, personid2)

and an index on

PEOPLERANKINGS.ranking

and  FRIENDS.personid1,FRIENDS.personid2  is a composite unique primary key


You could  get the top 10 ranked people

select * from PEOPLERANKINGS order by ranking desc limit 10


and get your own ranking and the ranking of your  friends:

select  peoplerankings.* from PEOPLERANKINGS
where personid  IN
  (select personid2 from FRIENDS where personid1 = ?yourId? )

NOTE: befriend yourself by default in the FRIENDS table.


Regards
Tim Romano
Swarthmore PA





On Fri, Jul 9, 2010 at 5:52 AM, Benoit Mortgat  wrote:

> On Fri, Jul 9, 2010 at 11:08, Ian Hardingham  wrote:
> > Hey guys.
> >
> > I have a query which is very slow, and was wondering if there was any
> > advice you guys had on it.
> >
> > Here are two table definitions:
> >
> > CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> > AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> > record TEXT);
> >
> > CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY
> > AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);
> >
> > And here is my query (written in a script language):
> >
> > db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE
> > upper(name) = upper('?') OR id < ? union all SELECT a.* FROM
> > globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?')
> > AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0,
> > %client.username, %globId, %client.username);
> >
>
> Create an index either on player column or friend column in your second
> table.
> CREATE INDEX i_friendTable ON friendTable (player ASC);
>
>
> --
> Benoit Mortgat
> 20, avenue Marcel Paul
> 69200 Vénissieux, France
> +33 6 17 15 41 58
> +33 4 27 11 61 23
> ___
> 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] Query critique

[Benoit Mortgat]

On Fri, Jul 9, 2010 at 11:08, Ian Hardingham  wrote:
> Hey guys.
>
> I have a query which is very slow, and was wondering if there was any
> advice you guys had on it.
>
> Here are two table definitions:
>
> CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> record TEXT);
>
> CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);
>
> And here is my query (written in a script language):
>
> db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE
> upper(name) = upper('?') OR id < ? union all SELECT a.* FROM
> globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?')
> AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0,
> %client.username, %globId, %client.username);
>

Create an index either on player column or friend column in your second table.
CREATE INDEX i_friendTable ON friendTable (player ASC);


-- 
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query critique

[Ian Hardingham]

Hey guys.

I have a query which is very slow, and was wondering if there was any 
advice you guys had on it.

Here are two table definitions:

CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, 
record TEXT);

CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);

And here is my query (written in a script language):

db.query("SELECT * FROM (SELECT DISTINCT * FROM globalRankingTable WHERE 
upper(name) = upper('?') OR id < ? union all SELECT a.* FROM 
globalRankingTable a, friendTable b WHERE upper(b.player) = upper('?') 
AND upper(b.friend) = upper(a.name)) ORDER BY score DESC", 0, 
%client.username, %globId, %client.username);

The intention of this query is to, basically:

- get the top 10 scores in rankingTable
- get "my" score in rankingTable
- get all of my friends' scores in rankingTable

The query works, but is very slow.  This may just be because it's a 
complex query, but all feedback would be much appreciated.

For your extra information:

- globalRankingTable has several thousand rows
- friendTable has several thousand rows also, but each user will only 
have 0-10 friends (usually on the low part of that scale)
- yes I know I'm an idiot with the upper stuff, a refactor is needed on 
that - I know all of my "name" fields should really be integers.

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