Account data or SQLite

[driver2]

Hi,

I was thinking how the performance of using account data compares to using SQLite for saving/accessing account-related stuff. The advantage of account data is that it's easy to use and it is stored together with the accounts, which is sensible for stuff closely related to the account (like register date or something).

However if you have a lot of accounts (let's say a few hundred) and want to filter or sort them by register date (or anything else), I was wondering if SQLite wasn't better suited for that. What do you think?

Greetings

[dzek (varez)]

well, if you ever use MySQL you will be dissapointed with sqlite.. it dont offer too much, even if it looks different..

i bet on MTA-MySQL module the fastest, and the easier to manage

sqlite isnt fast for writing, but not slow for reading

[50p]

well, if you ever use MySQL you will be dissapointed with sqlite.. it dont offer too much, even if it looks different..

i bet on MTA-MySQL module the fastest, and the easier to manage

sqlite isnt fast for writing, but not slow for reading

He never said anything about MySQL.

SQLite is fast for saving/reading. Accounts are not as fast as SQLite if you deal with loads of accounts. It's because accounts.xml is rewritten every time something changes, say you have 1000 accounts and you want to change just one attribute of one account, it will have to rewrite entire file to do so, at least AFAIK.

[dzek (varez)]

sqlite bahaves in same way afaik rewriting whole database when storing data

i told about mysql becouse maybe he dont know about mysql possibility?

[norby89]

sqlite bahaves in same way afaik rewriting whole database when storing data

You're kidding there right?

[dzek (varez)]

Well, i didnt say that im 100% sure, but tests (not by me) showed that 1000 INSERT queries took 76 sec on sqlite, and less than 1 sec on myisam engine on mysql.. Reading is almost the same..

[eAi]

That's definitely not the case, no sane database would rewrite the whole file every time. In my uses of SQLite and MySQL, they're roughly comparable in speed, at least in a similar order of magnitude.

http://www.sqlite.org/speed.html might be of interest.

There may be issues with MTA's implementation of course.

[dzek (varez)]

that test was doing with http server, not mta, but i will test the speeds myself, and post results here.

the link you posted is outdated (as we can read on the top of that page)

[eAi]

Outdated yes, but the performance isn't likely to have got worse.

[driver2]

Adding rows to SQLite is considerably slower than reading data. However, you also have to use it correctly to make a fair comparison. I'm certainly no expert, but here's an interesting comparison: I added 100 rows to an empty table, which took about 8.8590 seconds. Then I used "BEGIN" and "COMMIT" (http://www.sqlite.org/lang_transaction.html) to send it all in one transaction, and it took only 0.1090 seconds. That's not always usable of course, but if you add much data at once, it can be useful (much like prepared statements in MySQLi, even if the method might be different).

Also, if you compare it with MySQL, you also have to take into account that not everyone may have access to a MySQL database on the same server and sending it to another takes again more time, depending on the connection.