Bdtjtk

EDIT: Iterating over Reader takes time, Execute Reader returns in few seconds. Is there some faster way instead of iterating ?

I am trying to fetch data from MySQL based on Filters. Previously i was using EF it was very slow, so then i decided to switch to Reading data using MySQLDataReader, it works fine when the result contains few records but is terribly slow on large result lets say Million rows.
I am using following function to formulate the query.

public string GetCorrespondingQuery()

    {

        string zips = """ + string.Join("","", _zips) + """;

        string counties = """ + string.Join("","", _counties) + """;

        string states = """ + string.Join("","", _states) + """;

        string cities = """ + string.Join("","", _cities) + """;



        if ((_zips.Count == 0) && (_states.Count == 0) && (_counties.Count == 0) && (_cities.Count == 0))

            throw new Exception("Alert! No Filters Selected.");

        string query = "select * from dbcustomer JOIN dbzipcode On dbcustomer.ZIPCODE = dbzipcode.ZIP";

        if (_zips.Count > 0)

            query += " where dbzipcode.Zip in (" + zips + ")";

        if (_states.Count > 0)

            query += " and dbzipcode.STATE in (" + states + ")";

        if (_cities.Count > 0)

            query += " and dbzipcode.City in (" + cities + ")";

        if (_counties.Count > 0)

            query += " and dbzipcode.County in (" + counties + ")";



        return query + ";";

    }

Above query takes few seconds when executed in MySQL Workbench, but takes several minutes using C#.

Following is the Code to fetch Data from MySQL Database.

public List<CustomerDTO> FetchCustomersUsingQuery(CustomersFilter filter)

    {

        string query = filter.GetCorrespondingQuery();

        List<CustomerDTO> customers = new List<CustomerDTO>();

        using (MySqlConnection con = new MySqlConnection(_connectionString))

        {

            MySqlCommand cmd = new MySqlCommand(query, con);

            cmd.CommandTimeout = 0;

            cmd.CommandType = CommandType.Text;



            con.Open();

            using (MySqlDataReader reader = cmd.ExecuteReader())

            {

                while (reader.Read())

                {

                    var customer = new CustomerDTO()

                    {

                        PHONE = reader.GetString(0),

                        FIRSTNAME = reader.GetString(1),

                        LASTNAME = reader.GetString(2),

                        ADDRESS = reader.GetString(3),

                        CStatus = reader.GetString(4),

                        Campaign = reader.GetString(5),

                        ListName = reader.GetString(6),

                        Email = reader.GetString(7),

                        Recording = reader.GetBoolean(8),

                        ZIP = reader.GetString(9),

                        CITY = reader.GetString(11),

                        COUNTY = reader.GetString(12),

                        STATE = reader.GetString(13),

                    };

                    customers.Add(customer);

                }

                reader.Close();

            }

            con.Close();

        }

        //s.Stop();

        //throw new Exception("Time to Fetch " + customers.Count + " records = " + s.Elapsed);



        return customers;

    }

I have tried whatever i could,Can anyone guide me to speed it up? I am struggling to improve this from past few weeks