ADO Connection Speed up?

[sundog1]

Hi Guys,

Is there a best practise when having an ADO connection to speed up the process? Currently the method I am using below works fine but it's a little slow in getting the data.

Is there any hints, tips or tricks I can utilise to speed this up?

     //Opens the Line 50 Connection//
            adoConn.Open("SageLine50v19", "Manager", "", 0);

            //Creates new versions of the Connection string and Data Set//
            da = new OleDbDataAdapter();
            ds4 = new DataSet();

             if (File.Exists("C:\\twDB.xml"))
             {

                //Create a new Datatable called "accountsTable" and then adds that the the DataSet 4//
                DataTable accountsTable = new DataTable("accounts");
                ds4.Tables.Add(accountsTable);

                //SQL Statement ran against the tables via ADO to get information from the Accounts table//
                string query;
                query = "SELECT ACCOUNT_REF AS ACCOUNT, NAME, ADDRESS_3 AS TOWN, ADDRESS_1, ADDRESS_2, ADDRESS_4, ADDRESS_5 FROM SALES_LEDGER ORDER BY ACCOUNT_REF ASC";

                //Not to sure "exactly" what this does//
                adoRS.Open(query, adoConn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockPessimistic, 1);

                //Fills the newly created datatable using the ADO Record Set//
                da.Fill(accountsTable, adoRS);

                //Closes the Connection to Sage Line 50 (Read Only)//
                adoRS.Close();
                adoConn.Close();

                //Populates the DataGridView with the information from the SQL Select Statement//
                dataGridView1.DataSource = ds4.Tables[0];
                dataGridView1.Columns["ADDRESS_1"].Visible = false;
                dataGridView1.Columns["ADDRESS_2"].Visible = false;
                dataGridView1.Columns["ADDRESS_4"].Visible = false;
                dataGridView1.Columns["ADDRESS_5"].Visible = false;

Many thanks for any help offered

Regards
Mark.

UPDATE
It seems to takes about 17 seconds when pressing 'Account Customers' to load the data into the DGV which you can then select an account from. If I then select 'Cash Customers' it seems to take around the same amount of time (even though the Cash Customers are using ODBC Connection to an Access Database) ?

[Ketsuekiame]

Have you tried a more optimisitic locking strategy? (See line 20)

[sundog1]

Hi Ketsuekiame,

Many thanks for you reply.

I have tried that but still takes the same time to populate the DGV

[Ketsuekiame]

I did some Googling and found a couple of hits relating to Sage being slow with ADO connections.

Could it possibly be the network in between?

As a suggestion I would add timers to see if it's the data retrieval or the grid population that needs optimising. I'm guessing it's the retrieval personally.

EDIT: Also I noticed there is no cap on the number of records you retrieve. It may be worth saying "SELECT TOP 50" or "LIMIT 50" in the procedure to ensure that you aren't returning too large a dataset.

[sundog1]

Hi Ketsuekiame,

Many thanks for your help on this.. Just tried your EDIT option about selecting TOP 50 and that is instant on pulling data across.

Obvious issue though Is I need to show all Customers (Around 1000+) People onto the DGV.

Any ideas?

[Ketsuekiame]

Do you actually need to show every record all at once? I would implement paging. You can do this by setting the PageSize property on adoRS (your record set object) and then flip through the "pages".

Or, you can do this manually;

select col1, col2, col3, etc...
    from (
        select col1, col2, col3, ect..., ROW_NUMBER() OVER (order by orderColumn) as RowNum
        from yourTable
        ) as DerivedTable
where DerivedTable.RowNum between @startRow and @endRow

Just submit two row numbers to your query as parameters.

EDIT: Seems ADO has a hardcoded limit of 1000 records anyway so it may be wise to implement this if you have more...