Faster way to pull SQL data

Im back again with my SQL learning questions for bukkit got some awesome help so far, so figured I'd ask in here again.

Im using the logblock table to pull first join and timeplayed data for my players.

But I think I may be doing it the wrong way, as logblock itself is fast when doing its commands, but when using my command, its rather slow..I can see the lag as its pulls and sends each result to chat.

how Im doing it this:
My on enable:

Code:java

 
public void onEnable() {
System.out.println("[DXTime] Loaded!");
this.mysql =
new MySQL(this.log, "[DXTime]",
getConfig().getString("mysql.host"),
getConfig().getString("mysql.port"),
getConfig().getString("mysql.dbName"),
getConfig().getString("mysql.dbUser"),
getConfig().getString("mysql.password"));
this.mysql.open();
getCommand("age").setExecutor(new LPCommand(this));
getServer().getPluginManager().registerEvents(new LCListener(this), this);
info(getDescription(), "enabled");
}
 

and one of my code full pulling from that:

Code:java

 
public String LBtime(String playera)
{
ResultSet rs = this.mysql.query("SELECT * FROM `lb-players` WHERE playername = '" + playera + "'");
try
{
rs.first();
String LBtime = rs.getString("onlinetime");
return LBtime;
}
catch (SQLException e) {
}return "Error";
}
 

The issue is I have a few "Public String" using that code but to pull different queries. and when running the command that calls it, it takes 1-2 seconds to return its one variable.

So for example the code that runs to pull the first seen date, hours played, etc and return each on a line, looks like a 'ticker text' as each lines comes about 1-2 seconds after the other. which makes me think Im accessing it the wrong way.

is there a more efficient way of doing this?

 

Assuming the data is mostly constant and not changing A lot, then load the table in to hash maps or save a result set of all players so then search that for each player as they are needed. I don't think you can speed up the connection any more then you always have, so the only way to make it faster is to pre-load the data in to variables and work off them.

 

Ah ok, well it changes each time someone logs in and out for 'time played' so dont think I could do that.

Ill just take out the onjoin to display it to people. as it causes a slight lag to everyone when someone logs in pulling thier info.

 

Is your database hosted on the same server? If not, there will always be a slight lag in your connection. You should also try selecting only the fields you need from the database , so in this case "onlinetime" instead of all the fields in the table (*). It is also NOT advices to use back ticks around table and column names, unless they are reserved keywords, use of back ticks will actually slow the parsing down a little.

 

What he said, also, do you need to use a database? You could save playtime in a local yml file. Only use databases if you are going to store alot of data and/or want it tightly secured

 

You can still store the information to a hashmap, and update the data in the hashmap as each new player signs in or quits, and then save the hashmap information back to the database at a set interval. Then you don't have to deal with Database access each time someone joins, instead initial database load is done as the server is booting up, and the saves are in the background (and onDisable).

 

Its on the same host, only reason I'm using it is because Logblock already has all that stored in the format I need it (date is in mySQL format) and such.
Plus easier to keep persistent across maps, incase server folders get deleted or such. People are picky about making sure thier totalplaytime and age on the server never gets reset on map reset.

Can I be ignorant enough to ask what a 'back tick' is?
(edit: google-fu to find out what that referred to, removed them from the code now)

Think doing so to resave and such to the database is beyond my knowledge of SQL interfacing.
Trying to learn it as I go, but pulling info is as far as Ive made it
I might be in over my head using the technique, but seems to be the way I learn best...by doing something Im not ready to, and then get the 'well got to learn it now' mindset.

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

Well just keep it in the back of your mind as you learn more. I would put up an example if I was home, and If I remember I will tonight (thou I may forget... I don't have a great memory).

 

changing
"SELECT * FROM `lb-players` WHERE playername = '" + playera + "'"
to
"SELECT onlinetime FROM lb-players WHERE playername = '" + playera + "'"

seems to make exception errors, and I doing this wrong?

Thanks, you are the man!

trying to find good SQL tuts online, and bukkit SQL usage ones together, just confuses me more..there isnt a lot of overlap on the 2 (from a new guys standpoint) that seems to match up in any usable way

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

It seems valid, but I don't have the ability to do any testing till I get home.

 

no worries, Ill keep poking at it

 

What are the exceptions you are getting?

 

In your query you are only selecting the onlinetime column, not the playername column. So you can 'see' the onlinetime but as far as the result set is concerned the column playername does not exist. Try

Code:

"SELECT playername FROM lb-players WHERE playername='" + playera + "';";

That will load all the playernames and return the player name that is 'playera'.

 

That makes no sense and all, and is not required. You can use a where clause on fields that you do not select.

What I think that his issue more likely is, is the "-" in the table name. This is not entirely supported by MySQL, I missed that - when I suggested not to use back ticks, but as you are using that "-" it is required to use back ticks to "combine" it, currently MySQL is attempting to subtract "players" from "lb", and that is not really possible.

[edit]
I say not entirely because, while it is possible with back ticks, it is actually bad practice to use the - in table names. If you absolutely need to split table names, then use a _ instead.

[edit 2]
Why it makes no sense at all: Even though you already have the players name in the var "playera", you actually want to pass it to the database to obtain that players name? Which will return in this case the exact same name as the var "playera" already holds.

 

But surely that doesn't make any sense :S

The way I understand SQL queries is that with that query the whole playername column would be returned and then a where clause could pickout results from a smaller data set. If you have only selected that column then I didn't/don't think it would be able to read data from other columns without a new query.

Just to be clear I'm not trying to argue, just understand. I have moderate experience in SQL, I am by no means an expert but I have a strong basic understanding

 

Yea, I didn't make the LB table names, got to work with what logblock uses..so stuck with the '-' in the name.

and if thats the wrong query with the playername, what would be the correct one?
Thought with that it was calling the row named whatever playera was, and pulling the column 'onlinetime' from it.

 

Adamki11s, What your query does is return only the entries that match the where clause not the entire playernames column. It is also like I said not needed to select the fields that you are using in the where clause, orderby clause or groupby clause.

dxwarlock, The query that you should use would be:

Code:

"SELECT onlinetime FROM `lb-players` WHERE playername = '" + playera + "'"

 

that seems to work ok
still trying to figure out how to pull more than one return value per query, as I think thats also whats slowing me down..
I got one seperate command Public strings for:

Code:java

 
public String LBtime(String playera)
    {
        ResultSet rs = this.mysql.query("SELECT onlinetime FROM `lb-players` WHERE playername = '" + playera + "'");
        try
        {
            rs.first();
            String LBtime = rs.getString("onlinetime");
            return LBtime;
        }
        catch (SQLException e) {
        }return "Error";
    }
    //============================================
    public Date LBJoin (String playera)
    {
        ResultSet rs = this.mysql.query("SELECT firstlogin FROM `lb-players` WHERE playername = '" + playera + "'");
        try
        {
            rs.first();
            Date LBtime = rs.getDate("firstlogin");
            return LBtime;
        }
        catch (SQLException e) {
        }
        return new Date();
    }
   //============================================
    public Date LBJoin (String playera)
    {
        ResultSet rs = this.mysql.query("SELECT lastlogin FROM `lb-players` WHERE playername = '" + playera + "'");
        try
        {
            rs.first();
            Date LBtime = rs.getDate("lastlogin");
            return LBLtime;
        }
        catch (SQLException e) {
        }
        return new Date();
    }
 

seems a round about way to do it, query the database 3 seperate times with 3 public strings like above for info all in the same row.

 

simply group the fields with a comma! so: firstlogin, lastlogin, onlinetime

 

Hmm didnt think of that,

but how would I return that to a useable format to what called it? since in the public string I can only return one variable, and one of them is a string, and the other 2 are dates?

Am i making sense?

Like right now Im using
Date join = LBJoin(player);

and that Public date returns the date from SQL

and
String LB = LBtime(player );
that Public string returns the string from SQL

how would I combine the 3 above into one to call on?

 

return a List<String> instead, and then use a loop to pass through the results, or as in this case you know it's length, simply use: List<String> data = LBtime("SomeRandomPlayerWhoJustStoleMyCookie");
player.sendMessage(data.get(1));
player.sendMessage(data.get(2));
player.sendMessage(data.get(3));

 

ahh, good thing there is better java/SQL people than me on here.
I'd have spent 6 hours fighting it to get it to do that, and in a LOT longer and complicated way.

edit: wait, since 1 is a String, and the other 2 are 'Date', will that still work?

 

Trust me, when I started SQL 7 years ago, I too went through about the same route you are going through now... (Though admittedly, I learned using SQL in PHP not in Java hehe)

 

I understand that but wouldn't it pull only data from the column specified and then filter that for the where clause?

 

Nop, that it won't. What a query with a where clause does is pretty much this:
- connects to server
- tells server it wants to receive the following columns fieldX, fieldY and fieldQ from table wtf_bbq_sauce
- tells the server to only return the entries for which fieldZ is equal to "banana"
- the server then goes through all the entries in the given table and matches the value of fieldZ to banana, if it matches, the columns in the select will be added to the return, if not the columns in the select will be skipped.(*)
- the server then returns the data, and you can start working with it!

(*)This is actually quite resource intensive on larger tables, and can slow the query down alot. To solve this, indexes are added to certain fields, which are used as keys. In this case, such an index should be on the table "playernames". What an index allows the server to do, is pretty much sort the data by that column. So if it's a string, it can then order it alphabetically, and just select the parts which start off with [xxx].
This however, can still be quite resource intensive, consider having 500.000 users, whose name all start with a "p". And to counter this, data is usually cached. But on a small scale, caching does not totally pay off. And might be kind of hard to implement if you do not know what you are doing.

 

Ah thanks that clears up a lot

 

No problem Glad I was able to be of help!