none
C# database querying and data structures RRS feed

  • Question

  • I'm making a reusable class to (among other things) fetch database results from a given query.

    Here's what I have:
     public string[,] MySQLexecuteQuery(string query)
            {
                MySqlConnection con = new MySqlConnection();
                con.ConnectionString = "Database=wdb;Data Source=127.0.0.1;User Id=root;Password=pass;";
               
                MySqlCommand cmd = con.CreateCommand();
                cmd.CommandText = query;
                con.Open();
                MySqlDataReader reader = cmd.ExecuteReader();
                int rows = 0;
                while (reader.Read())
                {
                    rows += 1;
                }
                string[,] results = new string[rows,reader.FieldCount];
                reader.Close();
                reader = cmd.ExecuteReader();
                rows = 0;
                    while(reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                            results[rows, i] = readerIdea.ToString();
                        rows += 1;
                    }

                reader.Close();
                con.Close();
                return results;
            }


    The usage is like this (messy but it works):
                string[,] myArray = new string[database.MySQLexecuteQuery("SELECT * FROM table").GetUpperBound(0), database.MySQLexecuteQuery("SELECT * FROM table").GetUpperBound(1)];
                myArray = database.MySQLexecuteQuery("SELECT * FROM table");
                MessageBox.Show(myArray[1000,1]);


    It works on a matrix-like coordinate system (row by column), my question is how can I make this faster. I know arrays aren't the best way to handle this so any suggestions are most certainly welcome.
    Sunday, March 23, 2008 10:38 PM

Answers

  • I'm not familiar with MySql performance characteristics, but it looks like your biggest problem may be that you're executing the query six separate times in this example (twice during MySqlExecuteQuery, which is then called three times). I'd suggest using a structure for which you don't need to determine the number of rows before filling with data.

    Have you considered using a DataSet instead of array? It's unlikely to be faster than a sinlge array, but makes it simple to load data from a command in one go, and has a number of convenience abilities for manipulating the results (like binding to UI, searching, etc).

    There are several samples / frameworks around that will do much of this work for you (such as the Data Access Application Block).
    Monday, March 24, 2008 7:51 PM

All replies

  • All of the conceivable performance gains you might get by declaring your results as a static array are lost by the fact that you're executing the query and reading through its result set twice:  once to find out how many rows there are, and once to actually get the rows.

     

    The obvious way to make it faster:  only read the results once:

    Code Snippet

     

    List<string[]> results = new List<string[]>();

    MySqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())

    {

       string[] row = new string[reader.FieldCount];

       for (int i = 0; i < reader.FieldCount; i++)

       {

          row[i] = reader.GetString(i);

       }

       results.Add(row);

    }

     

     

     

     

    Monday, March 24, 2008 7:31 PM
  • I'm not familiar with MySql performance characteristics, but it looks like your biggest problem may be that you're executing the query six separate times in this example (twice during MySqlExecuteQuery, which is then called three times). I'd suggest using a structure for which you don't need to determine the number of rows before filling with data.

    Have you considered using a DataSet instead of array? It's unlikely to be faster than a sinlge array, but makes it simple to load data from a command in one go, and has a number of convenience abilities for manipulating the results (like binding to UI, searching, etc).

    There are several samples / frameworks around that will do much of this work for you (such as the Data Access Application Block).
    Monday, March 24, 2008 7:51 PM