0

I have to get a set of data from a database table that looks like this:

DataTable: 
id
data
phonenumber
date

and

PhoneGroup:
id
groupid
phonenumber

The first table, DataTable contains around 4000 numbers * number of date

The second table, PhoneGroup, will get all phonenumber from a groupID.

The program is a simple one where the user selects a start date and a end date and a groupid, then the program will get all the data of all the phone number in that group which will display all data in a table.

phonenumber 1AUG 2AUG 3AUG 4AUG 5AUG
11111       100  121  131  145  156 
22222       141  151  171  185  196 
33333       112  121  131  145  156 

The user must also have the ability to get data from the subset of the queried data.

Phone number 22222 used 34MB of data from 2AUG to 4AUG

My first approach is to use a nested dictionary:

Dictionary<phonenumber, Dictionary<datetime, data>>

The code to fill this dictionary is 2 sql loops, one for phone number, one for date with data

Tested it for 1 month of data with 4000 numbers which took a really long time.

Now my question is, is there any better way to store these data where the user still have the ability to get the total data from the subset of this data, eg, 2AUG to 4AUG for number 22222

Edit: Added query code

string sqlcommand =
          @"SELECT PhoneNo
            FROM phonegroup
            WHERE groupid=@groupid ORDER BY PhoneNo ASC";

    Dictionary<string, int> phoneList = new Dictionary<string, int>(); // not in use yet

    using (SqlConnection con = new SqlConnection(strConnectionString))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand(sqlcommand, con))
        {
            cmd.Parameters.AddWithValue("@groupid", groupid);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    string phonenumber = reader["PhoneNo"].ToString();

                    Literal_Results.Text += "<tr><td class='zui-sticky-col'>" + phonenumber + "</td>";
                    int datesize = cycleDateList.Count;

                    for (int i = 0; i < cycleDateList.Count; i++)
                    {
                        string sqlcommand2 =
                                    @"SELECT data
                                        FROM dataTable
                                        WHERE phonenumber=@phonenumber AND date_display=@date_display";

                        using (SqlConnection con2 = new SqlConnection(strConnectionString2))
                        {
                            con2.Open();
                            using (SqlCommand cmd2 = new SqlCommand(sqlcommand2, con2))
                            {
                                cmd2.Parameters.AddWithValue("@phonenumber", phonenumber);
                                cmd2.Parameters.AddWithValue("@date_display", cycleDateList[i].Date);

                                using (SqlDataReader reader2 = cmd2.ExecuteReader())
                                {

                                    if (reader2.Read())
                                    {
                                        Literal_Results.Text += "<td>" + float.Parse(reader2["data"].ToString()).ToString("0.##") + "</td>";
                                        datesize--;
                                    }

                                }
                            }
                            con2.Close();
                        }
                    }

                    for (int u = 0; u < datesize; u++)
                    {
                        Literal_Results.Text += "<td>" + "NIL" + "</td>";
                    }

                    Literal_Results.Text += "</tr>";
                }
            }
        }
        con.Close();
    }
    // Get Phone Number End

    Literal_Results.Text += "</table>";
LuxuryWaffles
  • 1,518
  • 4
  • 27
  • 50
  • Does it take long to fill the data in or does it take long to execute queries on this data. In the first case, add your code. Is it sample/random data or actual data? Running 4000*30 queries is slower than doing several at once, have a look e.g. [here](https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql). In the latter case, please show (or add) indexes of your tables, your query and the explain output. Also: if every number has a group, you could use "phonenumber, grouid" as your second table (no id); or the phonegroups-id in the datatable instead of the phonenumber. – Solarflare Aug 31 '17 at 10:46
  • @Solarflare Hi, thanks for the reply. the long time is from executing of the queries. To try speeding it out, i have removed the dictionary part temporary to display the table in the sql query. and numbers can have more than 1 group, and some numbers do not have groups (not added yet), I have updated the post with the query codes too – LuxuryWaffles Aug 31 '17 at 11:02
  • @Solarflare Also, will lookup the "running several" query at once part. might be what i need – LuxuryWaffles Aug 31 '17 at 11:05
  • Not several queries, but several rows. You read every row, 1 row per query, and evaluate it in your dictionary. That's not the point of a database (a textfile would be faster). You may want to have a look at a sql tutorial. Try `select sum(data), phonennumber, date from Datatable group by phonennumber, date` and evaluate/display that (use the workbench/phpmyadmin to see the output). Not sure where the groupid comes in here (it's no part of your sample result), but you can use `join PhoneGroup on PhoneGroup.phonenumber = Datatable.phonennumer` to include it. Indexes will speed up your queries. – Solarflare Aug 31 '17 at 11:21
  • @Solarflare yes, i just saw the groupid, and id could be merged as 1 if that was what you meant. And for the data, as the data inserted is the Current data of each data, sum(data) wont work for me as to get the data from 1AUG to 3AUG i need to deduct (3rdData from 1stData) and as data resets (back to 0) every month on a date, lets say 5th every month. I dont think I can calculate the sum easily from the query itself – LuxuryWaffles Aug 31 '17 at 11:29
  • i looked up this post https://stackoverflow.com/questions/26303954/how-to-make-two-sql-queries-really-asynchronous and was thinking of making each number as an asyc task where each task gets the data for that number from start till end, would that work? – LuxuryWaffles Aug 31 '17 at 11:31
  • Please try my query out. You seem to look at the database as a pure datastore (basically a simple textfile). That is totally normal if you come from a c background (1 statement after the other, and multithreading(=asynchronous query) to do more than 1). It can do much more. Please read a tutoral, after two days, you will look back to your current approach and wonder what you tried there. The database can do what you are doing in one query. It is optimized for exactly this kind of stuff (even if you do not add indexes in the first attempt). – Solarflare Aug 31 '17 at 11:43
  • To be more precise: the ONE query (without the join) will give you an ordered list by phonenumber and date. e.g. (111111, Aug1, 100), next row (111111 Aug2 121) and so on. Try to think about how to display/use the rows you get. You could still use this output to fill your dictionary (not required though, since it is ordered - a date that is not in there will have 0). You can include the groupid. And add filters for a month to the query (e.g. `where date >= '2017-08-05' and date < '2017-09-05'`) or have some dategroupid to group dates to a month, and do any logic you can model into a datamodel. – Solarflare Aug 31 '17 at 11:50
  • @Solarflare i think I get the idea, so basically i have to add a join for the two table, grouping them by phone number and adding the date filters to the query right? Testing it out now – LuxuryWaffles Aug 31 '17 at 11:54
  • Yes. Give it a try. You have to join the tables to be able to add the filter to the group (I overlooked it in the first line), so it will e.g. be `select sum(data), Datatable.phonenumber, date from Datatable join PhoneGroup on PhoneGroup.phonenumber = Datatable.phonennumer where PhoneGroup.groupid = 4 and Datatable.date >= '2017-08-05' and Datatable.date < '2017-09-05' group by Datatable.phonennumber, Datatable.date order by Datatable.phonennumber, Datatable.date`. It requires the date to be an actual date-type, otherwise `2AUG` as a string will be ordered after `1SEP` (just like in c#). – Solarflare Aug 31 '17 at 12:02
  • looks good, however my tables are both in different database, trying to alter the connection string to connect to both – LuxuryWaffles Aug 31 '17 at 12:05
  • You cannot connect to both databases at once. If it is on the same server, you can use `databasename.tablename.columnname` (e.g. `yourdbname.PhoneGroup`) in the query to use tables from different databases. If they are on different servers, there are options too, but it's a lot more complicated (especially at the beginning), so you should try to move all data to one server (and preferable into one database). – Solarflare Aug 31 '17 at 12:11
  • @Solarflare yes read up on that a little, thankfully both are on the same server. trying out the query now – LuxuryWaffles Aug 31 '17 at 12:13

0 Answers0