I am trying to figure out a way to streamline importing data from an access database into a .Net application

Question

I'm writing a fairly light-weight .Net application that queries an Access database and generates a Crystal Report based on the result-set.

The Dataset fill method is a little slow, but manageable.

The issue is when I load the crystal report. If the dataset being passed to Crystal is sizeable, at all, it takes forever to load, and the user gives up.

This is the code that sets the report dataset:

report.SetDataSource(largeDataSet);

largeDataSet has two tables. I have paired them down to the absolutely essential columns.

What ends up happening is the

reportViewer.Show();

command fires, but the ReportViewer takes minutes to actually display the report.

I am at a loss.

I appreciate your help and feedback.

Answer 1

I suggest you to try a performance profiler app. ANTS Performance Profiler is a good one.

http://www.red-gate.com/products/ants_performance_profiler/index.htm

Answer 2

I ended up using a DataView to first sort (effectively setting up a non-clustered index) and then filter out only the rows from each table that I needed, then I set up a new dataset and used the dataView.ToTable() method to populate the new dataset.

This set up a less costly transaction for Crystal Reports. The previously 6-10 minute report is now loading in under a minute.

private DataSet FilterData()
    {
        DataSet filteredData = new DataSet("FilteredData");
        DataView viewAccount = new DataView(global65DataSet.SET_ACCOUNT_TABLE);
        viewAccount.Sort = "Number ASC";
        viewAccount.RowFilter = " Number >= '" + beginningAcct+"' AND Number <= '" + endAcct + "' ";

        DataView viewTrans = new DataView(global65DataSet.SET_TRANSACTION_TABLE);
        viewTrans.Sort = "Transaction_ID ASC, DateTime ASC";
        viewTrans.RowFilter = " DateTime >= '" + beginningDate.ToShortDateString() + "' AND DateTime <= '" +
                              endDate.ToShortDateString() + "'";

        filteredData.Tables.Add(viewAccount.ToTable());
        filteredData.Tables.Add(viewTrans.ToTable());
        filteredData.Tables[0].TableName = "SET_ACCOUNT_TABLE";
        filteredData.Tables[1].TableName = "SET_TRANSACTION_TABLE";

        return filteredData;
    }

There's probably a better, more OO way to do this, but this works :)