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.
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.
-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] Advice on which to use (SQLite or SQL Server) for the following app.
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
Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection
On Tue, 9 Jun 2009 15:23:42 -0700 (PDT), Joanne Pham wrote: >Sorry Couldn't locate the email about Compite with> DSQLITE_THREADSAFE=1 bu the application has multiple >threads using the same connection? >Would you pleas direct me to any document that has this info. >Thanks, >JP As far as I can tell there is no problem with it, if there ever was a problem with it is has been solved. Perhaps you were thinking of this article: To: Subject: Re: [sqlite] SQLite spawns multiple processes? From: "D. Richard Hipp" Date: Fri, 15 May 2009 14:03:05 -0400 Of course you have to search the ticket database. http://www.sqlite.org/cvstrac/search?t=1&c=1 Any problem ever reported is registered there. > >From: Kees Nuyt >To: sqlite-users@sqlite.org >Sent: Tuesday, June 9, 2009 12:52:47 PM >Subject: Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has >mulitple threads using the same connection > >On Tue, 9 Jun 2009 12:06:44 -0700 (PDT), Joanne Pham > wrote: > >> >> >>Hi All, >>What was the problem with the SQLite library is builded >>with DSQLITE_THREADSAFE=1 but the application is using >>multiple threads with the same connection. >>Thanks, >>JP > >Joannek, > >I think this same issue was discussed very recently in the >mailing list. You may want to consult the archives. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Topics 1 - Re: sqllogictest tool - please help :-)
What have you been able to do so far? Have you been able to build sqllogictest (SLT)? Were you able to retrieve some of the test files for SLT? Have you gotten MySQL installed? Are you able to use MySQL via an ODBC interface? (SLT uses an ODBC interface to non-SQLite databases.) On Wed, Jun 10, 2009 at 3:27 AM, tsachi ofir wrote: > > > > Hi, > > we are working with SQLite in our company. > we need a tool that check if SQL returns the correct answer and to > compare with MySQL. > I got a new job and its my first assignment ( to start working on > sqlogictest) , and I don't understand much about sqllogictest. > Please help. > > Tsachi > > > > > > -- > > > > Message: 1 > > Date: Tue, 9 Jun 2009 16:12:30 -0400 > > From: "D. Richard Hipp" > > Subject: Re: [sqlite] sqllogictest tool - please help :-) > > To: General Discussion of SQLite Database > > Message-ID: <511286c3-5b52-4f01-8f74-3448d23d8...@hwaci.com> > > Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes > > > > > > On Jun 9, 2009, at 10:38 AM, tsachi ofir wrote: > > > > > Hi , > > > I wont to use the "sqllogictest" program. > > > > Let's start with the basics. Why do you think you want to use this > > program? What do you think it will accomplish for you? > > > > > > > > I'm using SQLite. > > > > > > I have read the "About Sqllogictest" and didn't fully understand how > > > to work > > > with it. > > > Can you please help on how I can use it? I will be very grateful for > > > your > > > help. > > > > > > - Where are the files of the program? is it those three files? > > > > > > > > > 1. sqllogictest.mk > > > 2. sqllogictest.h > > > 3. sqllogictest.c > > > > > > > > > - How can I install it? > > > - Just to migrate the files to which folder? > > > > > > Thanks for your help. > > > > > > Tsachi Ofir > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > D. Richard Hipp > > d...@hwaci.com > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order by term not in result set
Pavel Ivanov wrote: > It mentions "from" and "column_name" where column name in this > particular case is "selected". ;-) > > @Igor: I thought that sql standard in this case doesn't guarantee that > outer select will return rows in the same order that were enforced in > inner select by "order by", does it? Yes, technically, I guess, the order is not guaranteed, though SQLite is highly likely to return records in the same order subselect generated them. Note that Rand Huck's temporary table solution suffers from the same problem. My solution can be modified to give you that extra peace of mind: select id, url, selected from ( SELECT id, url, selected, name FROM db1.test UNION SELECT id, url, selected, name FROM db2.test ORDER BY name ASC, id DESC LIMIT 100 ) order by name ASC, id DESC; Of course, both are solutions in search of a problem. It is likely trivial to simply ignore the extra column in the host application. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order by term not in result set
Note to self: Do not ask questions before I finish my coffee. ;) On Wed, Jun 10, 2009 at 11:27 AM, Pavel Ivanov wrote: > It mentions "from" and "column_name" where column name in this > particular case is "selected". ;-) > > @Igor: I thought that sql standard in this case doesn't guarantee that > outer select will return rows in the same order that were enforced in > inner select by "order by", does it? > > Pavel > > On Wed, Jun 10, 2009 at 11:15 AM, Rand Huck wrote: > > Just out of curiosity, where in the syntax documentation does it mention > > "selected from"? > > > > On Wed, Jun 10, 2009 at 10:57 AM, Igor Tandetnik >wrote: > > > >> Rand Huck wrote: > >> > In addition to what was already said, if you absolutely to omit the > >> > 'name' from the final result set, you should be able to put the > >> > result of the union in a temporary table. > >> > >> Or else add an extra layer of indirection: > >> > >> select id, url, selected from ( > >> SELECT id, url, selected, name FROM db1.test UNION > >>SELECT id, url, selected, name FROM db2.test > >>ORDER BY name ASC, id DESC LIMIT 100 > >> ); > >> > >> This essentially instructs SQLite to create a temporary table for the > >> duration of this statemet (also known as ephemeral tables). > >> > >> Igor Tandetnik > >> > >> > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order by term not in result set
It mentions "from" and "column_name" where column name in this particular case is "selected". ;-) @Igor: I thought that sql standard in this case doesn't guarantee that outer select will return rows in the same order that were enforced in inner select by "order by", does it? Pavel On Wed, Jun 10, 2009 at 11:15 AM, Rand Huck wrote: > Just out of curiosity, where in the syntax documentation does it mention > "selected from"? > > On Wed, Jun 10, 2009 at 10:57 AM, Igor Tandetnik wrote: > >> Rand Huck wrote: >> > In addition to what was already said, if you absolutely to omit the >> > 'name' from the final result set, you should be able to put the >> > result of the union in a temporary table. >> >> Or else add an extra layer of indirection: >> >> select id, url, selected from ( >> SELECT id, url, selected, name FROM db1.test UNION >> SELECT id, url, selected, name FROM db2.test >> ORDER BY name ASC, id DESC LIMIT 100 >> ); >> >> This essentially instructs SQLite to create a temporary table for the >> duration of this statemet (also known as ephemeral tables). >> >> Igor Tandetnik >> >> >> >> ___ >> 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] Order by term not in result set
Just out of curiosity, where in the syntax documentation does it mention "selected from"? On Wed, Jun 10, 2009 at 10:57 AM, Igor Tandetnik wrote: > Rand Huck wrote: > > In addition to what was already said, if you absolutely to omit the > > 'name' from the final result set, you should be able to put the > > result of the union in a temporary table. > > Or else add an extra layer of indirection: > > select id, url, selected from ( > SELECT id, url, selected, name FROM db1.test UNION >SELECT id, url, selected, name FROM db2.test >ORDER BY name ASC, id DESC LIMIT 100 > ); > > This essentially instructs SQLite to create a temporary table for the > duration of this statemet (also known as ephemeral tables). > > Igor Tandetnik > > > > ___ > 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] Topics 1 - Re: sqllogictest tool - please help :-)
I think your problems have nothing to do with SQLite, yet. To begin you need some help related to the "C" programming language. The three files you have listed are the "Make", "Source" and "Header" files for a C program. You must compile these to develop an executable program. Once you have accomplished this, you can start learning SQLite. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of tsachi ofir Sent: Wednesday, June 10, 2009 2:27 AM To: sqlite-users@sqlite.org Subject: [sqlite] Topics 1 - Re: sqllogictest tool - please help :-) > > Hi, we are working with SQLite in our company. we need a tool that check if SQL returns the correct answer and to compare with MySQL. I got a new job and its my first assignment ( to start working on sqlogictest) , and I don't understand much about sqllogictest. Please help. Tsachi > > -- > > Message: 1 > Date: Tue, 9 Jun 2009 16:12:30 -0400 > From: "D. Richard Hipp" > Subject: Re: [sqlite] sqllogictest tool - please help :-) > To: General Discussion of SQLite Database > Message-ID: <511286c3-5b52-4f01-8f74-3448d23d8...@hwaci.com> > Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes > > > On Jun 9, 2009, at 10:38 AM, tsachi ofir wrote: > > > Hi , > > I wont to use the "sqllogictest" program. > > Let's start with the basics. Why do you think you want to use this > program? What do you think it will accomplish for you? > > > > > I'm using SQLite. > > > > I have read the "About Sqllogictest" and didn't fully understand how > > to work > > with it. > > Can you please help on how I can use it? I will be very grateful for > > your > > help. > > > > - Where are the files of the program? is it those three files? > > > > > > 1. sqllogictest.mk > > 2. sqllogictest.h > > 3. sqllogictest.c > > > > > > - How can I install it? > > - Just to migrate the files to which folder? > > > > Thanks for your help. > > > > Tsachi Ofir > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > D. Richard Hipp > d...@hwaci.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order by term not in result set
Rand Huck wrote: > In addition to what was already said, if you absolutely to omit the > 'name' from the final result set, you should be able to put the > result of the union in a temporary table. Or else add an extra layer of indirection: select id, url, selected from ( SELECT id, url, selected, name FROM db1.test UNION SELECT id, url, selected, name FROM db2.test ORDER BY name ASC, id DESC LIMIT 100 ); This essentially instructs SQLite to create a temporary table for the duration of this statemet (also known as ephemeral tables). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Order by term not in result set
In addition to what was already said, if you absolutely to omit the 'name' from the final result set, you should be able to put the result of the union in a temporary table. CREATE TEMPORARY TABLE `tmp` (`id`, `url`, `selected`, `name`); INSERT INTO `tmp` ( SELECT id, url, selected, name FROM db1.test UNION SELECT id, url, selected, name FROM db2.test ORDER BY name ASC, id DESC LIMIT 100); SELECT `id`, `url`, `selected` FROM `tmp`; Takes a little more CPU, but gets the job done. Although my expertise is more on the MySQL side, so Sqlite might have some kind of limitation I don't know about. On Tue, Jun 9, 2009 at 1:20 PM, wrote: > On Tue, 9 Jun 2009, Simon Slavin wrote: > > >> SELECT id, url, selected, name FROM db1.test UNION > >> SELECT id, url, selected, name FROM db2.test > >> ORDER BY name ASC, id DESC LIMIT 100" > > > > I would add to Marin's answer that reading the above, I have no idea > > which database the column 'name' is in. Suppose db1 and db2 both had > > a 'name' column. Which one would it be. So it might be better to use > > > > SELECT id, url, selected, name FROM db1.test UNION > > SELECT id, url, selected, name FROM db2.test > > ORDER BY db1.name ASC, db2.id DESC LIMIT 100 > > The answer to the question 'If both had a name column, which one would it > be?' is both. The UNION operator creates a resultset having a single name > column which contains values from both tables. ORDER BY operates on this > combined resultset, it cannot distinguish between the name values > contributed by db1 and those contributed by db2. The ordering is only > performed on the combined results, not on the separate SELECT clauses. > Once the UNION is generated, there is only a single name column to be > sorted. So the 'ORDER BY db1.name' shown above gives the same results as > if you entered 'ORDER BY name'. The 'db1' prefix is ignored in this > context. > > The question "suppose both had a name column" may reflect a > misunderstanding of the UNION. Each SELECT clause of a union must > contribute the same number of columns, thus, it is not possible to have > 'name' coming only from one SELECT clause and not the other. For instance, > this will fail: > > SELECT id, url, selected, name FROM db1.test UNION > SELECT id, url, selected FROM db2.test; > > It fails because the first SELECT has 4 cols, the 2nd has 3. If db2.test > in fact does not have a name column, but you want include name values from > db1, you need to use a placeholder like this: > > SELECT id, url, selected, name FROM db1.test UNION > SELECT id, url, selected, NULL FROM db2.test; > > or this > > SELECT id, url, selected, name FROM db1.test UNION > SELECT id, url, selected, ' ' FROM db2.test; > > The result of these queries will have values from db1 for name, but the > name column for data contributed by db2 will be NULL/empty. > > Chris > ___ > 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] help,low RAM problem
You have to configure the page size and cache size (number of pages) to fit your 30K RAM. To be specific: page size * cache size < 30K. 徐中华 wrote: > > > > > hi, > I have a project based on an embedded platform. > the platform just has 30K RAM and a large NAND Flash, > so I want to use SQLite on this platform. > Can SQLite 3.6 be used on this platform . And if it 's possible, > how can I configure the SQlite ? >Thanks. > ___ > 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/help%2Clow-RAM-problem-tp23513661p23961762.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Topics 1 - Re: sqllogictest tool - please help :-)
> > Hi, we are working with SQLite in our company. we need a tool that check if SQL returns the correct answer and to compare with MySQL. I got a new job and its my first assignment ( to start working on sqlogictest) , and I don't understand much about sqllogictest. Please help. Tsachi > > -- > > Message: 1 > Date: Tue, 9 Jun 2009 16:12:30 -0400 > From: "D. Richard Hipp" > Subject: Re: [sqlite] sqllogictest tool - please help :-) > To: General Discussion of SQLite Database > Message-ID: <511286c3-5b52-4f01-8f74-3448d23d8...@hwaci.com> > Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes > > > On Jun 9, 2009, at 10:38 AM, tsachi ofir wrote: > > > Hi , > > I wont to use the "sqllogictest" program. > > Let's start with the basics. Why do you think you want to use this > program? What do you think it will accomplish for you? > > > > > I'm using SQLite. > > > > I have read the "About Sqllogictest" and didn't fully understand how > > to work > > with it. > > Can you please help on how I can use it? I will be very grateful for > > your > > help. > > > > - Where are the files of the program? is it those three files? > > > > > > 1. sqllogictest.mk > > 2. sqllogictest.h > > 3. sqllogictest.c > > > > > > - How can I install it? > > - Just to migrate the files to which folder? > > > > Thanks for your help. > > > > Tsachi Ofir > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > D. Richard Hipp > d...@hwaci.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
