Hey guys.
So, my project (simple enough, but extended due to Blender bugs and whatnot) is coming along. Release is next week and I have a few questions that you might be able to fill in for me:
The bulk of the app I’m creating is about posting text messages on billboards over a city. The database handling is straightforward; at startup it checks every sign for any pre-written content stored in a PHP/MySQL configuration based on the Server Side Highscores thingy from the wiki. Nowadays, mostly it works, however I did get some first-time “Couldn’t resove host” message. Also, just now, I got a “couldn’t connect to host” warning. The amount of signs doing their own individual lookup at start is 60 right now, with a total number of probably 500 in the end. The app has crashed a couple of times with Unity reporting low memory/out of memory (I have 8GB in my computer).
I’m not well versed in databases, but I’m hopefully not an idiot either. Is the memory drain due to non-optimal database accessing? In that case, would it be smarter to time every 20 or so signs to grab their needed info with a 1-2 second delay? I cannot say how many will use this app simultaneously, but it will be a local “art” feature during a festival here next week so I expect quite some push.
If you have any other suggestions, I would greatly appreciate them, since my time is running out. Thanks.
To clarify, all texts are capped at 140 characters max (just like Twitter) so no obese, excessive amounts per text area/sign. Also, the “Couldn’t resolve host” issue came up just today as well. Worrying that something is wrong with the connection or something, or that I would need to leave my phpMyAdmin on the server run in an open window or something like that. No idea. You?
When I first started reading and you said “sign” I was thinking you were storing textures as BLOB’s or something. Maybe I’m missing something, but there’s no way 60 web queries for character strings can result in an OOM condition.
I think it would be beneficial if you were to post some relevant code. It sounds like you might be constantly creating new strings in update() or a coroutine.
If the url is correct, then that is an error with your local network. It indicates that your isp’s DNS server is having some issues. If it continues, you should considering switching your local network configuration to use one of the myriad of public dns servers.
I would suggest something along the lines of a random time delay instead of fixed updates. Just establish a minimum and maximum sign refresh delay.
Although I doubt 60 web queries will give you an OOM, applications are typically capped at 2 gig for running room. If you are creating an open connection, then a reader to read the data, followed by a close reader then close connection, you will be fine. However, without seeing your database connection / reading routine, it is impossible for anyone to help you.
Rule of thumb with regards to databases, you will want to use a “using” statement, this way when your routine finishes, it self maintains the connection, an example
(this example assumes ODBC, you might be using ADO or something else)
using (OdbcConnection oCon = new OdbcConnection(sCon))
{
oCon.Open();
}
When that executes, the connection will open and subsequently closes. If all you are doing is reading from a database, use a datareader, not a dataset or dataview.
Further example:
C# of course
using (OdbcConnection oCon = new OdbcConnection(sCon))
{
oCon.Open();
try
{
OdbcDataReader ordr = null;
using (OdbcCommand ocmd = new OdbcCommand(qry, oCon))
{
ordr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);
if (ordr.HasRows)
{
while (ordr.Read())
{
// populate your billboards
}
}
ordr.Close();
ordr.Dispose();
}
}
catch(Exception ex)
{
}
}
Of course qry is your SQL query, ocon is your connection object, scon is your connection string.
Post examples like this so we can see what might be going on, maybe it has nothing to do with the database at all.
OK. Thanks for your responses, and sorry for being a bit late with replying. I’m quite sure that some of zumwalt’s ideas are probably a bit more hardcore than what is needed, and definitely more so than what my working solution is at the moment. Regarding the update loop, I only have one, and it runs nothing that should have to do with text or anything like that.
So the way it works is: (as I said, all based off of the serverside highscores deal)
Sign-shaped mesh with a text-prefab game object. Prefab contains mesh renderer, text mesh, and a text script. Text script is messy as hell, so I chopped some bits out:
// Script for getting and securing texts
var typable : boolean;
private var containedString : String;
var location : String;
//HSController block
var addScoreUrl="…addscore.php?";
var highscoreUrl="…display.php";
var www = new WWW(highscoreUrl);
function Start() {
location = this.transform.parent.name;
//getText(); // This is what kills it if turned on with larger number of text meshes (billboard signs with text mesh)
//this.GetComponent(TextMesh).text = "";
if (containedString == "") {
typable = true;
}
else typable = true; // Exception case until later: return to false
}
function getText() {
//print(this.transform.parent.name);
var form = new WWWForm();
form.AddField("location", location);
var hs_get = new WWW(highscoreUrl, form);
//hs_get = WWW(highscoreUrl);
yield www;
yield hs_get;
if(hs_get.error) {
print("There was an error getting database info: " + hs_get.error);
} else {
//this.gameObject.GetComponentInChildren(TextMesh).text = hs_get.data;
this.gameObject.GetComponentInChildren(TextMesh).text = hs_get.data;
}
}
Player is supposed to get close to sign, click, and type their text string (max 140 chars) if it is vacant. Database consists of autogenerated ID, text field, location field. As the code says, currently I am making an exception case so I can always overwrite. On submitting (clicking the corresponding button) the following line of code (and some more, though irrelevant right now) are executed inside a submit function:
function postText(text, location) {
var hash=md5functions.Md5Sum(text + location + secretKey);
var highscore_url = addScoreUrl + "text=" + WWW.EscapeURL(text) + "&location=" + WWW.EscapeURL(location) + "&hash=" + hash;
//var highscore_url = addScoreUrl + "name=" + WWW.EscapeURL(name) + "&score=" + score + "&hash=" + hash;
hs_post = WWW(highscore_url);
yield hs_post;
if(hs_post.error) {
print("There was an error posting the high score: " + hs_post.error);
}
}
This has worked perfectly for 20’ish signs. Now that I am in dire need of expanding to more signs the problems pop up. I am looking into creating a trigger that runs getText on all child signs when in proximity, though stupid as I am, I have not gotten the loop or similar to work as such (self-taught, bedroom programmer). It does however work on a 1:1 basis, so in principle it should work, as soon as I get that code (for -all- child objects with a certain name, or something) right.
Have you checked the database table(s) to check that the same message record isn’t being added more than once? Perhaps you can post the OnGUI function you are using to submit the text?