ado recordset very slow - on dual core

[ryansdv]

hello everyone,

please see my code.

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.ConnectionString = mycnn
con.Open

Set rs.ActiveConnection = con
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly
rs.CursorType = adOpenForwardOnly
rs.DataSource = "select * from products" 'about 30K records
rs.Open

Set bpartners.DataGrid1.DataSource = rs

My question is, its very slow on my dual core, 1gb memory computer. its takes about 60 seconds. Is there any other way to do it?

thank you very much.

 

[SBerthold]

See how long each action is taking:

Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    con.ConnectionString = mycnn
    con.Open
    [b]msgbox "Conn opened"[/b]
    Set rs.ActiveConnection = con
    rs.CursorLocation = adUseClient
    [s]rs.LockType = adLockReadOnly[/s]
    'Not a bookmarkable cursor type, which a datagrid needs
    [s]rs.CursorType = adOpenForwardOnly[/s]
    [s]rs.DataSource = "select * from products" 'about 30K records[/s]
    [b]rs.Source = = "select * from products"[/b]    
    rs.Open
    [b]msgbox "RS Opened"[/b]
    Set bpartners.DataGrid1.DataSource = rs
    [b]msgbox "Grid loaded"[/b]

30K records is alot for a grid. If the grid is taking too long to load, you might what to then try using a ServerCursor and an async operation instead.

 

[BobRodes]

<ServerCursor
Specifically, a keyset cursor, which doesn't fully populate your recordset before making records available.