Performance tip on multiple selects to retrieve alot of BLOBS?
-
I am retrieving a lot of data and want this to be as fast as possible. Let's say I have a database with two tables; tablePerson and tableFiles. A person has one file wich is stored in a BLOB. In my solution as it is now, I select * from person table, and while reading from this I select one row from the table with the blob.. This sums up to alot of one-row-queries towards the blob-table.. This blob-table has 678000 rows, and all these queries are taking a lot of time... So.. How can I improve performance on this? Any tips? This is something similar to my code:
using(SqlCommand cmd = new SqlCommand("select * from tablePerson", _conn)
{
SqlDataReader r = cmd.exeCuteReader();while(r.read())
{
Person p = new Person();
//read some data from columns..string documentID = Convert.ToString(r\["p\_docid"\]); p.LocationToDocument = ExtractDocument(documentID);}
r.close();
r.dispose();
}...
private string ExtractDocument(string docid)
{
string filename = "";
using (SqlCommand cmd = new SqlCommand("select d_docid, d_title, d_BLOB from tableDocument where d_docid=@paramDocID", _conn)
{
SqpParameter p = new SqlParameter("@paramDocID", System.Data.SqlDbType.Text);
p.Value = docid;SqlDataReader r = cmd.exeCuteReader(System.Data.CommandBehavior.SequentialAccess); while(r.read()) //1 row.. { //extract blob to a file, and set filename=that document.. }}
return filename;
} -
I am retrieving a lot of data and want this to be as fast as possible. Let's say I have a database with two tables; tablePerson and tableFiles. A person has one file wich is stored in a BLOB. In my solution as it is now, I select * from person table, and while reading from this I select one row from the table with the blob.. This sums up to alot of one-row-queries towards the blob-table.. This blob-table has 678000 rows, and all these queries are taking a lot of time... So.. How can I improve performance on this? Any tips? This is something similar to my code:
using(SqlCommand cmd = new SqlCommand("select * from tablePerson", _conn)
{
SqlDataReader r = cmd.exeCuteReader();while(r.read())
{
Person p = new Person();
//read some data from columns..string documentID = Convert.ToString(r\["p\_docid"\]); p.LocationToDocument = ExtractDocument(documentID);}
r.close();
r.dispose();
}...
private string ExtractDocument(string docid)
{
string filename = "";
using (SqlCommand cmd = new SqlCommand("select d_docid, d_title, d_BLOB from tableDocument where d_docid=@paramDocID", _conn)
{
SqpParameter p = new SqlParameter("@paramDocID", System.Data.SqlDbType.Text);
p.Value = docid;SqlDataReader r = cmd.exeCuteReader(System.Data.CommandBehavior.SequentialAccess); while(r.read()) //1 row.. { //extract blob to a file, and set filename=that document.. }}
return filename;
} -
I am retrieving a lot of data and want this to be as fast as possible. Let's say I have a database with two tables; tablePerson and tableFiles. A person has one file wich is stored in a BLOB. In my solution as it is now, I select * from person table, and while reading from this I select one row from the table with the blob.. This sums up to alot of one-row-queries towards the blob-table.. This blob-table has 678000 rows, and all these queries are taking a lot of time... So.. How can I improve performance on this? Any tips? This is something similar to my code:
using(SqlCommand cmd = new SqlCommand("select * from tablePerson", _conn)
{
SqlDataReader r = cmd.exeCuteReader();while(r.read())
{
Person p = new Person();
//read some data from columns..string documentID = Convert.ToString(r\["p\_docid"\]); p.LocationToDocument = ExtractDocument(documentID);}
r.close();
r.dispose();
}...
private string ExtractDocument(string docid)
{
string filename = "";
using (SqlCommand cmd = new SqlCommand("select d_docid, d_title, d_BLOB from tableDocument where d_docid=@paramDocID", _conn)
{
SqpParameter p = new SqlParameter("@paramDocID", System.Data.SqlDbType.Text);
p.Value = docid;SqlDataReader r = cmd.exeCuteReader(System.Data.CommandBehavior.SequentialAccess); while(r.read()) //1 row.. { //extract blob to a file, and set filename=that document.. }}
return filename;
}As Asfield said, join the 2 tables so that you retrieve them once. I would add to what he said is that you probably want to add paging. So instead of processing 1 M record, you can process 1000 record at a time etc..