Sorry about the previous mail, I hit tab in a gmail and it automatically
sent it.
Anyway, back to my question
I'm planning out a program right now - web based - that would require each
user of the application to be able to create their own independent data
store with its own schema and modifications etc. In a perfect world,
sqlite
would be great for this as I could just give each individual user their
own
sqlite database and let them manipulate tables and schema as they see fit
before they start to store information into the db.
My concern is scalability and the performance hit of having let's say 5000
users potentially, accessing 5000 databases at the same time on the
server.
Based on my readings around the web there are ways to optimize sql queries
(transactions, in memory dbs and all that) but even with that, would it be
feasible or better yet advisable?
The main issue here is the ability of the users to create and modify their
own schemas which may leave my only other option to be xml
Thanks.
Jason
It of course depends on the nature of your application, i.e., how the user
will be using the database. Are they going to perform inserts or updates of
large data set all the time? There are some common techniques for sqlite to
improve its performance, such as use PRAGMA synchronous = OFF;, use
transactions to wrap up inserts and updates whenever possible, and use
indexes. My experience and the tests done here:
http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison indicated that indexes
do improve performance greatly.
If your users were to input a CREATE TABLE statement, there's nothing much
you can do. However, if you only let the user design a table, when you
generate the CREATE TABLE statements, you might also want to take care of
index creations.
Another thing you should look into is the concurrency. SQLite can't write
(inserts and updates), when another connection is reading (selects) from the
same database file. It looks like each user having their own database file
is the way to go, but just make sure you handle busy status properly.
My 2 cents.
Best regards,
He Shiming
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
