Sloooooooooooooooow Connectivity with ODBCDataReader

  • Thread starter Thread starter Roger H
  • Start date Start date
R

Roger H

Hello!

I have two database applications that I must merge the data from to
produce a file for a third database.

One system is running a Microsoft SQL Server and it's local to my
network. Performance is wonderful, I read everything in (about 25,000
records) in under 5 seconds.

The second database system is located 2000 miles away, and uses a
Sybase SQLAnywhere 6.0 system.

What I would like to do is download the tables I need from the SQL
Anywhere system and hold them in memory while I manipulate/merge.

Here's the 5 second SQL Server Read

public void GetData(SqlConnection cn, string tableName, string
dataBase, DataSet ds, System.Windows.Forms.StatusBar sb)
{
sb.Text = dataBase + "." + tableName;
SqlCommand cmd = new SqlCommand
("SELECT * FROM " + dataBase + "." + tableName , cn);
cmd.CommandType = CommandType.Text;
SqlDataReader rdr;

//Counter Setup
cn.Open();
SqlCommand count = new SqlCommand
("SELECT COUNT(*) FROM " + dataBase + "." + tableName, cn);
count.CommandType = CommandType.Text;
SqlDataReader countreader =
count.ExecuteReader(CommandBehavior.CloseConnection);
countreader.Read();
int counter = countreader.GetInt32(0);
countreader.Close();
cn.Close();

//perform query
cn.Open();
rdr = cmd.ExecuteReader();
bool bMoreResults=true;
while (bMoreResults)
{

DataTable dtSchemaTable = rdr.GetSchemaTable();
DataTable dtData = new DataTable(tableName);
int i;
for (i = 0; i < dtSchemaTable.Rows.Count; i++)
{
DataRow dr = dtSchemaTable.Rows;
string columnName = dr["ColumnName"].ToString();
DataColumn dc = new DataColumn(columnName, dr.GetType());
dtData.Columns.Add(dc);
}
int c = 0;
ds.Tables.Add(dtData);
while (rdr.Read())
{
DataRow dr = dtData.NewRow();
for (i = 0; i < rdr.FieldCount ; i++)
{
dr = rdr.GetValue(i);

}
dtData.Rows.Add(dr);
sb.Text = dataBase + "." + tableName + " " + ++c + "/"
+ counter.ToString() + " records read";

}
bMoreResults = rdr.NextResult();
}
rdr.Close();

cn.Close();
}

And the 36 Hour ODBC Read:
public void GetData(OdbcConnection cn, string tableName, DataSet ds,
System.Windows.Forms.StatusBar sb)
{

sb.Text = tableName;
OdbcCommand cmd = new OdbcCommand
("SELECT * FROM " + tableName +";COMMIT;", cn);
cmd.CommandType = CommandType.Text;
OdbcDataReader rdr;


//Get record counts
cn.Open();
OdbcCommand count = new OdbcCommand
("SELECT COUNT(*) FROM " + tableName +";COMMIT;", cn);
count.CommandType = CommandType.Text;
OdbcDataReader countreader =
count.ExecuteReader(CommandBehavior.CloseConnection);
countreader.Read();
int counter = countreader.GetInt32(0);
countreader.Close();
//set progress bar metrics

cn.Close();


//execute query
cn.Open();
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
bool bMoreResults=true;
while (bMoreResults)
{

DataTable dtSchemaTable = rdr.GetSchemaTable();
DataTable dtData = new DataTable(tableName);
int i;
for (i = 0; i < dtSchemaTable.Rows.Count; i++)
{

DataRow dr = dtSchemaTable.Rows;
string columnName = dr["ColumnName"].ToString();
DataColumn dc = new DataColumn(columnName, dr.GetType());
dtData.Columns.Add(dc);
}
ds.Tables.Add(dtData);
int c = 0;

while (rdr.Read())
{
DataRow dr = dtData.NewRow();
for (i = 0; i < rdr.FieldCount ; i++)
{
dr = rdr.GetValue(i);
}
dtData.Rows.Add(dr);
sb.Text = tableName + " " + ++c + "/"
+ counter.ToString() + " records read";
}
bMoreResults = rdr.NextResult();
}
rdr.Close();
cn.Close();
}

When I use the server explorer on one of the tables (with only 44
records) I get all of them back from this remote server in about 3
seconds. When My ODBC query runs against it I am seeing that same
table returned in right at one minuet (1.25-1.5 seconds per record)


How do I get my data reader to run as fast as the Server Explorer???

Thanks!!!

Roger
 
Back
Top