Threaded SQL queries

Discussion in 'Plugin Development' started by Goblom, Jan 21, 2014.

Thread Status:
Not open for further replies.
  1. Offline

    Goblom

    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?
     
  2. Offline

    xTrollxDudex

    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();
     
  3. Offline

    morshu9001

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

    Goblom

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

    morshu9001

    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.
     
  6. Offline

    Goblom

  7. Offline

    xTrollxDudex

    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.
     
  8. Offline

    1Rogue Retired Staff


    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.
     
  9. Offline

    Goblom

  10. Offline

    1Rogue Retired Staff

    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).
     
  11. Offline

    Goblom

    1Rogue That seems way to advanced for me at the current time :'(
     
  12. Offline

    1Rogue Retired Staff

    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.
     
  13. Offline

    CraftBang

    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
     
  14. Offline

    Goblom

  15. Offline

    CraftBang

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

    Goblom

    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.
     
  17. Offline

    morshu9001

    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.
     
Thread Status:
Not open for further replies.

Share This Page