CODEWITHSUNDEEP
c#asp.netsql-server-2008
jimmy
jimmy

Reputation: 756

DataTable Load very slow

I'm using a datatable as the datasource of some dropdowns on a page, but have noticed that the page is very slow during the postbacks.

I've tracked it through to here:

DataTable dt = new DataTable();
dt.Load(sqlCmd.ExecuteReader()); // this takes ages

The sql command is a parametrised query, not a stored procedure (the return values and where are quite 'dynamic' so this wouldn't be practicable), but nevertheless a simple select union query. Usually returns between 5 and 20 options per dropdown, depending on what's been selected on the other dropdowns. When I run the query in the management studio, it's done in under a second. Here it can take up to 7 seconds per dropdown, with 6 dropdowns on the page it soon adds up. I have also tried with a SqlDataAdapter:

SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt); // this takes ages

but this was just as slow. I have this on 2 different systems and on both have the same performance issues.

If anyone knows a better (faster) methord, or knows why this is so slow that would be great.

Upvotes: 3

Views: 15541

Answers (2)

MethodMan
MethodMan

Reputation: 18843

here is an example on how you can load a DataTable very quickly notice how I show specific Columns that I want to return

private DataTable GetTableData()
{
  string sql = "SELECT Id, FisrtName, LastName, Desc FROM MySqlTable";
  using (SqlConnection myConnection = new SqlConnection(connectionString))
  {
    using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
    {
      myConnection.Open();
      using (SqlDataReader myReader = myCommand.ExecuteReader())
      {
        DataTable myTable = new DataTable();
        myTable.Load(myReader);
        myConnection.Close();
        return myTable;
      }
    }
  }
}

If you want to use DataAdapter to Fill the DataTable here is a simple example

private void FillAdapter()
{
    using (SqlConnection conn = new SqlConnection(Your ConnectionString))
    {
      conn.Open();
          using (SqlDataAdapter dataAdapt = new SqlDataAdapter("SELECT * FROM EmployeeIDs", conn))
      {
         DataTable dt = new DataTable();
         dataAdapt.Fill(dt);
        // dataGridView1.DataSource = dt;//if you want to display data in DataGridView 
      }
    }
}

Upvotes: 0

RandomUs1r
RandomUs1r

Reputation: 4190

Not the best thread I've seen on the issue, but there's good links inside, & it's in my post history:

SQL Query that runs fine in SSMS runs very slow in ASP.NET

The SQL Optimizer sometimes likes to decide what's best & you'll have to break out your query through some tracing and logging of data execution plans. It may very well be something as buried as a bad index, or your query code might need optimization. Seeing as we don't have the query code, and having it may or may not be helpful. I'd recommend you follow the guides linked to in the above post and close your question.

Upvotes: 2

Related Questions