How efficient is SQL?

[supanatral]

I know its a stupid question because SQL is pretty much made to be efficient but I'm just wondering how efficient it is for large amounts of data. I'm planning on storing the entire bible in a SQL database if I go through with it.

I tried it and it seems slow when it tries to load the bible chapter. I ran it on the performance program and I attached the results

[City41]

SQL Is merely a language. How the database is implemented and even more important how you use it determine how efficient it is.

[Jwesttci]

SQL can be very efficient as long as you access your data using indexes. The problem most developers run into with relational databases is that they don’t give proper consideration to how the indexes are structured when creating their SQLs. The database software is going to extract the information you requested even if it needs to read thru every record in the table. One thing I have found is that joining tables together using a single query can sometimes confuse the database optimizer logic (if the SQL is not built correctly) causing a full table read, sometimes a full sub-table read for every record selected from the primary table. I have been developing code using ORACLE & MySQL for 12 years and try to stay clear of joins whenever I can. If you must join tables together try creating a view.

[detz]

If I was doing that I would store them as text files and just load them when you need them. There is no way to store book information in a relational database.

[dicklacara]

Originally Posted by detz

If I was doing that I would store them as text files and just load them when you need them. There is no way to store book information in a relational database.

Normally, I'd agree...

...except in this case (the bible) you have lots of small files (chapter and verse) that you want to index and manipulate. So, the entire bible might require thousands of individual text files.

What's the minimum OS X file size on the iPhone?

You might be able to save a lot of Flash storage and minimize RAM use by using an SQL db over manipulating text files.

You could develop a complex scheme where you had a separate text file for each chapter, and an offset index (text file) into that file to locate the verses.

But, now, you are trying to outdo SQL at its job.

SQLite supports Blobs (fields of arbitrary size) so any field size is possible (even the entire bible in a single field).

Interestingly, I just checked, and SQLite does not enforce size limits (nor truncate) varchar fields. So, you could (conceptually, anyway) store the verses of a chapter as a many-to-one table of verses (varchar) related to a one-to-many table of chapters.

To use any SQL database intelligently and efficiently, you should: create indexes where appropriate, based on usage; consider the underlying structure of the data when making queries (manipulating data); and avoid redundancies where possible.

However, sometimes, if you break the redundancy rule, you can gain efficiency and flexibility at the expense of a little redundant data.

For example, we might have a chapter index that contained:
title: "John 3:16"
pointer/key to verse table

However, we intend to use the db by displaying the chapter title and the first few words of the verse, say in a table view.

So, every cellForRowAtIndexPath (worst case) would query the chapter index table, and the related verse table to display our row.

But, if we change our chapter index to:
title: "John 3:16"
words: "For God so loved the world..."
pointer/key to verse table

We can do a lot of manipulation (search/sort) entirely within the chapter index table (without ever accessing the verse table), and display table rows like:

John 3:16 For God so loved the world...

*

*

*

We have broken the rule, have redundant data, increased file size some... but gained a whole lot of performance in how we actually use the db.


HTH

Dick