Last post May 23, 2012 10:27 AM by kraznodar
May 16, 2012 02:04 AM|roitshete|LINK
I am using asp.net and MySql combination.
In our application we are generating Report in Excel format.For that we are fetching data from database.
Sample data access code:
using (MySqlCommand command = GetCommandObject(connection, qo))
command.CommandTimeout = 120;
DataSet result = new DataSet();
MySqlDataAdapter adpt = new MySqlDataAdapter(command);
DataTableReader reader = result.Tables.CreateDataReader();
int i = 0;
// while (i <= result.Tables.Rows.Count)
collection.Add(entityMapper.MapEntity(reader) as TEntityType);
//insert into collection
In this we are fetching more that 3K rows the putting them into collection object.
After that we are selecting required fields from collection,binding collection to gridview and generation
excel from grid.
In this fetching data and putting it to collection taking around 20 minuts.
Please help me to bring this fast..
May 17, 2012 10:53 AM|kraznodar|LINK
I'm not familiar with MySql but I would suggest that the command you execute be refined to only return the fields that you want instead of the entire row. I would also suggest that you put debug statements into your code to record which parts of the process
are taking a lot of time. I've had similar situations where the problem was improperly optomized database and also a slow network connection.
May 18, 2012 06:43 AM|roitshete|LINK
There is no problem with database.
Inserting row by row data from dataset to collection taking lot of time.
How can I improve this?
May 23, 2012 10:27 AM|kraznodar|LINK
OK, I'll take your word for the database not being the problem and work on the premise that the MySQL has been optomised properly for I/O speed. That still leaves your network so I'd suggest a speed test to make sure there isn't a problem there.
The other option is to use a datatable instead of a dataset. A dataset has a large amount of overhead and is generally noticably slower than a data table. Your query only returns 1 table so the dataset isn't needed. Trim down the query to only return
fields that you need.
I generally don't use a datareader. I'm not sure how much overhead they generate. Actually, I don't think you need the datareader at all. Set the gridview datasource to the returned datatable and then you are ready to generate the Excel. I don't recall
if you need to define any columns when you set a gridview datasource but a quick Google search should locate the specifics for you.