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

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

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

[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

[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

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

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

[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

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

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

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;

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

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

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

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

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

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

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

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

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

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,

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

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

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

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

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,

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

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 Weimer wrote: > 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

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

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

[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" -

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

Re: [sqlite] UPDATE with inline view/ derived table

Hi Pavel, On Thu, Jun 11, 2009 at 8:28 PM, Pavel Ivanov wrote: > 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

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.

Re: [sqlite] UPDATE with inline view/ derived table

On Thu, 11 Jun 2009 20:17:59 +0200, Frank Naude wrote: >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

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

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.

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

[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 ) {

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

[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

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'); > >

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

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

[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

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