Threaded SQL queries

I am writing a plugin and it has come to my attention that all the DB querys are bound to cause lag, I want my plugin to have no lag (hopefully, or at least have it happen rarely).

I have looked around and i haven't found out how to make Threaded SQL Queries. This is the class that i am using Database.java every query goes through the query(String string) method, and connection is opened when query is made and closed when query is completed.

So, how do i make each query Threaded so that it doesn't lag the server when a query is made?

 

Goblom
You spelled copyright wrong, and if you are copyrighting it, why release the source?

Back to the topic, do you want something like:

PHP:

private class Task extends Thread {
    public void run() {
        // Execute async methods
    }
}
new Task().start();

 

I agree with xTrollxDudex's solution. It's also better to not open/close the connection every single time you make a query.

 

morshu9001 should I keep the connection open and just create new statements every time?

 

I think you're supposed to open it when the plugin starts and close it when it ends. Either that or just less frequently than you have it set up to do now. But you need to make sure it won't crash the plugin if the connection is established then the MySQL server goes down later.

 

xTrollxDudex What about using "synchronized"...

Code:java

synchronized ResultSet query(String sql) {
    //do query stuff
}

 

Goblom
You shouldn't need to synchronize it. If you lock access to the class with a singleton, then synchronization is unnecessary, see my post down in kumpelblase2 's design patterns resource.

 

Depending on the implementation opening/closing for each queries has advantages and disadvantages. For one, keeping a constant global connection somewhat breaks the idea of security/global state. If I have a "rogue" class that decides to close the connection I could wind up crashing my entire plugin. Sure, there are safeguards you can put against this, but I usually find it is easier to simply open a connection throughout an entire operation session, and close when you finish. If you run this all through a separate thread, there's not much loss either.

 

1Rogue THE SQL GOD

Did i Thread the DB Connection correctly or efficiently? (This would be my first attempting at Threading a query)

https://github.com/Goblom/CommonNet...rg/goblom/cnc/core/database/Database.java#L52
https://github.com/Goblom/CommonNet...g/goblom/cnc/core/database/Database.java#L419

 

Instead of building your API into using threads, try making a solid SQL api, and then making all of those calls from a separate thread. (So you have a query method / etc that would run things on the same thread it's called from, just call it from another thread).

 

1Rogue That seems way to advanced for me at the current time

 

Completely forget about threading. Write your SQL normally. Then, when you finish that, make it so that all calls to your functions that would create an SQL query are run through a thread.

 

Goblom if you can make this, would you than please release the source, since this is to advanced for me too I want to learn from the code.
I also tried something with MySQL and it amde my server lagg (because main thread was trying to get the MySQL data)
But I failed in making it in new thread and that stuff. I saw that @compression was making a class, I think it'll work right.
(My topic : http://forums.bukkit.org/threads/mysql-causing-lagg.221418/)
@comperssion 's class (not done)

https://gist.github.com/bm/8506344

 

CraftBang the whole thing is open source...

https://github.com/Goblom/CommonNet...e/src/main/java/org/goblom/cnc/core/database/

 

Goblom but it's still freezing the server for some secs right ?

 

CraftBang I havent tested it yet. Common Network Core is in pre-alpha and i am still working on the main areas of the plugin.

 

What 1Rogue said isn't as difficult to do as it might seem. You just have to write your SQL classes as normal, ignoring threading, and then either add some methods that call your query (or other database) methods in a separate thread, or have the main plugin call your database methods in a separate thread.

If you choose to connect to the SQL server when the server is starting, you have the option to not run that in a separate thread so your server waits until it has a connection to the SQL server. Or run it in a separate thread, making sure that nothing will crash if you call the query methods before there's a connection. But it should have a connection timeout… I don't know what Java's SQL connect() method does exactly.