Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Thanks for the reply Roger and I have read the section you mentioned, very informative. I'm not trying to compare the 2 products, but rather trying to find out if my app will work with SQLite. I don't necessarily require a server. My app can work as a web app or web service where clients hit this service and my app will collect the necessary info and write to the DB. So in reality, the web app will be doing all the writing and reading to SQLite. I'm trying to avoid using a traditional database server if SQLite can handle this. I wanted to check here first for guidance to see if SQLite can do this. Thanks, Robel -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns Sent: Thursday, June 11, 2009 1:31 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app. -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Robel Girma wrote: > I'm trying to choose the most efficient db for this application and my main > criteria is response time. Will SQLite do this more efficiently than SQL > Server. I'm planning to allocate upto 1GB memory. SQLite doesn't operate as a server so there isn't a straight forward comparison. I suggest reading http://www.sqlite.org/whentouse.html which gives good advice. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkowlowACgkQmOOfHg372QQ0NQCbBwYmOVAQOAvhRwM70+cpioZz 94MAoI62fP0VKlFN/9K2rjFp5Bv49oZ4 =okkY -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] Advice on which to use (SQLite or SQL Server) for the following app.
* Robel Girma: > Example, 5000 users connect to our server every 10 seconds and each > time they connect, I need to update a table with their IP and > Last_connect_time. That's 500 commits per second, right? If you need durability, you can get these numbers only with special hardware. SQL Server might offer better performance, assuming it can group commits. However, it might be easier to to just keep the data in memory and log the changes to disk. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Robel Girma wrote: > but rather trying to > find out if my app will work with SQLite. SQLite will definitely work and at the very least you will it useful during (rapid) development and demos. Quite simply SQLite will get you results far quicker than server based approaches which have to marshal the data across the network. > in reality, the web app will be doing all the writing and reading to SQLite. It depends very strongly on how the app is structured and in particular if there are a few persistent connections to the SQLite database, or if each request involves a separate connection to the database. If you have lots of connections then there will be contention. If the work done during contention is quick and simple then you are fine. If it is long running then you will benefit from a server based approach. But when you have commits then disk access is serialized and you will have performance limitations no matter what the database server or SQLite. (That is the point Florian is making.) > I'm trying to avoid using a traditional database server if SQLite can handle > this. I wanted to check here first for guidance to see if SQLite can do > this. I'd suggest just going ahead and doing a mock implementation and see what you get. If you have a URL that does representative work then you can use tools like ab (the Apache Benchmark tool that comes with Apache but works with any server) to run the number of concurrent requests you specify. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkowsQoACgkQmOOfHg372QQsRQCeIHiikyM8k/h/oTeitLCPzHpA IaYAoM9dEjvqD2GZWIkiWZCnni2H28GH =LzSr -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow select from a single table when using a view
Hi all, I have a table T with a few million rows. It has a column C with only a handful of distinct values for grouping the data. When a user wants to access the data my application reads it from a temporary view: CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); where (C=1 OR C=2) will reflect the permissions of the user. There is also an index for C: CREATE INDEX idx_C ON T(C); I have a problem with performance when using the view for simple selects. The following query returns the result immediately: SELECT min(C) from T; However the same query on the view takes a very long time: SELECT min(C) from T_view; It would seem that the index is used but there are some differences: EXPLAIN QUERY PLAN SELECT min(C) from T; returns 0|0|TABLE T WITH INDEX idx_C ORDER BY but query EXPLAIN QUERY PLAN SELECT min(c) from T_view; returns 0|0|TABLE T WITH INDEX idx_C Is there a way to speed up the queries or maybe an alternative method of filtering results based on C? -- Antti Nietosvaara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting readline to work on Solaris
I've built various versions of SQLite on Solaris 10 (u7 currently, with the companion CD). Things keep breaking so I have to change how I build it, but recently I've been building it by: * Fetching the amalgamation * configuring with ./configure --prefix=/home/tfb/packages/sqlite-3.6.14.2 \ --enable-static=no \ --enable-readline=yes \ LIBS="-L/opt/sfw/lib -R/opt/sfw/lib -lreadline -lcurses" \ INCLUDES="-I/opt/sfw/include" * gmake; gmake install This produces a binary which works fine and it is linked with readline: $ ldd ~/packages/sqlite-3.6.14.2/bin/sqlite3 libsqlite3.so.0 => /home/tfb/packages/sqlite-3.6.14.2/ lib/libsqlite3.so.0 libreadline.so.4 => /opt/sfw/lib/libreadline.so.4 libcurses.so.1 =>/lib/libcurses.so.1 libc.so.1 => /lib/libc.so.1 libgcc_s.so.1 => /usr/sfw/lib/libgcc_s.so.1 libm.so.2 => /lib/libm.so.2 But history etc does not work. In significantly older versions history *did* work (but building was much different then). Has anyone had any better success getting this to work than me? I suspect my problem is mostly "not running on a Linux distribution sufficiently similar to ", but that's not really an option for me unfortunately. Thanks --tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
On 11 Jun 2009, at 8:23am, Roger Binns wrote: > It depends very strongly on how the app is structured and in > particular > if there are a few persistent connections to the SQLite database, or > if > each request involves a separate connection to the database. If you > have lots of connections then there will be contention. 500 connections a second, each from a different computer. If the OP handles each one with a separate run of his/her application, that's 500 connections to the database a second, each updating one record in one table. > If the work done during contention is quick and simple then you are > fine. If it is long running then you will benefit from a server based > approach. But when you have commits then disk access is serialized > and > you will have performance limitations no matter what the database > server or SQLite. (That is the point Florian is making.) As far as I can tell, with correct programming each query would be one connection for all the data the query would want. So the OP's objective is /probably/ achievable with SQLite but I'd want to prototype it to be sure. Sorry, Robel, but we can only guess. Try it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote: > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); > where (C=1 OR C=2) will reflect the permissions of the user. > > There is also an index for C: > CREATE INDEX idx_C ON T(C); > > I have a problem with performance when using the view for simple > selects. > The following query returns the result immediately: > SELECT min(C) from T; > > However the same query on the view takes a very long time: > SELECT min(C) from T_view; You didn't create an index on T_view ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to get WHERE condition in sqlite?
Hello, Eample: select * from table1 where id=2 and name='Mitja'; I give from vdbe: - sqlite3_column_count(pVM); - sqlite3_column_name(pVM, i); but how can I get where condition? It is also parsed and interpreted in some structures? I need this datas for another interface which dont understand SQL syntax. Br, Branko ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
On Thursday 11 June 2009 11:50:56 Simon Slavin wrote: > On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote: > > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); > > where (C=1 OR C=2) will reflect the permissions of the user. > > > > There is also an index for C: > > CREATE INDEX idx_C ON T(C); > > > > I have a problem with performance when using the view for simple > > selects. > > The following query returns the result immediately: > > SELECT min(C) from T; > > > > However the same query on the view takes a very long time: > > SELECT min(C) from T_view; > > You didn't create an index on T_view ? No, it seems you cannot create an index on views. Trying to do so resulted: "SQL error: views may not be indexed" I just noticed that if there I use only one C filter, for example: CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE C=1; the queries will be just as fast as with using T directly. Also, explain query plan returns the string ending with ORDER BY, just like it does when selecting from T. With this single filter my query took about 8 ms. With two filters (C=1 OR C=2) the time went to over 6 seconds. With (C=1 OR C=2 OR C=3) about 13 seconds. -- Antti Nietosvaara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Aqlite is not the DB for your application. You need a server like PostgreSQL or Oracle. Robel Girma wrote: > Hello, > > I am in need for a database to hold a couple of tables with max 10,000 rows > each that I will update frequently and query frequently. > > Example, 5000 users connect to our server every 10 seconds and each time > they connect, I need to update a table with their IP and Last_connect_time. > > Also, every 10 seconds or so, a couple of hundred users will query this > table with simple select statements (Select ip from table1 where > last_connect_time is greater than 20seconds). > > > > I'm trying to choose the most efficient db for this application and my main > criteria is response time. Will SQLite do this more efficiently than SQL > Server. I'm planning to allocate upto 1GB memory. > > I appreciate any input we can give on this. > > Robel > > ___ > 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] Getting readline to work on Solaris
On 11 Jun 2009, at 09:42, Tim Bradshaw wrote: > > * configuring with > ./configure --prefix=/home/tfb/packages/sqlite-3.6.14.2 \ >--enable-static=no \ >--enable-readline=yes \ >LIBS="-L/opt/sfw/lib -R/opt/sfw/lib -lreadline -lcurses" \ >INCLUDES="-I/opt/sfw/include" > * gmake; gmake install I lost patience with this and, for what it's worth, the following works: $ ./configure --prefix=/home/tfb/packages/sqlite-3.6.14.2 \ --enable-static=no \ --enable-readline=yes \ LIBS="-L/opt/sfw/lib -R/opt/sfw/lib -lreadline -lcurses" \ CPPFLAGS="-I/opt/sfw/include" (INCLUDES is not right, it has to be CPPFLAGS). Then edit the Makefile and add a -DHAVE_READLINE=1 to the appropriate place. Then it builds with readline, and works. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
I think you should try to rewrite condition to exclude OR like this: WHERE C >= 1 AND C <= 2. You can even do like this: WHERE C >= 1 AND C <= 3 AND (C = 1 OR C = 3). I think it will be faster than just ORed conditions alone. Pavel On Thu, Jun 11, 2009 at 5:19 AM, Antti Nietosvaarawrote: > On Thursday 11 June 2009 11:50:56 Simon Slavin wrote: >> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote: >> > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); >> > where (C=1 OR C=2) will reflect the permissions of the user. >> > >> > There is also an index for C: >> > CREATE INDEX idx_C ON T(C); >> > >> > I have a problem with performance when using the view for simple >> > selects. >> > The following query returns the result immediately: >> > SELECT min(C) from T; >> > >> > However the same query on the view takes a very long time: >> > SELECT min(C) from T_view; >> >> You didn't create an index on T_view ? > > No, it seems you cannot create an index on views. Trying to do so resulted: > "SQL error: views may not be indexed" > > I just noticed that if there I use only one C filter, for example: > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE C=1; > the queries will be just as fast as with using T directly. Also, explain query > plan returns the string ending with ORDER BY, just like it does when selecting > from T. > With this single filter my query took about 8 ms. With two filters (C=1 OR > C=2) > the time went to over 6 seconds. With (C=1 OR C=2 OR C=3) about 13 seconds. > > -- > Antti Nietosvaara > > ___ > 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] How to get WHERE condition in sqlite?
Branko Zebec wrote: > Eample: select * from table1 where id=2 and name='Mitja'; > > I give from vdbe: > > - sqlite3_column_count(pVM); > > - sqlite3_column_name(pVM, i); > > but how can I get where condition? You yourself supplied the SQL statement, so you already know the condition. > It is also parsed and interpreted > in some structures? Well, it's embedded in the code of the virtual machine. Run your statement prefixed with EXPLAIN to see what that looks like. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
Antti Nietosvaara wrote: > I have a table T with a few million rows. It has a column C with only > a handful of distinct values for grouping the data. When a user wants > to access the data my application reads it from a temporary view: > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); > where (C=1 OR C=2) will reflect the permissions of the user. > > There is also an index for C: > CREATE INDEX idx_C ON T(C); You may find that an index on a column with only a few distinct values actually hurts performance. Something as simple as select * from T where C=1; may take longer with an index. You see, it takes O(N) time to run this query without an index, and O(M (logN)^2) time to run it with index, where N is the total number of records in the table, and M is the number of records satisfying the condition. So the index has a clear benefit when M is much smaller than N, but is obviously detrimental when M is close to N. The break-even point occurs approximately at M equal to 10% of N. > I have a problem with performance when using the view for simple > selects. The following query returns the result immediately: > SELECT min(C) from T; > > However the same query on the view takes a very long time: > SELECT min(C) from T_view; Because this query is no longer a simple select. It is translated internally into select min(C) from (SELECT * FROM T WHERE (C=1 OR C=2)); Index is used to satisfy the WHERE condition - but that's still a substantial portion of all records in T, which then have to be scanned linearly. > Is there a way to speed up the queries Well, if you are talking of this particular query, you can do something like this: select (case when exists (select 1 from T where C=1) then 1 when exists (select 1 from T where C=2) then 2 else null end); This should run fast. However, I kind of doubt that "SELECT min(C) from T_view;" is a typical query in your application. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
On Thursday 11 June 2009 14:54:04 Igor Tandetnik wrote: > Because this query is no longer a simple select. It is translated > internally into > > select min(C) from > (SELECT * FROM T WHERE (C=1 OR C=2)); Ah, this would indeed explain the slowdown. I was hoping views would translate into the "where" part of the query, like: SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2); I may need to find out another way to filter the rows from users. -- Antti Nietosvaara Turun Turvatekniikka Oy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
Antti Nietosvaara wrote: > On Thursday 11 June 2009 14:54:04 Igor Tandetnik wrote: >> Because this query is no longer a simple select. It is translated >> internally into >> >> select min(C) from >> (SELECT * FROM T WHERE (C=1 OR C=2)); > > Ah, this would indeed explain the slowdown. I was hoping views would > translate into the "where" part of the query, like: > SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2); I predict this last query wouldn't run any faster. Try it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
On Thursday 11 June 2009 16:30:12 Igor Tandetnik wrote: > > Ah, this would indeed explain the slowdown. I was hoping views would > > translate into the "where" part of the query, like: > > SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2); > > I predict this last query wouldn't run any faster. Try it. You are correct. It is way too slow. -- Antti Nietosvaara Turun Turvatekniikka Oy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Thanks all for your input, very helpful. And yes, there will be 500 separate connections to the db per seconds, each updating 1 record. I've read about setting PRAGMA synchronous=OFF to cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. "But if you lose power in the middle of a transaction, your database file might go corrupt"==> I can live with this risk if it makes an huge improvement with the possible contention issue I'm facing. Any input with this setting you can provide will be greatly appreciated as always. Robel -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, June 11, 2009 4:49 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app. On 11 Jun 2009, at 8:23am, Roger Binns wrote: > It depends very strongly on how the app is structured and in > particular > if there are a few persistent connections to the SQLite database, or > if > each request involves a separate connection to the database. If you > have lots of connections then there will be contention. 500 connections a second, each from a different computer. If the OP handles each one with a separate run of his/her application, that's 500 connections to the database a second, each updating one record in one table. > If the work done during contention is quick and simple then you are > fine. If it is long running then you will benefit from a server based > approach. But when you have commits then disk access is serialized > and > you will have performance limitations no matter what the database > server or SQLite. (That is the point Florian is making.) As far as I can tell, with correct programming each query would be one connection for all the data the query would want. So the OP's objective is /probably/ achievable with SQLite but I'd want to prototype it to be sure. Sorry, Robel, but we can only guess. Try it. 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] Advice on which to use (SQLite or SQL Server) for the following app.
I bet "synchronous"ness will not be your only bottleneck. Opening connection, preparing statement and closing connection will take in total much longer than executing statement itself. So that doing all these operations 500 times per second will not be possible I think. If you keep pool of connections along with already prepared statements then your application will have chances for survival in such contention environment. And yes, your application will not have any chances without "synchronous = OFF". Without it you're able to do only 10 to 20 transactions per second. Pavel On Thu, Jun 11, 2009 at 9:53 AM, Robel Girmawrote: > Thanks all for your input, very helpful. And yes, there will be 500 separate > connections to the db per seconds, each updating 1 record. I've read about > setting PRAGMA synchronous=OFF to cause SQLite to not wait on data to reach > the disk surface, which will make write operations appear to be much faster. > "But if you lose power in the middle of a transaction, your database file > might go corrupt"==> I can live with this risk if it makes an huge > improvement with the possible contention issue I'm facing. Any input with > this setting you can provide will be greatly appreciated as always. > Robel > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Thursday, June 11, 2009 4:49 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the > following app. > > > On 11 Jun 2009, at 8:23am, Roger Binns wrote: > >> It depends very strongly on how the app is structured and in >> particular >> if there are a few persistent connections to the SQLite database, or >> if >> each request involves a separate connection to the database. If you >> have lots of connections then there will be contention. > > 500 connections a second, each from a different computer. If the OP > handles each one with a separate run of his/her application, that's > 500 connections to the database a second, each updating one record in > one table. > >> If the work done during contention is quick and simple then you are >> fine. If it is long running then you will benefit from a server based >> approach. But when you have commits then disk access is serialized >> and >> you will have performance limitations no matter what the database >> server or SQLite. (That is the point Florian is making.) > > As far as I can tell, with correct programming each query would be one > connection for all the data the query would want. So the OP's > objective is /probably/ achievable with SQLite but I'd want to > prototype it to be sure. > > Sorry, Robel, but we can only guess. Try it. > > 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] Slow select from a single table when using a view
If you only have a handful of values for C and are already going to the trouble of creating separate views for each C, you could partition your data into separate tables for each value of C and maybe create another table containing the list of values of C and maybe the number of items in each C table (if that would be useful), or other attributes common to each value of C. Whether that makes sense depends on what else you're doing with the views. You might have to repeat queries across a set of tables and then do some work in your application to collate the results. That could be a big deal or not, depending on what you're doing. For this min example, you'd only have to query the master table. Jim On 6/11/09, Antti Nietosvaarawrote: > On Thursday 11 June 2009 16:30:12 Igor Tandetnik wrote: >> > Ah, this would indeed explain the slowdown. I was hoping views would >> > translate into the "where" part of the query, like: >> > SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2); >> >> I predict this last query wouldn't run any faster. Try it. > > You are correct. It is way too slow. > > -- > Antti Nietosvaara > Turun Turvatekniikka Oy > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Here's what I'd try: 1. Write a small server that accepts connections and writes to the SQLite database using prepared statements. If you need require 500 transaction per second, it's simply not possible with rotating media. So the solution is to either turn off synchronous, which is dangerous, or use group commit, ie, batching your external transactions into larger database commits. It's possible you might lose a batch of transactions from a hardware failure, but at least your database will be usable afterwards. With synchronous=off, your database may be corrupted. You might object to group commit because when you "ack" your external connection, you want to ensure you have done a commit before closing the connection. However, with synchronous=off, your commit is only in memory, so it's basically the same as group commit. Using group commit, it should be easy to do 500 SQL external "transactions" per second. 2. If this is still too slow, it's likely because of establishing the TCP connection. If possible, you could switch to UDP, which has a much lower overhead. You'd have to be able to live with losing or repeating data points sometimes, but maybe that would be easy to manage in your mini server above by ignoring repeated data points or using averaging to fill in missing data points. Jim On 6/11/09, Pavel Ivanovwrote: > I bet "synchronous"ness will not be your only bottleneck. Opening > connection, preparing statement and closing connection will take in > total much longer than executing statement itself. So that doing all > these operations 500 times per second will not be possible I think. If > you keep pool of connections along with already prepared statements > then your application will have chances for survival in such > contention environment. > And yes, your application will not have any chances without > "synchronous = OFF". Without it you're able to do only 10 to 20 > transactions per second. > > Pavel > > On Thu, Jun 11, 2009 at 9:53 AM, Robel Girma wrote: >> Thanks all for your input, very helpful. And yes, there will be 500 >> separate >> connections to the db per seconds, each updating 1 record. I've read about >> setting PRAGMA synchronous=OFF to cause SQLite to not wait on data to >> reach >> the disk surface, which will make write operations appear to be much >> faster. >> "But if you lose power in the middle of a transaction, your database file >> might go corrupt"==> I can live with this risk if it makes an huge >> improvement with the possible contention issue I'm facing. Any input with >> this setting you can provide will be greatly appreciated as always. >> Robel >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin >> Sent: Thursday, June 11, 2009 4:49 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for >> the >> following app. >> >> >> On 11 Jun 2009, at 8:23am, Roger Binns wrote: >> >>> It depends very strongly on how the app is structured and in >>> particular >>> if there are a few persistent connections to the SQLite database, or >>> if >>> each request involves a separate connection to the database. If you >>> have lots of connections then there will be contention. >> >> 500 connections a second, each from a different computer. If the OP >> handles each one with a separate run of his/her application, that's >> 500 connections to the database a second, each updating one record in >> one table. >> >>> If the work done during contention is quick and simple then you are >>> fine. If it is long running then you will benefit from a server based >>> approach. But when you have commits then disk access is serialized >>> and >>> you will have performance limitations no matter what the database >>> server or SQLite. (That is the point Florian is making.) >> >> As far as I can tell, with correct programming each query would be one >> connection for all the data the query would want. So the OP's >> objective is /probably/ achievable with SQLite but I'd want to >> prototype it to be sure. >> >> Sorry, Robel, but we can only guess. Try it. >> >> 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 > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
I should have mentioned, if it were me, I'd write the mini server first as a single process in a loop, and make it as fast as possible. If you try to do db updates with multiple processes, you'll have concurrency issues. It might make sense to use multiple processes if you also have lots of queries, and have only 1 process (or thread) writing, while a pool of processes handles queries. Not sure how well SQLite handles this situation, but since you are doing group commits, it will greatly decrease your write load and potential concurrency issues. Jim On 6/11/09, Jim Wilcoxsonwrote: > Here's what I'd try: ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Jim Wilcoxson wrote: Here's what I'd try: 1. Write a small server that accepts connections and writes to the SQLite database using prepared statements. If you need require 500 transaction per second, it's simply not possible with rotating media. I am a late comer to this discussion, so this might have already been purposed... Ever consider having all the updates are done on none rotating media (SSD) and then having 1 process that reads in blocks of the data and puts it into it's final home? Purge the SSD SQLite db simply by rolling to a new one from time to time and delete the old once once all the connections have moved on to the new SQLite db. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
SSD's usually have poor write performance, because to do a write, they have to use read, erase, write sequences across large blocks like 64K. Most of the SSD benchmarks that quote good write performance are for sequential write performance. If you skip all over the disk doing small writes, like a database does, I suspect you'll see pretty bad performance with most SSD's. In most of the SSD benchmarks I've seen, random write performance is worse than rotating media. Actually, most of the SSD benchmarks I've seen completely skip over this point, perhaps not even testing random write performance, but only sequential writes. Using a separate copy of the database on SSD for queries would probably work well, assuming the database doesn't fit into RAM. If the db does fit in RAM, SSD won't buy you anything. Companies are putting all kinds of smarts into SSD's to try to minimize the effects of the read, erase, write cycle, usually by some form of caching, but then you are also playing with losing the transaction guarantees of a commit. Can't really have it both ways. Jim On 6/11/09, Sam Carletonwrote: > Jim Wilcoxson wrote: >> Here's what I'd try: >> >> 1. Write a small server that accepts connections and writes to the >> SQLite database using prepared statements. If you need require 500 >> transaction per second, it's simply not possible with rotating media. > > I am a late comer to this discussion, so this might have already been > purposed... > > Ever consider having all the updates are done on none rotating media > (SSD) and then having 1 process that reads in blocks of the data and > puts it into it's final home? Purge the SSD SQLite db simply by rolling > to a new one from time to time and delete the old once once all the > connections have moved on to the new SQLite db. > > Sam > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Hey, if anybody has an SSD laying around, it would be interesting to run that commit test program I posted a while back to see what kind of transaction rates are possible. Although, verifying whether the SSD is actually doing the commits or just saying it is would be very difficult. With rotating media, you at least have an upper bound (120/sec for 7200rpm, 166/sec for 10Krpm, 250/sec for 15Krpm) and if you go outside that, you know it's a lie. Not sure how you could verify that commits/syncs with an SSD are actually working other than repeatedly pulling the plug and seeing if the db survives intact. Jim On 6/11/09, Jim Wilcoxsonwrote: > SSD's usually have poor write performance, because to do a write, they > have to use read, erase, write sequences across large blocks like 64K. -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Thank you all for the wonderful advices. I guess the only thing left now is to dive into writing the app and stress test to find out :) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Wilcoxson Sent: Thursday, June 11, 2009 11:19 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app. SSD's usually have poor write performance, because to do a write, they have to use read, erase, write sequences across large blocks like 64K. Most of the SSD benchmarks that quote good write performance are for sequential write performance. If you skip all over the disk doing small writes, like a database does, I suspect you'll see pretty bad performance with most SSD's. In most of the SSD benchmarks I've seen, random write performance is worse than rotating media. Actually, most of the SSD benchmarks I've seen completely skip over this point, perhaps not even testing random write performance, but only sequential writes. Using a separate copy of the database on SSD for queries would probably work well, assuming the database doesn't fit into RAM. If the db does fit in RAM, SSD won't buy you anything. Companies are putting all kinds of smarts into SSD's to try to minimize the effects of the read, erase, write cycle, usually by some form of caching, but then you are also playing with losing the transaction guarantees of a commit. Can't really have it both ways. Jim On 6/11/09, Sam Carletonwrote: > Jim Wilcoxson wrote: >> Here's what I'd try: >> >> 1. Write a small server that accepts connections and writes to the >> SQLite database using prepared statements. If you need require 500 >> transaction per second, it's simply not possible with rotating media. > > I am a late comer to this discussion, so this might have already been > purposed... > > Ever consider having all the updates are done on none rotating media > (SSD) and then having 1 process that reads in blocks of the data and > puts it into it's final home? Purge the SSD SQLite db simply by rolling > to a new one from time to time and delete the old once once all the > connections have moved on to the new SQLite db. > > Sam > -- Software first. Software lasts! ___ 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] Advice on which to use (SQLite or SQL Server) for the following app.
Jim, I am about to have my first one here in a few hours. Can you email me the program directly? Sam Jim Wilcoxson wrote: Hey, if anybody has an SSD laying around, it would be interesting to run that commit test program I posted a while back to see what kind of transaction rates are possible. Although, verifying whether the SSD is actually doing the commits or just saying it is would be very difficult. With rotating media, you at least have an upper bound (120/sec for 7200rpm, 166/sec for 10Krpm, 250/sec for 15Krpm) and if you go outside that, you know it's a lie. Not sure how you could verify that commits/syncs with an SSD are actually working other than repeatedly pulling the plug and seeing if the db survives intact. Jim On 6/11/09, Jim Wilcoxsonwrote: SSD's usually have poor write performance, because to do a write, they have to use read, erase, write sequences across large blocks like 64K. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
On Thu, Jun 11, 2009 at 1:46 AM, Florian Weimerwrote: > That's 500 commits per second, right? If you need durability, you can > get these numbers only with special hardware. > Not really, you don't need special hardware (if you don't use SQLite). The use case that Robel described requires best row locks. In a RDBMS server, nobody stops you to commit several transactions in the same time, using the same disk spin. With SQLite you cannot, because SQLite use database lock, so all transactions are serialized. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from a single table when using a view
On 11 Jun 2009, at 10:19am, Antti Nietosvaara wrote: > On Thursday 11 June 2009 11:50:56 Simon Slavin wrote: >> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote: >>> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2); >>> where (C=1 OR C=2) will reflect the permissions of the user. >>> >>> There is also an index for C: >>> CREATE INDEX idx_C ON T(C); >>> >>> I have a problem with performance when using the view for simple >>> selects. >>> The following query returns the result immediately: >>> SELECT min(C) from T; >>> >>> However the same query on the view takes a very long time: >>> SELECT min(C) from T_view; >> >> You didn't create an index on T_view ? > > No, it seems you cannot create an index on views. Trying to do so > resulted: > "SQL error: views may not be indexed" I apologise: of course you can't create an index on views. You're quite right. I was thinking you were reading from a temporary table, not a temporary view. So we now wonder if a view is really necessary. You can create a temporary table and read the records from the view into that. Or even ignore that stage entirely and include your restriction on 'C' in your SELECT command. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
On Jun 11, 2009, at 4:53 PM, Sam Carleton wrote: > I am a late comer to this discussion, so this might have already > been purposed... Additionally, if this was not mentioned already, you can partition your database across multiple physical files through the magic of 'attach database' or something and load balance across those. http://en.wikipedia.org/wiki/Partition_(database) CHeers, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPDATE with inline view/ derived table
Hi, I need some help getting this UPDATE to work with sqlite 3.3.8: UPDATE fud28_read SET user_id=2, msg_id=t.last_post_id, last_view=1244710953 FROM (SELECT id, last_post_id FROM fud28_thread WHERE forum_id=4 AND last_post_date > 0) t WHERE user_id=2 AND thread_id=t.id Error: near "FROM" - syntax error: HY000 Does sqlite support inline views/ derived tables within UPDATE statements? Any suggestions on how to get it to work? Best regards. Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE with inline view/ derived table
According to this http://www.sqlite.org/lang_update.html you have invalid syntax. I believe you can achieve the same by this (assuming that id is unique in fud28_thread): UPDATE fud28_read SET user_id=2, last_view=1244710953, msg_id=(SELECT last_post_id FROM fud28_thread WHERE id = fud28_read.thread_id) WHERE user_id=2 AND thread_id in (SELECT id FROM fud28_thread WHERE forum_id=4 AND last_post_date > 0) Pavel On Thu, Jun 11, 2009 at 2:17 PM, Frank Naudewrote: > Hi, > > I need some help getting this UPDATE to work with sqlite 3.3.8: > > UPDATE fud28_read > SET user_id=2, msg_id=t.last_post_id, last_view=1244710953 > FROM (SELECT id, last_post_id FROM fud28_thread WHERE forum_id=4 AND > last_post_date > 0) t > WHERE user_id=2 AND thread_id=t.id > > Error: near "FROM" - syntax error: HY000 > > Does sqlite support inline views/ derived tables within UPDATE > statements? Any suggestions on how to get it to work? > > Best regards. > > Frank > ___ > 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] UPDATE with inline view/ derived table
Hi Pavel, On Thu, Jun 11, 2009 at 8:28 PM, Pavel Ivanovwrote: > According to this http://www.sqlite.org/lang_update.html you have > invalid syntax. > I believe you can achieve the same by this (assuming that id is unique > in fud28_thread): > > UPDATE fud28_read > SET user_id=2, last_view=1244710953, > msg_id=(SELECT last_post_id FROM fud28_thread > WHERE id = fud28_read.thread_id) > WHERE user_id=2 > AND thread_id in (SELECT id FROM fud28_thread > WHERE forum_id=4 AND last_post_date > 0) Thanks for the quick reply. The thread id is unique and your solution works like a charm. BTW: Are there any plans to support the original syntax at some stage (it's working with MySQL, PostgreSQL and Oracle)? Best regards. Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
Yes, good point. If you partition the database into multiple databases, you will have to place each on its own physical disk drive to increase transaction rates. If your base transaction rate with one drive is T, with N drives it should be N*T; 4 drives gives you 4x the transaction rate, etc. Each of the drives has to be completely independent - no filesystems crossing drives. If you partition into multiple databases and keep them on the same drive, your transaction rate will likely go down, because now you are introducing seeks back and forth between the two databases as you commit. I think someone mentioned using the journal_data option with ext3 to increase performance. In theory, it possibly could, but on my Linux system, it didn't. I got the same results when I tried using tune2fs to change it, though I'm not sure it actually did anything. Jim On 6/11/09, Petite Abeillewrote: > > On Jun 11, 2009, at 4:53 PM, Sam Carleton wrote: > >> I am a late comer to this discussion, so this might have already >> been purposed... > > Additionally, if this was not mentioned already, you can partition > your database across multiple physical files through the magic of > 'attach database' or something and load balance across those. > > http://en.wikipedia.org/wiki/Partition_(database) > > CHeers, > > -- > PA. > http://alt.textdrive.com/nanoki/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE with inline view/ derived table
On Thu, 11 Jun 2009 20:17:59 +0200, Frank Naudewrote: >Hi, > >I need some help getting this UPDATE to work with sqlite 3.3.8: > >UPDATE fud28_read >SET user_id=2, msg_id=t.last_post_id, last_view=1244710953 >FROM (SELECT id, last_post_id FROM fud28_thread WHERE forum_id=4 AND >last_post_date > 0) t >WHERE user_id=2 AND thread_id=t.id > >Error: near "FROM" - syntax error: HY000 > >Does sqlite support inline views/ derived tables within UPDATE >statements? Any suggestions on how to get it to work? Not directly, but you can update "the tables behind" a view with an INSTEAD OF trigger. The trigger can contain any update statement you like. http://www.sqlite.org/lang_createtrigger.html#instead_of_trigger >Best regards. > >Frank -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
On 11 Jun 2009, at 20:05, Jim Wilcoxson wrote: > If you partition the database into multiple databases, you will have > to place each on its own physical disk drive to increase transaction > rates. If your base transaction rate with one drive is T, with N > drives it should be N*T; 4 drives gives you 4x the transaction rate, > etc. Each of the drives has to be completely independent - no > filesystems crossing drives. I think - if you are serious about the problem - you can just rely on a disk array to do this for you. You see something that looks like a disk but which is of course spread over lots of spindles and with a bucketload of NV cache in front of it, and which can sustain really high numbers of ops/second. Of course that may be what the previous person meant by "special hardware". ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
On 11 Jun 2009, at 16:19, Jim Wilcoxson wrote: > SSD's usually have poor write performance, because to do a write, they > have to use read, erase, write sequences across large blocks like 64K. > Most of the SSD benchmarks that quote good write performance are for > sequential write performance. If you skip all over the disk doing > small writes, like a database does I think it's not the case that a database needs to skip all over the disk. For a start you can write to a log at the DB level, but even if you don't at least some modern filesystems are copy-on-write, so they never actually rewrite blocks (well, they do, but not in the read- modify-write sense). ZFS is one such filesystem. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
On Jun 11, 2009, at 9:05 PM, Jim Wilcoxson wrote: > you will have to place each on its own physical disk drive to > increase transaction rates. Arguably, such micro management of what data block sits on what disk spindle would be better left to the underlying volume manager or such. A bit Oracle specific, but covers a lot of relevant ground: "Back-of-the-Envelope Database Storage Design" http://www.oracle.com/technology/products/database/asm/pdf/back%20of%20the%20env%20by%20nitin%20oow%202007.pdf Cheers, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sql query with sqlite3_exec
Hi, i am using sqlite3 with C++, and everything is ok. I have a situation and i do not know how to handle it : Let's say i have a database file named "MyDatabase", i am opening this database as follows: sqlite3 *db; int rc; rc = sqlite3_open( "C:\\MyDatabase", ); if ( rc ) { sqlite3_close(db); } else //Database connection opened successfuly { char *zErrMsg = 0; rc = sqlite3_exec( db, "DELETE FROM urls", NULL, NULL, ); if( rc != SQLITE_OK ) { sqlite3_free( zErrMsg ); } sqlite3_close(db); } The previous code will delete everything in the "urls" table and this is not what i want. I have a string Array "MyURLsArray" which contains 20 URLs as strings. My question is: How can i format the SQL query so that, it will delete all the entries in "urls" table, but keep the entries that match the strings stored in the string Array "MyURLsArray" ? I am new to SQL so any help will be appreciated. Thanks in advance. -- View this message in context: http://www.nabble.com/sql-query-with-sqlite3_exec-tp23988559p23988559.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] sql query with sqlite3_exec
sql_newbie wrote: > rc = sqlite3_exec( db, "DELETE FROM urls", NULL, NULL, ); > > The previous code will delete everything in the "urls" table and this > is not what i want. > I have a string Array "MyURLsArray" which contains 20 URLs as > strings. My question is: > > How can i format the SQL query so that, it will delete all the > entries in "urls" table, but keep the entries that match the strings > stored in the string Array "MyURLsArray" ? delete from urls where url not in ('url1', 'url2', ..., 'url20'); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Issue with Distinct and Large numbers
I receive erroneous data when I try to populate a table using data from another table: Here is how! I have TABLE A (that has IDs of INTEGER, Seats as INTEGER, and so forth) I want to take this master table and in essence transfer the data I only need into another table called TABLE B (say it only has IDs of INTEGER) To do this I do the following INSERT INTO TABLE B SELECT DISTINCT Content_ID from TABLE A unfortunately numbers like 854459, 854477, 900499, 900517, 905209, 905227, will produce float results of 854459.1, 854477.1, etc. but the neighboring numbers stay intact and do not produce a float value. If I do the following : INSERT INTO TABLE B SELECT * from TABLE A It does not produce the float values of course I have extra data I don't need. As a final test to make sure it was the DISTINCT value I did INSERT INTO TABLE B SELECT DISTINCT * from TABLE A and sure enough those values that I mentioned before produced the same floating values. I noticed they are always 18 numbers apart and end in a 9 or 7. I would appreciate some advice on why this would be the case is there a bug or did I find a silly Easter Egg :-(. Thank you, David Cortez ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sql query with sqlite3_exec
Thanks, with help of Friend of mine, we have made the following changes to your statement: rc = sqlite3_exec( db, "DELETE FROM urls where url not in (" + MyURLsArray + ")", NULL, NULL, ); Igor Tandetnik wrote: > > > delete from urls > where url not in ('url1', 'url2', ..., 'url20'); > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/sql-query-with-sqlite3_exec-tp23988559p23991543.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] Issue with Distinct and Large numbers
On Jun 11, 2009, at 9:14 PM, dbcor...@rockwellcollins.com wrote: > I receive erroneous data when I try to populate a table using data > from > another table: Here is how! > > I have TABLE A (that has IDs of INTEGER, Seats as INTEGER, and so > forth) > > I want to take this master table and in essence transfer the data I > only > need into > another table called TABLE B (say it only has IDs of INTEGER) > > To do this I do the following > INSERT INTO TABLE B SELECT DISTINCT Content_ID from TABLE A > > unfortunately numbers like > > 854459, 854477, > 900499, 900517, > 905209, 905227, > > will produce float results of 854459.1, 854477.1, etc. > but the neighboring numbers stay intact and do not produce a float > value. Impossible. SQLite never converts an integer into a float without a very good reason (such as the number is too large to represent as an integer, or there was an explicit cast). Unless, of course, you are using a really, really old version of SQLite, like version 2.8 or something. Can you provide a test case that shows an integer being converted into a floating point value? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue with Distinct and Large numbers
On 12/06/2009 11:14 AM, dbcor...@rockwellcollins.com wrote: > I receive erroneous data when I try to populate a table using data from > another table: Here is how! > > I have TABLE A (that has IDs of INTEGER, Seats as INTEGER, and so forth) > > I want to take this master table and in essence transfer the data I only > need into > another table called TABLE B (say it only has IDs of INTEGER) > > To do this I do the following > INSERT INTO TABLE B SELECT DISTINCT Content_ID from TABLE A > > unfortunately numbers like > > 854459, 854477, > 900499, 900517, > 905209, 905227, > > will produce float results of 854459.1, 854477.1, etc. > but the neighboring numbers stay intact and do not produce a float value. [snip] You are likely to get better responses faster if you post a small script (preferably language agnostic e.g. for the command-line sqlite3 program) that reproduces what you describe. Also what version of sqlite3? binary download or home-made (if so, how?)?, what platform are you running this on? what mechanism are you using that inspects table_B and finds values like 854459.1? is this finding corroborated by any other mechanism? But before you start that, try this simple query; it might give us a clue as to what the problem is: select typeof(Content_ID), count(*) from table_A group by typeof(Content_ID); Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] repeating events?
Hello, I'm looking for suggestions on how to store and retrieve events for a calendering system in SQlite. For each user there must be: 1) All day events on a specific day. 2) All day events that are repeated over a given date range. 3) All day events that are repeat each day from until canceled. 4) Time specific events on a specific day. 5) Time specific events that repeat daily over a given date range. 6) Time specific events that repeat each day from now until canceled. ("Day-of-week" repeating logic may need to be added well.) So the questions: 1) How should I be structuring the DB so to store this data sanely. 2) How should I query the DB for a list of events that are scheduled at any given moment. (across all users.) 3) How should I query the DB for a list of events that are scheduled in any given time interval? (across all users.) Thank you, :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] repeating events?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Allen Fowler wrote: > I'm looking for suggestions on how to store and retrieve events for a > calendering system in SQlite. The general way this is done, and especially if you want to be compatible with standards such as vcal/ical is to store events with a repeating rule. The rule may be something like the 1 st of each month or every Friday. You also store the begin date/time and end date/time (the end could be blank or some date far in the future). Separately from that you store a list of exceptions. Each exception points to an id of an event, the date/time of the exception and then describes the exception. Options are cancelling the event (eg a public holiday) or overriding it such as with a new time, description or participants. See http://en.wikipedia.org/wiki/ICalendar > 1) How should I be structuring the DB so to store this data sanely. The above is the easiest way if you want to do imports and exports. > 2) How should I query the DB for a list of events that are scheduled at any > given moment. (across all users.) > 3) How should I query the DB for a list of events that are scheduled in any > given time interval? (across all users.) Those are pretty much the same thing. The simplest way is to read in all events that have a begin date before the interval and an end date after the interval, plus all exceptions that apply to them. In terms of what happens next, my solution was for a particular date to calculate all entries that apply (using repeating rules and exceptions). One you then have the set of entries for the day you can decide how to proceed with them (for example sort by start time) and what to do about overlapping ones. Also note entries from the previous day may apply such as if the event started before midnight and finished afterwards on the calculation day. When letting users edit events you'll need to ask if they are changing one repeating instance (ie create an exception) or the core repeating event. This approach stores the minimum amount of data and makes you do the (tedious) calculations. You could do a space vs time tradeoff by having a table with the calculations already done for each day although you'll have to be very careful to ensure it stays in sync. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkox7mkACgkQmOOfHg372QT/lgCgrz61537JtBYsuezJYAmfYhEY dhQAn2Ud9GXlVFIzmg0XlA4I6+wF5na7 =7b37 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users