A Game Company in Trouble

[BlackHawke]

My Name is Nick Soutter, I am the owner of a small game company, Aepox Games
(We're in the middle of a name change from "Lamar Games"),
www.lamargames.net.



Our first commercial game, Andromeda Online (www.andromedaonline.net) is
going into beta soon. It runs on an evaluation edition of SQL Server 2000
(our intention is, when it launches, we earn the money to buy a copy before
the evaluation expires).



We have been testing Andromeda Online, and found that saves to the database
take about 10 seconds (we were anticipating less than 1). We felt we need
somebody experienced in optimizing sql databases to help us optimize the
database, and get it running in the best method for our particular
application.



Our program accesses the database in Java, and people with understanding in
how to optimize java connections would be a tremendous help.



My company is small, and we honestly cant afford much. Everybody on this
project, from the sound guys to the graphic artist, has worked for 1/10 to
1/100 of the value of the job. We're simply a starting company looking for
dedicated people who are willing to work more for credit than money.



We can offer credit on our website
(http://www.andromedaonline.net/credits.html) to anybody who helps us, but
little more (maybe $100, but we're very over budget, and in desperate need
of help). Because of how we intend the game to run (with maybe 100-200
concurrent games running online), a 10 second save time is simply
unacceptable.



Anybody who would be willing to help us, please send a resume to
(e-mail address removed). Experience would be nice, but not a requirement.
We're looking for someone who can talk with our programmer about the types
of calls made to our SQL database, and then can log into the DB and optimize
it to run as fast as possible considering our specific needs.



Thank you for your time.



Nick Soutter

Aepox (Lamar) Games

 

[Chris Smith]

We have been testing Andromeda Online, and found that saves to the database
take about 10 seconds (we were anticipating less than 1). We felt we need
somebody experienced in optimizing sql databases to help us optimize the
database, and get it running in the best method for our particular
application.



Our program accesses the database in Java, and people with understanding in
how to optimize java connections would be a tremendous help.

Some simple advice, without knowing anything about your application.
It's highly unlikely that the problem is related to your use of Java-
based interfaces to the database. The problem is almost certainly with
the SQL that you are running, and is independent of the means of issuing
that SQL to the database itself. An exception applies, though, if
you're using some high-level abstraction like an OR mapper that
generates the SQL for you. Is this hand-written JDBC and SQL, or
something more complex?

Anybody who would be willing to help us, please send a resume to
(e-mail address removed). Experience would be nice, but not a requirement.
We're looking for someone who can talk with our programmer about the types
of calls made to our SQL database, and then can log into the DB and optimize
it to run as fast as possible considering our specific needs.

Given your financial constraints, it would perhaps be more prudent for
you to take advantage of the advice freely offered on this newsgroup.
You'd be expected to take part in finding the solution, by for example
putting together test cases and doing some troubleshooting, but if you
have a real question about a factual or difficult issue, someone will
almost certainly jump in to help.

A good place to start is to look over the statements that are issued in
a save. Many databases have a logging mode that can capture this info,
but if your database doesn't have such a mode or flag, you could do it
from the application or with a filter JDBC driver. So to start with the
easiest solution, which database are you using?

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Erland Sommarskog]

Our first commercial game, Andromeda Online (www.andromedaonline.net) is
going into beta soon. It runs on an evaluation edition of SQL Server
2000 (our intention is, when it launches, we earn the money to buy a
copy before the evaluation expires).

When did you download this evaluation edition? Beware that the original
evaluation edition is vulnerable for the Slammer worm. If you go to
http://www.microsoft.com/sql you can obtain a version of the Evaluation
Edition that is Slammer-safe.

We have been testing Andromeda Online, and found that saves to the
database take about 10 seconds (we were anticipating less than 1). We
felt we need somebody experienced in optimizing sql databases to help us
optimize the database, and get it running in the best method for our
particular application.

What exactly are you saving? Does it take 10 seconds to save a single
row to the database? Or does it take 10 seconds to save a player's
entire game?

The latter could mean a whole lot calls to SQL Server to insert data.
If all operations are through INSERT statements sent from the Java
code, there is a whole lot to win by using stored procedures. For even
higher speed, you could construct an XML document, and then unpack that on
the SQL Server side with OPENXML(). You save a lot of network roundtrips
that way.

 

[Chris Smith]

I suspect that most of the problem lies in how our database is configured.
Knowing nothing about configuring these things, I set it up in the default
settings. I've boosted the priority, and that helped, but I know nothing
about enhancing the database response time.

We are using MS SQL Server 2000... Is that what you meant by what database
are we using? As far as how the JAVA code interacts with it, I'd have to ask
the programmer.

Okay, please do ask the programmer to make an appearance here; there's
no reason to work through a level of indirection.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Ed Yu]

What database are you using Oracle/MSSQL/MySQL/Postgresql and the platform
NT/XP/Linux/BSD? Without knowing that there is no way to make any
recommendations regarding performance tunning.

 

[BlackHawke]

If I failed to mention it earlier, SQL 2000, Evaluation Edition, on Windows
2003 Server, Eval Edition.

I'll ask the programmer to make an appearance ASAP.

Nick

 

[Ed Yu]

While you may suggest the obvious such as indexing the proper columns, you
need to know much more about the system as a whole to even begin suggesting
scenerio that could be the bottleneck. In fact, even if you have follow all
the best RDBMS best practice (including tunning SQL by generating execution
plans), you may have chocked up your system if you are not careful about
creating large objects on the java end or misconfigurating the connection
pool. So, with that said, we really need to begin at point zero, which in
this case is:

1) Platform - Win2K
2) Database - MSSQL 2000 Eval
3) Java - ??? a web application I assume, it is JSP? Servlet? Using any type
of framework Struts/WebWorks?
4) Web server - ??? Apache/IIS/Tomcat?
5) Application Server - ??? Tomcat/Weblogic/Websphere?
6) Physical system configuration - machine A (web server), machine B
(application server), machine C (database server)
7) Client - ??? Http client/Applet/C/C++ clients?
8) Protocol - ??? HTTP/HTTPS/Socket?

 

[DiscoStu]

1) Platform - Win2K

2) Database - MSSQL 2000 Eval
3) Java - ??? a web application I assume, it is JSP? Servlet? Using any type
of framework Struts/WebWorks?
4) Web server - ??? Apache/IIS/Tomcat?
5) Application Server - ??? Tomcat/Weblogic/Websphere?
6) Physical system configuration - machine A (web server), machine B
(application server), machine C (database server)
7) Client - ??? Http client/Applet/C/C++ clients?
8) Protocol - ??? HTTP/HTTPS/Socket?

Hello Everyone, I'm the lead programmer on this project. Thanks ahead
of time for any-and-all good advice coming from you fine folks
Well, this is a standard java application, no web-server involved. Im
using microsofts JDBC4 SQLServer driver for my connection to the
database, no ODBC (ugh!) involved. Also, Im using the apache commons
connection pool classes to handle all of the database connection
pooling as well as the player execution thread pooling.


I dont think we have any indexes setup on our tables just yet. Most of
the tables have the primary key set to an identity column I call
"IndexCol". The important tables have this ID column as well as a
"GameID", "PlayerID", "ShipID" fields to determin exactly which object
this row of data corresponds to. Do I want to setup the indexs on
these three columns?

The speed problems we see currently, arent on the SELECT end of the
spectrum, they are on the INSERT parts. I am using prepared statements
to "INSERT" the data row-by-row into the database. Im not using batch
mode at the moment. I've tried to make the tables as lean as possible,
factoring out columns into other tables where possible. The main ship
table is pretty large, maybe 100 columns of int/smallint/bit data, no
text. Right now, to update the game specifics, I delete all the data
in that table for the specific game, then I use the prepared
statements to re-insert the updated data. I dont want to use "UPDATE"
sql because some records aren't restored, thats why I like the
delete/insert combo.

If theres any other info I can send, please let me know.

Thanks, Greg.

 

[Erland Sommarskog]

[Followups trimmed to comp.databases.ms-sqlserver, as this is the only
relevant group.]

The speed problems we see currently, arent on the SELECT end of the
spectrum, they are on the INSERT parts. I am using prepared statements
to "INSERT" the data row-by-row into the database. Im not using batch
mode at the moment. I've tried to make the tables as lean as possible,
factoring out columns into other tables where possible. The main ship
table is pretty large, maybe 100 columns of int/smallint/bit data, no
text. Right now, to update the game specifics, I delete all the data
in that table for the specific game, then I use the prepared
statements to re-insert the updated data. I dont want to use "UPDATE"
sql because some records aren't restored, thats why I like the
delete/insert combo.

So, how many rows do insert when it takes 10 seconds to save a game?

The obvious improvement I see is to use stored procedures instead. If
that doesn't help, package data in an XML document and send this to
SQL Server, and then write a stored procedure that unpacks it with
OPENXML. Or use bulk-copy routines to load the data, but I have no
idea to do that from JDBC.

 

[Ed Yu]

Without considering the physical server hardware and network configurations,
looks like you need a cluster index (not primary key) on the GAMEID column.
Since you do not have any index on this column, everytime someone starts the
same, you are doing a full table scan to locate and remove rows associated
with the GAMEID.

Looks like you need to read a little more about RDBMS application
programming and best pratices. BTW, I think for MSSQL, at least you need to
place the data files and log files on to seperate hardware (ie. disks
preferably on different controllers).

In addition, I am a little uneasy about using RDBMS for games since SQL
engines, IMHO, got too much overhead for a gaming application.

 

[Erland Sommarskog]

Without considering the physical server hardware and network
configurations, looks like you need a cluster index (not primary key) on
the GAMEID column. Since you do not have any index on this column,
everytime someone starts the same, you are doing a full table scan to
locate and remove rows associated with the GAMEID.

Since the access problems were with saving, indexes are not so much of
an issue, although we don't know what the statements look like. INSERT
VALUES does not need any indexes, unless there is some trigger involved.
True, though, DiscoStu is deleting all data for a game first, and that
DELETE could use an index. I would suspect, however, that at this point
he don't have that much data in the database yet, so he has not yet
arrived at problems where indexes are the solution.

Looks like you need to read a little more about RDBMS application
programming and best pratices. BTW, I think for MSSQL, at least you need
to place the data files and log files on to seperate hardware (ie. disks
preferably on different controllers).

Undoubtedly this can be good for performance, but there are many systems
out there that don't have this configuration and still have good
performance. I don't think this is the right place to start.