Re: [sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
I did try to do something with rank() and row_number() but it slowed the query a lot bringing it up to 26 ms. > On 14 Feb 2019, at 16:33, Shawn Wagner wrote: > > Are you using a new enough version of sqlite (3.25 or better) that you can > use window functions? > >> On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde > >> Hi! >> >> So I have been trying to write a query that needs to be really fast (under >> 1 ms would be ideal) to a very large database (around 20 000 rows and 20 >> columns). The problem is that I need to find the user inside the sorted >> database and retrieve a number of users better and worse than him as to >> make a ranking table and show the user in the middle of it (the ranking >> table consists of 7 players). >> Currently this query takes about 3.5 ms and has a lot of issues. Some of >> them include the fact that I can't retrieve the users that have the same >> amount of points as my user. I also need to know the exact rank of the >> person inside the whole database that's why I need the count (*). It would >> be ideal to have the ranks of every player in the database but I couldn't >> find a way that isn't ridiculously slow. Is there any fast way I can >> retrieve 3 players better or equal to my player with points and worse or >> equal to my player with points so there aren't any duplicates? >> >> select player.user_profile_id, (select count(*) + 1 from event_stats >> player2 where player2.points > player.points order by player2.points desc) >> as 'rank', 'player' as 'stats_group', player.name, player.points from >> event_stats player where player.user_profile_id=202 >> union all >> SELECT * >> FROM ( >> select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', >> stats.name, stats.points from event_stats user >> left join event_stats stats on stats.points > user.points >> where user.user_profile_id=202 order by stats.points ASC limit 3 >> ) q1 >> union all >> SELECT * >> FROM ( >> select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', >> stats.name, stats.fame from event_stats user >> left join event_stats stats on stats.points < user.points >> where user.user_profile_id=202 order by stats.points DESC limit 3 >> ) q2 order by points desc >> >> Thanks for your help! >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tips for index creation.
On 14 Feb 2019, at 6:06pm, Randall Smith wrote:
> One feature that would be useful for the sqlite3 command shell would be a
> mode where the duration of the operation is reported after the operation runs
> (".timing"?).
.timer ON
Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tips for index creation.
One feature that would be useful for the sqlite3 command shell would be a mode
where the duration of the operation is reported after the operation runs
(".timing"?). This would make it easy to compare different approaches for
writing a query, formulating and using different indices, etc. Of course, one
can do this programmatically with a simple fixture, but that's work that may or
may not be simple.
Extra points for breaking out sub-timings for different parts of a complex
query so one can see immediately where the bottlenecks are.
This would be a nice companion to the ".expert" feature and make sqlite3 a
one-stop-shop for query tuning.
Randall.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Global UDF using sqlite3_create_function()?
I think this is a good option. Looks like I can save from registering UDFs everytime a connection is created. Thanks!! On Thu, 14 Feb 2019 18:59:09 +0530 Keith Medcalf wrote > > See also SQLITE_EXTRA_INIT > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Arun - Siara Logics > >(cc) > >Sent: Thursday, 14 February, 2019 06:14 > >To: SQLite mailing list > >Subject: Re: [sqlite] Global UDF using sqlite3_create_function()? > >Importance: High > > > >Thank you!! It works like a charm. Before your reply I added > >FUNCTION macros before sqlite3InsertBuiltinFuncs(). That worked too. > >But I guess using sqlite3_auto_extension() is elegant and I will go > >that way. > > > > > > On Thu, 14 Feb 2019 17:16:01 +0530 Richard Hipp > > wrote > > > On 2/14/19, Arun - Siara Logics (cc) wrote: > > > > > > > > The sqlite3_create_functions require the 'db' parameter to > >define a user > > > > defined function according to the doc: > > > > "The first parameter is the database connection to which the SQL > >function is > > > > to be added. If an application uses more than one database > >connection then > > > > application-defined SQL functions must be added to each database > >connection > > > > separately." > > > > > > > > Is it possible to define a global scalar function so that it > >does not need > > > > to be registered for every function? That way, I can define > >such a function > > > > and ship it with the binary and it can be used with any database > >using the > > > > binary. > > > > > > Use the sqlite3_auto_extension() interface > > > (https://www.sqlite.org/c3ref/auto_extension.html) to cause your > >UDFs > > > to be registered automatically with all new database connections. > > > > > > -- > > > D. Richard Hipp > > > d...@sqlite.org > > > ___ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >users > > > > > > > > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
Yes, sqlite 3.26. On Thu, Feb 14, 2019 at 4:33 PM Shawn Wagner wrote: > Are you using a new enough version of sqlite (3.25 or better) that you can > use window functions? > > On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde wrote: > > > Hi! > > > > So I have been trying to write a query that needs to be really fast > (under > > 1 ms would be ideal) to a very large database (around 20 000 rows and 20 > > columns). The problem is that I need to find the user inside the sorted > > database and retrieve a number of users better and worse than him as to > > make a ranking table and show the user in the middle of it (the ranking > > table consists of 7 players). > > Currently this query takes about 3.5 ms and has a lot of issues. Some of > > them include the fact that I can't retrieve the users that have the same > > amount of points as my user. I also need to know the exact rank of the > > person inside the whole database that's why I need the count (*). It > would > > be ideal to have the ranks of every player in the database but I couldn't > > find a way that isn't ridiculously slow. Is there any fast way I can > > retrieve 3 players better or equal to my player with points and worse or > > equal to my player with points so there aren't any duplicates? > > > > select player.user_profile_id, (select count(*) + 1 from event_stats > > player2 where player2.points > player.points order by player2.points > desc) > > as 'rank', 'player' as 'stats_group', player.name, player.points from > > event_stats player where player.user_profile_id=202 > > union all > > SELECT * > > FROM ( > > select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', > > stats.name, stats.points from event_stats user > > left join event_stats stats on stats.points > user.points > > where user.user_profile_id=202 order by stats.points ASC limit 3 > > ) q1 > > union all > > SELECT * > > FROM ( > > select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', > > stats.name, stats.fame from event_stats user > > left join event_stats stats on stats.points < user.points > > where user.user_profile_id=202 order by stats.points DESC limit 3 > > ) q2 order by points desc > > > > Thanks for your help! > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
Are you using a new enough version of sqlite (3.25 or better) that you can use window functions? On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde Hi! > > So I have been trying to write a query that needs to be really fast (under > 1 ms would be ideal) to a very large database (around 20 000 rows and 20 > columns). The problem is that I need to find the user inside the sorted > database and retrieve a number of users better and worse than him as to > make a ranking table and show the user in the middle of it (the ranking > table consists of 7 players). > Currently this query takes about 3.5 ms and has a lot of issues. Some of > them include the fact that I can't retrieve the users that have the same > amount of points as my user. I also need to know the exact rank of the > person inside the whole database that's why I need the count (*). It would > be ideal to have the ranks of every player in the database but I couldn't > find a way that isn't ridiculously slow. Is there any fast way I can > retrieve 3 players better or equal to my player with points and worse or > equal to my player with points so there aren't any duplicates? > > select player.user_profile_id, (select count(*) + 1 from event_stats > player2 where player2.points > player.points order by player2.points desc) > as 'rank', 'player' as 'stats_group', player.name, player.points from > event_stats player where player.user_profile_id=202 > union all > SELECT * > FROM ( > select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', > stats.name, stats.points from event_stats user > left join event_stats stats on stats.points > user.points > where user.user_profile_id=202 order by stats.points ASC limit 3 > ) q1 > union all > SELECT * > FROM ( > select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', > stats.name, stats.fame from event_stats user > left join event_stats stats on stats.points < user.points > where user.user_profile_id=202 order by stats.points DESC limit 3 > ) q2 order by points desc > > Thanks for your help! > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
Hi! So I have been trying to write a query that needs to be really fast (under 1 ms would be ideal) to a very large database (around 20 000 rows and 20 columns). The problem is that I need to find the user inside the sorted database and retrieve a number of users better and worse than him as to make a ranking table and show the user in the middle of it (the ranking table consists of 7 players). Currently this query takes about 3.5 ms and has a lot of issues. Some of them include the fact that I can't retrieve the users that have the same amount of points as my user. I also need to know the exact rank of the person inside the whole database that's why I need the count (*). It would be ideal to have the ranks of every player in the database but I couldn't find a way that isn't ridiculously slow. Is there any fast way I can retrieve 3 players better or equal to my player with points and worse or equal to my player with points so there aren't any duplicates? select player.user_profile_id, (select count(*) + 1 from event_stats player2 where player2.points > player.points order by player2.points desc) as 'rank', 'player' as 'stats_group', player.name, player.points from event_stats player where player.user_profile_id=202 union all SELECT * FROM ( select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', stats.name, stats.points from event_stats user left join event_stats stats on stats.points > user.points where user.user_profile_id=202 order by stats.points ASC limit 3 ) q1 union all SELECT * FROM ( select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', stats.name, stats.fame from event_stats user left join event_stats stats on stats.points < user.points where user.user_profile_id=202 order by stats.points DESC limit 3 ) q2 order by points desc Thanks for your help! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Global UDF using sqlite3_create_function()?
See also SQLITE_EXTRA_INIT --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Arun - Siara Logics >(cc) >Sent: Thursday, 14 February, 2019 06:14 >To: SQLite mailing list >Subject: Re: [sqlite] Global UDF using sqlite3_create_function()? >Importance: High > >Thank you!! It works like a charm. Before your reply I added >FUNCTION macros before sqlite3InsertBuiltinFuncs(). That worked too. >But I guess using sqlite3_auto_extension() is elegant and I will go >that way. > > > On Thu, 14 Feb 2019 17:16:01 +0530 Richard Hipp > wrote > > On 2/14/19, Arun - Siara Logics (cc) wrote: > > > > > > The sqlite3_create_functions require the 'db' parameter to >define a user > > > defined function according to the doc: > > > "The first parameter is the database connection to which the SQL >function is > > > to be added. If an application uses more than one database >connection then > > > application-defined SQL functions must be added to each database >connection > > > separately." > > > > > > Is it possible to define a global scalar function so that it >does not need > > > to be registered for every function? That way, I can define >such a function > > > and ship it with the binary and it can be used with any database >using the > > > binary. > > > > Use the sqlite3_auto_extension() interface > > (https://www.sqlite.org/c3ref/auto_extension.html) to cause your >UDFs > > to be registered automatically with all new database connections. > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users > > > > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Global UDF using sqlite3_create_function()?
Thank you!! It works like a charm. Before your reply I added FUNCTION macros before sqlite3InsertBuiltinFuncs(). That worked too. But I guess using sqlite3_auto_extension() is elegant and I will go that way. On Thu, 14 Feb 2019 17:16:01 +0530 Richard Hipp wrote > On 2/14/19, Arun - Siara Logics (cc) wrote: > > > > The sqlite3_create_functions require the 'db' parameter to define a user > > defined function according to the doc: > > "The first parameter is the database connection to which the SQL function > > is > > to be added. If an application uses more than one database connection then > > application-defined SQL functions must be added to each database connection > > separately." > > > > Is it possible to define a global scalar function so that it does not need > > to be registered for every function? That way, I can define such a > > function > > and ship it with the binary and it can be used with any database using the > > binary. > > Use the sqlite3_auto_extension() interface > (https://www.sqlite.org/c3ref/auto_extension.html) to cause your UDFs > to be registered automatically with all new database connections. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Global UDF using sqlite3_create_function()?
On 2/14/19, Arun - Siara Logics (cc) wrote: > > The sqlite3_create_functions require the 'db' parameter to define a user > defined function according to the doc: > "The first parameter is the database connection to which the SQL function is > to be added. If an application uses more than one database connection then > application-defined SQL functions must be added to each database connection > separately." > > Is it possible to define a global scalar function so that it does not need > to be registered for every function? That way, I can define such a function > and ship it with the binary and it can be used with any database using the > binary. Use the sqlite3_auto_extension() interface (https://www.sqlite.org/c3ref/auto_extension.html) to cause your UDFs to be registered automatically with all new database connections. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Window functions in System.Data.Sqlite
Hello, I am sorry in advance for this post but I am really looking forward having the window functions in System.Data.Sqlite. The expected release date went from December 2018 to February 2019 in the news page. Do you have any more info when will the new version of System.Data.Sqlite be released ? Thank you. Alex ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Global UDF using sqlite3_create_function()?
Hi, The sqlite3_create_functions require the 'db' parameter to define a user defined function according to the doc: "The first parameter is the database connection to which the SQL function is to be added. If an application uses more than one database connection then application-defined SQL functions must be added to each database connection separately." Is it possible to define a global scalar function so that it does not need to be registered for every function? That way, I can define such a function and ship it with the binary and it can be used with any database using the binary. I see sqlite3InsertBuiltinFuncs() in sqlite3.c. Can it be used for this purpose? Regards Arun ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
