C# Programming
05-22-2013, 01:13 AM
Hi Guys. Suppose this question has been asked numerous times. I have a database "postgresql" with about 20 columns and about 1.4million records. I select data from the database between two given date ranges and it takes about 4 minutes to fill the dataset. I would like to speed this up. I am doing no updates, just using the data to compile a report. is there a faster way to do this? Below code is what I have. My results are OK but just filling the dataset is a bit of a problem i that it takes +- 4 to 5 minutes to fill the dataset.
string sql = @"SELECT * FROM datbase where bus_received_time_stamp between @startDate AND @endDate"; //Making use of a Postgresql Connection helper NpgsqlConnection conn = new NpgsqlConnection(conns); //Here we format the dateTimePicker dates to be used with the database string fromDate = dtStartDate.Text; string toDate = dtEndDate.Text; //Instanciate a SqlCommand and connect to the DB and fill a Dataset NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.Parameters.AddWithValue("@startDate", fromDate + " 00:00:01"); cmd.Parameters.AddWithValue("@endDate", toDate + " 23:59:59"); #endregion conversionRate = txtConversionRate.Text; double conRate = Convert.ToDouble(conversionRate); try { //Open the DB Connection conn.Open(); setText(this, "Connection Established"); NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd); setText(this, "Collecting Data for Processing!"); da.Fill(dts);//Time between the two "setText statements takes a good 4 to 5 minutes setText(this, "Define Data To be Used");
From here the processing takes a few minutes as there quite a bit of matching to do etc. However the main thing here is that filling the DataAdapter takes too much time. I would like to cut this down to maybe a few seconds, maybe a minute? I have added an Index on the DB on the Datee column to try and speed things up but now sure if this is correct?
Any ideas??
Excellence is doing ordinary things extraordinarily well.
string sql = @"SELECT * FROM datbase where bus_received_time_stamp between @startDate AND @endDate"; //Making use of a Postgresql Connection helper NpgsqlConnection conn = new NpgsqlConnection(conns); //Here we format the dateTimePicker dates to be used with the database string fromDate = dtStartDate.Text; string toDate = dtEndDate.Text; //Instanciate a SqlCommand and connect to the DB and fill a Dataset NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.Parameters.AddWithValue("@startDate", fromDate + " 00:00:01"); cmd.Parameters.AddWithValue("@endDate", toDate + " 23:59:59"); #endregion conversionRate = txtConversionRate.Text; double conRate = Convert.ToDouble(conversionRate); try { //Open the DB Connection conn.Open(); setText(this, "Connection Established"); NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd); setText(this, "Collecting Data for Processing!"); da.Fill(dts);//Time between the two "setText statements takes a good 4 to 5 minutes setText(this, "Define Data To be Used");
From here the processing takes a few minutes as there quite a bit of matching to do etc. However the main thing here is that filling the DataAdapter takes too much time. I would like to cut this down to maybe a few seconds, maybe a minute? I have added an Index on the DB on the Datee column to try and speed things up but now sure if this is correct?
Any ideas??
Excellence is doing ordinary things extraordinarily well.