On 16/10/10 21:07, Chris Percol wrote: > Hi there, > > I am thinking of moving from mysql to sqlite3 and want to know if there are > any gotchas? > > My current situation is a web based school booking system using a mysql > database with four tables. The busiest of the tables may have 6000 rows > added each year (I tend to empty this table at the end of the year). The > other tables store a little information, maybe 100 rows between them. > > There is approx 100 users creating bookings throughout the week, not a lot > of concurrents but I guess the possibility is there. > > From what I read on the sqlite site sqlite could handle this like a breeze. > I just wanted to ask for any thoughts regarding performance or limitations > before I make the move. >
I don't know what language your web application is in, but I have just completed a port from Postgres of a financial accounting and planning application I wrote several years ago for myself. This was using PHP. From a performance perspective, it is really fast. I open a transaction and the head of most web pages, and keep it open through out the page even if I am only using selects, and every page I have comes up instantenously. Even one which calculates the complete profit and loss for my business, including calculating depreciation for capital purcheses made part way through a year finishes fast than you can notice. Riding on the success of that I am part way through porting an American Football results picking competition I think its important for a multiuser application to use WAL mode and I think this minimises the potential for locking conflicts from each of users. The benefit of a web application is that each page request opens the database, does its stuff and closes it again. There is therefore a reasonably large probability that all connections to the database are closed in a reasonably short space of time. In WAL mode, the last database close encorporates the WAL back into the main database, so there is relatively little time when its not encorporated back in. For me, the biggest problem is having sufficiently recent versions of all the code and libraries to support WAL mode. At least one host (Bluehost) that I use can't handle that. -- Alan Chandler http://www.chandlerfamily.org.uk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
