« This Post Belongs on Techmeme | Main | ANN: FeedDemon 2.6.1 Beta 2 »

Thursday, March 27, 2008


Feed You can follow this conversation by subscribing to the comment feed for this post.

Run, don't walk, to the book store and get a copy of "The Definitive Guide to SQLite" from APress books. Not only is it a great guide to both what SQLite does and how it works, it's a really great overview of SQL. I came to SQLite when I worked on Palm's Foleo device without any background in SQL, and by the time I was through with the book, I felt I had a great grasp on things.

With SQLite: make sure you use transactions (BEGIN TRANSACTION, END TRANSACTION), as they're the main way to keep performance high and the database consistent. DB writes are only committed on the END, so this avoids lots of churn in the DB when you're doing something like updating a big bunch of records or adding a lot of data.

Also, be careful with indices. They can really speed up queries, but they also can slow down modifications. A common trick is to drop the indices, do a batch of modifications, then recreate them.

Finally, be sure to use column affinity properly. If you don't, you can get into odd situations where you get the wrong data type out of a query. In particular, VARCHAR and STRING don't mean anything, you've got to use TEXT. I found that one in some code I was auditing that was acting odd when a user entered a number with leading zeroes as their ID code :)

What controls are you using to access SQLite?

@Ben: Thanks for the tip - I just ordered that book. Column affinity is one of the first stumbling points I hit, primarily because I didn't understand how to query based on a date range. I think I've got that straightened out now, though.

@Mike: I'm using DISQLite3 for Delphi.

The snag we hit with one project was the mis-use of transactions. Make sure that you maximize their efficiency. Once you've knocked that out you're in pretty good shape as its performance is pretty reasonable. We've moved some software over to Firebird, but I think that SQLite has a lot of merit. Can't wait to test out the updated code.

Um - for anything SQL (and specific syntax) - I tend to hit www.sqlzoo.net - and from memory they also have a tab for SQLLite now as well.

@Warreen thanks for the pointer to SQLZoo.com. I use Oracle, DB2, SQL Server, and MySQL on a weekly basis and always find myself trying to remember syntax differences and having to look them up. That site could turn into my goto source.

Technically, it should be celibate and not virgin ;-)

I'm in the same boat. In the early 90's I sold a few systems that I developed using R-Base, MS Access. Today I can only read the SELECT statements and hope for the best.


@Ben: thanks again for the book recommendation - it arrived today, and as you said, it's excellent.

I just started playing around with DISQLite myself a week ago. One of my products runs on Jet/ADO, and I swore I would never go that route again. Not only that, but SQLite is more "portable" to other platforms.

Have you considered NOT storing the XML feeds in SQL?


Very good performance, leaves the data in XML (including for example extension elements) lets you run XQuery and XPath to query the data. The database is under the ASL 1.1 and BSD licenses.

@Gavin: thanks for the tip about Berkeley DB XML. Any idea how embedding it in your app increases the EXE size?

The comments to this entry are closed.