C# Retrieving large datatable from mysql takes too long (200k - 20 column records)

380
January 19, 2017, at 04:32 AM

Im trying to get some data from datatable this is my code

          public DataTable getAllProducts()
    {
        MySqlConnection conSQL = new MySqlConnection(connectionString);
        conSQL.Open();
        MySqlCommand getProductsCMD = new MySqlCommand(@"SELECT
                                                  products.*
                                                FROM products", conSQL);
        MySqlDataAdapter adapter = new MySqlDataAdapter(getProductsCMD);
        DataSet productDataSet = new DataSet();
        adapter.Fill(productDataSet, "Products");
        conSQL.Close();
        if (productDataSet.Tables[0].Rows.Count > 0)
        {
            return productDataSet.Tables[0];
        }
        return null;
    }

Filling the datatable takes almost 10 seconds and takes at least 800mb of my memory. afterward i dump it to a class which is much lighter and my application will drop to 70mb. the problem is that 10 seconds (at least) . it takes too long. is there any way to make it faster?

Update:

to those who say call your data partially . i have tried that mysql bottlenecks the process. there is another list that we get from wholesale 200k . and it needs to be scanned and then check if we have that product in our database. 200k times asking from mysql only for one datarow for the program to scan it and do its procedure took about 40 min but if i load all the datatable in program and filter it in my own program it takes about 5 minutes to finish the whole work. so mysql is a bottleneck in this case.

Rent Charter Buses Company
READ ALSO
rot13 function on MYSQL

rot13 function on MYSQL

I've searched all the internet looking for this

336
MySql Query to get output as below for given dat

MySql Query to get output as below for given dat

what will be the query to select disinct rows having the highest id for a pair (sender,receiver), taken columns in any sequencethat is (sender,receiver) or (receiver,sender)

308
Sailsjs what attribute does findOrCreate use to find if a record exists?

Sailsjs what attribute does findOrCreate use to find if a record exists?

I'm using sailsjs with mysql, and using it to save data from a shopify webhook when an order is placedIt returns a json object, and one of the properties is a customer object, which I'm saving in a different table related back to the main orders table

457
How to repeat MySQL query for multiple intervals

How to repeat MySQL query for multiple intervals

I've got a query which produces the proper results for a given time interval of 15 minutes

399