SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Help Archive

 
 
Thread Tools Display Modes
Old 24/09/2009, 10:07 AM   #1
Dabombber
Big Clucker
 
Join Date: May 2006
Posts: 79
Reputation: 1
Default SQL performance

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?
Dabombber is offline  
Old 24/09/2009, 10:36 AM   #2
V1ceC1ty
Banned
 
Join Date: Oct 2008
Location: West Australia
Posts: 1,896
Reputation: 273
Default Re: SQL performance

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

you should have put this in Scripting Discussion.
V1ceC1ty is offline  
Old 24/09/2009, 10:39 AM   #3
Kyosaur
Gangsta
 
Kyosaur's Avatar
 
Join Date: Mar 2008
Location: USA - California
Posts: 973
Reputation: 349
Default Re: SQL performance

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.
Kyosaur is offline  
Old 24/09/2009, 10:44 AM   #4
Dabombber
Big Clucker
 
Join Date: May 2006
Posts: 79
Reputation: 1
Default Re: SQL performance

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 .
Dabombber is offline  
Old 24/09/2009, 11:56 AM   #5
Misiek
Big Clucker
 
Join Date: May 2006
Posts: 113
Reputation: 16
Default Re: SQL performance

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.
__________________
Ex-SA-MP beta tester 😉
net4game.com
Misiek is offline  
Old 24/09/2009, 12:27 PM   #6
gotenks918
Huge Clucker
 
Join Date: May 2007
Posts: 202
Reputation: 0
Default Re: SQL performance

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;
gotenks918 is offline  
Old 24/09/2009, 03:31 PM   #7
Dabombber
Big Clucker
 
Join Date: May 2006
Posts: 79
Reputation: 1
Default Re: SQL performance

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).
Dabombber is offline  
Old 24/09/2009, 03:49 PM   #8
RoBo
Huge Clucker
 
RoBo's Avatar
 
Join Date: Jan 2008
Posts: 377
Reputation: 12
Default Re: SQL performance

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.
__________________
as4life
RoBo is offline  
 

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Timers performance Zamaroht Scripting Help 5 30/08/2012 09:29 AM
9400 GT - GTA:SA Performance rafay Everything and Nothing 14 09/06/2009 04:23 PM


All times are GMT. The time now is 10:41 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.