Database Options

Spirited · 08/31/2011, 19:06

Hey ... so here it is again.
The hotly discussed topic of "What database should we be using?".

Now, I'm not going to try something I'm not comfortable with, but that leaves me with a lot of options...
and I'd really like help with my decision.

1. MySql - by far one of my favorites. Last time I checked, it took 150 milliseconds to read an entire record though. That's what's making me what to change.

2. MsSql - free on Dreamspark for me. I don't know how fast it is but I can download the Developer edition of Microsoft SQL. I'm comfortable in it too.

3. Local SQL - using an sdf database. I don't know much about the speed so I'm concerned about it's limitations. I know LOTF used it (which is concerning).

Those are the options that I have come up with.
Please if you have alternatives - let me know about them!
I'm currently using an object oriented, xml mapping sql system.

Has anyone done millisecond tests?
Thanks.

Sincerely,
Fang

Arco. · 08/31/2011, 19:25

No ini?

InfamousNoone · 08/31/2011, 19:52

Quote:

Originally Posted by Fаng

Hey ... so here it is again.
The hotly discussed topic of "What database should we be using?".

Now, I'm not going to try something I'm not comfortable with, but that leaves me with a lot of options...
and I'd really like help with my decision.

1. MySql - by far one of my favorites. Last time I checked, it took 150 milliseconds to read an entire record though. That's what's making me what to change.

2. MsSql - free on Dreamspark for me. I don't know how fast it is but I can download the Developer edition of Microsoft SQL. I'm comfortable in it too.

3. Local SQL - using an sdf database. I don't know much about the speed so I'm concerned about it's limitations. I know LOTF used it (which is concerning).

Those are the options that I have come up with.
Please if you have alternatives - let me know about them!
I'm currently using an object oriented, xml mapping sql system.

Has anyone done millisecond tests?
Thanks.

Sincerely,
Fang

MySQL is a nice option, but the .NET wrapper for it is trash.
MSSQL is goes along quite nicely with .NET
SQLITE goes quite well for loading the cached information (items, spells, etc)

I haven't messed too much with SDF's.

~~BaussHacker~~ · 08/31/2011, 20:04

Quote:

Originally Posted by Arco.

No ini?

Why would he use Ini, if he prefers SQL?

InfamousNoone · 08/31/2011, 20:08

Quote:

Originally Posted by BaussHacker

Why would he use Ini, if he prefers SQL?

I have a library I wrote that makes SQL-queries compatible with inis

. Well, on my old computer anyways. Fang uses stored procedures anyways though.

~~BaussHacker~~ · 08/31/2011, 20:12

Quote:

Originally Posted by InfamousNoone

I have a library I wrote that makes SQL-queries compatible with inis . Well, on my old computer anyways. Fang uses stored procedures anyways though.

That's what you are using in your new source right?

Btw. not against ini, using it myself

InfamousNoone · 08/31/2011, 23:12

Quote:

Originally Posted by BaussHacker

That's what you are using in your new source right?

Btw. not against ini, using it myself

Naw, using SQL-queries would be a tad pointless when you can just *code* the logic. Coding the logic should always be faster than processing a query-string. I developed the DBFile system literally a week before I started working on my server, lol. It just so happens it uses the same format as and INI file because they're quite nicely organized.

Spirited · 08/31/2011, 23:59

Yah, I'm currently using the "drivers" from the MySql Community Server Wrapper. I'm guessing that's the **** one you're talking about Roy... and yah, I agree with you. It is problematic. I might just go with MsSql then (now that Navicat supports it too).

And with ini... I never saw that as a legitimate database tool. It's great for loading configurations and such... but I've never seen it work well with high volumes of database information. Please correct me if I'm wrong- but I've heard that it's much slower than Sql (unless you do something out of the norm like using a kernel32.dll import... I don't have any proof that that's bad).

MsSql looks promising then. It's 4GBs though. Yikes.

That's what I have available to download.

~~BaussHacker~~ · 09/01/2011, 00:06

Quote:

Originally Posted by InfamousNoone

Naw, using SQL-queries would be a tad pointless when you can just *code* the logic. Coding the logic should always be faster than processing a query-string. I developed the DBFile system literally a week before I started working on my server, lol. It just so happens it uses the same format as and INI file because they're quite nicely organized.

Oh, didn't look at it like that.

InfamousNoone · 09/01/2011, 01:48

Quote:

Originally Posted by Fаng

Yah, I'm currently using the "drivers" from the MySql Community Server Wrapper. I'm guessing that's the **** one you're talking about Roy... and yah, I agree with you. It is problematic. I might just go with MsSql then (now that Navicat supports it too).

And with ini... I never saw that as a legitimate database tool. It's great for loading configurations and such... but I've never seen it work well with high volumes of database information. Please correct me if I'm wrong- but I've heard that it's much slower than Sql (unless you do something out of the norm like using a kernel32.dll import... I don't have any proof that that's bad).

MsSql looks promising then. It's 4GBs though. Yikes.

That's what I have available to download.

If whenever you have your server set up you'd like to compare how long it takes us to load say item/spell stats or even a character (character is some what unfair as I, or you may be loading more data than the other depending on whats implemented) we can check out the speeds.

Korvacs · 09/01/2011, 15:22

To be honest things like item stats and spell stats shouldn't be put into the Sql Database anyway, it rarely changes if ever so loading that sort of data would most likely be slower through the Sql Database. Although with cached lookup who knows.

You should test loading a characters inventory, the number of values shouldn't differ that greatly..

Spirited · 09/02/2011, 01:52

Quote:

Originally Posted by InfamousNoone

If whenever you have your server set up you'd like to compare how long it takes us to load say item/spell stats or even a character (character is some what unfair as I, or you may be loading more data than the other depending on whats implemented) we can check out the speeds.

We would have to put them on the same machine for accurate testing. I'm pretty sure our hard disks/ram/cpu all run at different speeds. I'm starting to doubt the use of sql.

Quote:

Originally Posted by Korvacs

To be honest things like item stats and spell stats shouldn't be put into the Sql Database anyway, it rarely changes if ever so loading that sort of data would most likely be slower through the Sql Database. Although with cached lookup who knows.

You should test loading a characters inventory, the number of values shouldn't differ that greatly..

I'll code a quick ini database system after I test with Sql. I might find that you're completely right (I wouldn't doubt it either). I wish that my database system wasn't so hard to change though. I'm still converting it over to MsSql. I might stop using NH because it's so annoying (and I doubt it's faster at all).

Any other suggestions?

xBlackPlagu3x · 09/08/2011, 07:09

Quote:

Originally Posted by Fаng

We would have to put them on the same machine for accurate testing. I'm pretty sure our hard disks/ram/cpu all run at different speeds. I'm starting to doubt the use of sql.

I'll code a quick ini database system after I test with Sql. I might find that you're completely right (I wouldn't doubt it either). I wish that my database system wasn't so hard to change though. I'm still converting it over to MsSql. I might stop using NH because it's so annoying (and I doubt it's faster at all).

Any other suggestions?

Can NEVER go wrong with flat-file. If I knew how, I would try out Jagex's "database" storage and use a cache system/flat-file.

I heard that the majority of games are now using MsSQL but I could've heard false information, who knows.

Spirited · 09/08/2011, 07:53

I'm using ini (I might switch back to mssql).
Sql takes 50 - 75 milliseconds to start while ini takes between 0 and 1 millisecond.
Ini is slightly faster as well at executing commands (there is no big difference though at all once it's working with stored procedures in sql).

~~BaussHacker~~ · 09/08/2011, 15:02

Quote:

Originally Posted by xBlackPlagu3x

Can NEVER go wrong with flat-file. If I knew how, I would try out Jagex's "database" storage and use a cache system/flat-file.

I heard that the majority of games are now using MsSQL but I could've heard false information, who knows.

MSSQL is awesome, here is a wrapper I did some time ago. I'm sure it could have been done a lot better.

Code:

    public class MssqlCommand
    {
        public MssqlCommand(string ConnectionString, string Table)
        {
            m_table = Table;
            m_constring = ConnectionString;
            Parameters = new List<MssqlParameter>();
        }
        private string m_table;
        private List<MssqlParameter> Parameters;
        private string m_constring;

        public MssqlCommand Insert()
        {
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO ");
            str.Append(m_table);
            str.Append(" (");
            foreach (MssqlParameter param in Parameters)
            {
                str.Append(param.Column);
                str.Append(", ");
            }
            str.Length -= 2;
            str.Append(") VALUES (");
            foreach (MssqlParameter param in Parameters)
            {
                str.Append("@");
                str.Append(param.Column);
                str.Append(", ");
            }
            str.Length -= 2;
            str.Append(")");
            string command = str.ToString();
            SqlConnection conn = new SqlConnection(m_constring);
            SqlCommand comm = new SqlCommand(command, conn);

            foreach (MssqlParameter param in Parameters)
            {
                comm.Parameters.Add("@" + param.Column, param.Type).Value = param.Value;
            }

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }

            return this;
        }
        public MssqlCommand Insert(MssqlParameter param)
        {
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO ");
            str.Append(m_table);
            str.Append(" (");
            str.Append(param.Column);
            str.Append(") VALUES (@");
            str.Append(param.Column);
            str.Append(")");

            string command = str.ToString();
            SqlConnection conn = new SqlConnection(m_constring);
            SqlCommand comm = new SqlCommand(command, conn);
            comm.Parameters.Add("@" + param.Column, param.Type).Value = param.Value;

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }

            return this;
        }
        public MssqlCommand Insert(string Column, object Value, System.Data.SqlDbType Type)
        {
            StringBuilder str = new StringBuilder();
            str.Append("INSERT INTO ");
            str.Append(m_table);
            str.Append(" (");
            str.Append(Column);
            str.Append(") VALUES (@");
            str.Append(Column);
            str.Append(")");

            string command = str.ToString();
            SqlConnection conn = new SqlConnection(m_constring);
            SqlCommand comm = new SqlCommand(command, conn);
            comm.Parameters.Add("@" + Column, Type).Value = Value;

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }

            return this;
        }

        public object Read(MssqlParameter Select, string Where, string Equal)
        {
            SqlConnection conn = new SqlConnection(m_constring);

            StringBuilder str = new StringBuilder();
            str.Append("SELECT ");
            str.Append(Select.Column);
            str.Append(" FROM ");
            str.Append(m_table);
            str.Append(" WHERE ");
            str.Append(Where);
            str.Append(" = ");
            str.Append(Equal);
            string command = str.ToString();
            SqlCommand comm = new SqlCommand(command, conn);

            object Ret = null;

            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();

                Ret = reader[Select.Column];

                reader.Close();
            }
            conn.Close();

            return Ret;
        }
        public object Read(string Select, string Where, string Equal)
        {
            SqlConnection conn = new SqlConnection(m_constring);

            StringBuilder str = new StringBuilder();
            str.Append("SELECT ");
            str.Append(Select);
            str.Append(" FROM ");
            str.Append(m_table);
            str.Append(" WHERE ");
            str.Append(Where);
            str.Append(" = ");
            str.Append(Equal);
            string command = str.ToString();
            SqlCommand comm = new SqlCommand(command, conn);

            object Ret = null;

            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();

            if (reader.HasRows)
            {
                reader.Read();

                Ret = reader[Select];

                reader.Close();
            }
            conn.Close();

            return Ret;
        }

        public MssqlCommand Update(MssqlParameter param, string Where, string Equal)
        {
            StringBuilder str = new StringBuilder();
            str.Append("UPDATE ");
            str.Append(m_table);
            str.Append(" SET ");
            str.Append(param.Column);
            str.Append(" = @");
            str.Append(param.Column);
            str.Append(" WHERE ");
            str.Append(Where);
            str.Append(" = ");
            str.Append(Equal);

            string command = str.ToString();
            SqlConnection conn = new SqlConnection(m_constring);
            SqlCommand comm = new SqlCommand(command, conn);
            comm.Parameters.Add("@" + param.Column, param.Type).Value = param.Value;

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }

            return this;
        }
        public MssqlCommand Update(string Column, object Value, System.Data.SqlDbType Type, string Where, string Equal)
        {
            StringBuilder str = new StringBuilder();
            str.Append("UPDATE ");
            str.Append(m_table);
            str.Append(" SET ");
            str.Append(Column);
            str.Append(" = @");
            str.Append(Column);
            str.Append(" WHERE ");
            str.Append(Where);
            str.Append(" = ");
            str.Append(Equal);

            string command = str.ToString();
            SqlConnection conn = new SqlConnection(m_constring);
            SqlCommand comm = new SqlCommand(command, conn);
            comm.Parameters.Add("@" + Column, Type).Value = Value;

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }

            return this;
        }

        public void AddParameter(string _Column, object _Value, System.Data.SqlDbType _Type)
        {
            Parameters.Add(new MssqlParameter()
            {
                Value = _Value,
                Type = _Type,
                Column = _Column
            });
        }
    }

Code:

    public class MssqlParameter
    {
        public object Value;
        public System.Data.SqlDbType Type;
        public string Column;
    }