[Dabombber]

I've just been playing around with the built in sql, and it's pretty slow. It takes about 100ms for an INSERT, so putting GeoLite into a database would take over 2.5 hours. Is this normal, a bug or am I doing something horribly wrong?

[V1ceC1ty]

are you sure its sql or your pc thats really slow?

you should have put this in Scripting Discussion.

[Kyosaur]

Quote:

Originally Posted by Dabombber I've just been playing around with the built in sql, and it's pretty slow. It takes about 100ms for an INSERT, so putting GeoLite into a database would take over 2.5 hours. Is this normal, a bug or am I doing something horribly wrong?

Your not doing anything wrong, the built in sql is just ... slow lol. Try using one of the two MySQL plugins.

[Dabombber]

The built in sql never worked from filterscripts in 0.2 so I'm assuming this is a 0.3 bug kinda. I have tried in both filterscripts and gamemodes, on different computers and operating systems and it's still slow. I'd prefer not to use a plugin, especially if there's native functions to do the same thing .

[Misiek]

SQLite itself isn't that slow. Maybe it's something with the SA-MP's implementation.

Code:

1000 unique queries: SELECT * FROM table WHERE field LIKE ‘unique_value%’ :
- SQLite - 0.6806 sec.
- SQLite (Memory) - 0.71688 sec.
- MySQL (MyISAM) - 0.16667 sec.
- MySQL (InnoDB) - 0.13743 sec.
- MySQL (Memory) - 0.33858 sec.

Code:

1000 identical queries SELECT * FROM table:
- SQLite - 0.77956 sec.
- SQLite (Memory) - 0.76029 sec.
- MySQL (MyISAM) - 0.86335 sec.
- MySQL (InnoDB) - 1.31506 sec.
- MySQL (Memory) - 0.77291 sec.

Code:

1000 INSERTS:
- SQLite - 74.157 sec.
- SQLite (BEGIN-COMMIT) - 0.16181 sec.
- SQLite (Memory, BEGIN-COMMIT) - 0.13252 sec.
- MySQL (MyISAM) - 0.11942 sec.
- MySQL (InnoDB) - 25.019 sec.
- MySQL (InnoDB, BEGIN-COMMIT) - 0.16452 sec.
- MySQL (Memory) - 0.08914 sec.

(from http://bukox.pl/php/sqlite-alternatywa-dla-mysql/)

And when it comes to inserting/updating/deleting a lot of data, use transactions, which are present in SQLite (unlike MySQL's MyISAM for example).
You've got to remember, that G-Stylezz's MySQL plugin features threading, so queries won't freeze your server until they are done.

[gotenks918]

When performing queries and your only returning a set amount of data/rows, use LIMIT, for example if your logging someone in SELECT * FROM table WHERE(username = username, password = password) LIMIT 1;

[Dabombber]

Thanks for the tips, using transactions helps lots. ~100k INSERTs in 7 seconds, 4 of them for processing the file, 3 for the INSERTs which isn't too bad.

SELECT statements don't seem to be that slow, using "LIMIT 1" doesn't seem to have any effect on the speed. Using SELECT to get a country from ~100k entries takes about 200 ms if nothing is found, which I'm guessing is the worst case since some only take 1ms.

No idea if these numbers are normal or samp being slow, it's almost usable but still not as good as using file operations (the worst case for getting a country is 170ms).

[RoBo]

SQLite is native to SA:MP and uses a single file for the database, MySQL is only provided via plugins and has to connect to a server thus is MUCH slower.