Best solution for large local database

[vasouv]

Hello fellow coders, I'd like some advice on a project I'm building. I have a 35MB SQL file that inserts data to the database, my machine needed ~2 hours to insert all the data in MySQL. The largest table has around half a million entries.

Now, since I'm building the app on my machine, it's fairly easy since I can start the server, test the app etc. If I want to test the app on a linux system or give it to a friend to test it etc, I must set the MySQL server again and re-run the SQL file. It's a bit of a p.i.t.a. 'cause each system will need to be configured separately. I believe this goes for each of the big RDBMSs.

My first choice was SQLite (single file DB), though I don't think it's supposed to be used in large databases such as mine, with so many thousands of entries.

The obvious (and possibly the best) solution to this, would be to rent a MySQL server and be on my merry way. I don't want to do that yet though, that might happen if I decide to make my app a web one, and go commercial.

So, what do you think is the best approach to this?

[rowdy]

You could try something like Firebird.

It's feature set is pretty limited, but unless it has changed radically in the last few years you could setup a database which would store all the data in a single file, then just backup, or even rename (to make sure nothing is accessing it) and copy the database file itself.

[phoenix1234]

Hello fellow coders, I'd like some advice on a project I'm building. I have a 35MB SQL file that inserts data to the database, my machine needed ~2 hours to insert all the data in MySQL. The largest table has around half a million entries.

Now, since I'm building the app on my machine, it's fairly easy since I can start the server, test the app etc. If I want to test the app on a linux system or give it to a friend to test it etc, I must set the MySQL server again and re-run the SQL file. It's a bit of a p.i.t.a. 'cause each system will need to be configured separately. I believe this goes for each of the big RDBMSs.

My first choice was SQLite (single file DB), though I don't think it's supposed to be used in large databases such as mine, with so many thousands of entries.

The obvious (and possibly the best) solution to this, would be to rent a MySQL server and be on my merry way. I don't want to do that yet though, that might happen if I decide to make my app a web one, and go commercial.

So, what do you think is the best approach to this?

I'm quite sure that your first choice sqlite is the best choice.
It is because sqlite can handle up to Tetra-bytes system.
So I don't think a millions of records will be any issue with sqlite.
 

[vasouv]

SQLite eh? Yeah I guess it's the best choice since I need to transfer the DB along with the app.

Firebird I'd never heard of but I'll take a look at, I see it has a Java driver and a .net one.

I even considered the embeddable DerbyDB but eeeh, will make the executable too big I guess.

Oh well, here's to another 2hrs of populating a SQLite db to start with...

Thanks guys

[rowdy]

Have you tried disabling indexes before doing the import, then enabling them afterwards?

Often faster to do that.

[vasouv]

No I hadn't tried that to be honest. The SQL file I have, doesn't create indexes (or primary keys for the matter of fact, I add them later).

For starters, I'll try SQLite through command-line, I'll leave the Firefox extension out of this since it derped up yesterday...

If that doesn't work out, I'll write a program that reads from MySQL and writes to MongoDB, there are a couple hosts that provide 512MB databases for free, eh, better than nothing. Want to check out MongoDB anyway.

[phoenix1234]

No I hadn't tried that to be honest. The SQL file I have, doesn't create indexes (or primary keys for the matter of fact, I add them later).

For starters, I'll try SQLite through command-line, I'll leave the Firefox extension out of this since it derped up yesterday...

If that doesn't work out, I'll write a program that reads from MySQL and writes to MongoDB, there are a couple hosts that provide 512MB databases for free, eh, better than nothing. Want to check out MongoDB anyway.

Derby is ok and reliable but please note that Derby is Java-based so it is up to you whether to have jvm or not.

For Sqlite manager, you can take a look at sqliteman (http://sqliteman.com/) instead of Sqlite Manager Firefox Plugin.

[agodinhost]

What's the problem with the MySQL man?
A 500k insert will take time whatever database you choose.
sqLite is lighter and quicker but only because it doesn't have a few features

SQLite:

    easier to setup
    great for temporary (testing databases)
    great for rapid development
    great for embedding in an application
    doesn't have user management
    doesn't have many performance features
    doesn't scale well.

MySQL:

    far more difficult/complex to set up
    better options for performance tuning
    can scale well if tuned properly
    can manage users, permissions, etc.

take a look at here

[JPG]

I don't know them all, but I work a lot with MSSQL server and I got some experience with oracle and I know that both offer a free "express" version. There are some limitations, but for a personal database you should be way ok.

I use MSSQL server at job and I like it a lot. There's a lot of powerfull tools and you can end up using the guy interface if you want (easier if you are new) or do it all by commands.

As for your objective, well you could simply load all the data and make a backup of the database. The you would only need to restore the database, which will take as long as you need to write it to the disk. You could also make your database available through internet, but it could be annoying if you don't have a static IP.

Also, I never used MySQL, but I am pretty sure you can do all of this with it since it's used a lot, even in big production environments.

[daerid]

I've been using MySQL on and off again for over 10 years, and I've hated it every time.

[vasouv]

There were some "advances" since the last time I posted here. I managed to find an actual SQLite database file of the data I wanted. So I'll just work off that when I start.

I don't have a problem with MySQL, I think it's fine, but for my (starting) purpose I believe it's unnecessary trouble. I'd have to install it on all machines blah blah blah, keep it updated...

If I decide to take my project out in the open sure, I'll have to rent a MySQL server or similar and work off that but for prototyping purposes (as you pointed out) SQLite is easier to handle.

Besides, I'm about to install WAMP for web dev so I'll have MySQL for all my projects anyway. Static IPs and stuff are not an option right now

phoenix I'm downloading that program, I don't really like the FF extension...

[rowdy]

I used to use MySQL - I liked its simplicity, feature-set, comprehensive SQL, operating system transparency (each database in its own directory, each table in its own files).

But then Oracle bought it and went all silly with licencing.

So now I use PostgreSQL.

[nuclearsandwich]

sqlite's primary limitation isn't database size but concurrency. As long as only one process/thread needs to access the database at any given time. It's great!