Speed issues with combo box


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Speed issues with combo box

  1. #1
    Ross Gilbert Guest

    Speed issues with combo box


    I'm having a speed issue loading a combo box will a lot of records. I'm trying
    to load 360 items into it using a simple Do Until statement.
    For Example o Until rs.EOF
    cmbCity.AddItem rs!City
    rs.MoveNext
    Loop
    rs.Close
    I know this is a lot of items to load but this is the way the powers that
    be want it. Is there a better way to do this? Thanks in advance.

    Ross Gilbert

  2. #2
    ralph Guest

    Re: Speed issues with combo box


    360 records doesn't sound like too many. Are you sure it is this code that
    is slow?

    1) Turn sort off.
    2) Make recordset "forward-only"
    3) Use the "With .. End With" block on rs
    4) Not sure if the field variant rs!City is slower or not,
    but might be worth a look.

    Does anyone know if the 'move' method is faster on a 'forward-only' recordset.
    (I have no reason to believe it is.)

    Take a look at some of the 3rd party toys out there that take a straight
    recordset assignment.



  3. #3
    Ross Gilbert Guest

    Re: Speed issues with combo box


    Thanks Ralph
    I didn't think it was that many records either. But even after making
    the changes you suggested it takes 4.2 seconds to load. I've tried it on
    other machines with the same results. Thanks for the help!

    "ralph" <nt_consulting32@hotmal.com> wrote:
    >
    >360 records doesn't sound like too many. Are you sure it is this code that
    >is slow?
    >
    >1) Turn sort off.
    >2) Make recordset "forward-only"
    >3) Use the "With .. End With" block on rs
    >4) Not sure if the field variant rs!City is slower or not,
    > but might be worth a look.
    >
    >Does anyone know if the 'move' method is faster on a 'forward-only' recordset.
    >(I have no reason to believe it is.)
    >
    >Take a look at some of the 3rd party toys out there that take a straight
    >recordset assignment.
    >
    >



  4. #4
    Matthew Solnit Guest

    Re: Speed issues with combo box

    Try this:

    Dim fldCity As ADODB.Field

    Set fldCity = rs.Fields("City")

    Do Until rs.EOF
    cmbCity.AddItem fldCity.Value
    rs.MoveNext
    Loop

    "Ross Gilbert" <rgilbert@drc.com> wrote in message
    news:3993f929$1@news.devx.com...
    >
    > Thanks Ralph
    > I didn't think it was that many records either. But even after making
    > the changes you suggested it takes 4.2 seconds to load. I've tried it on
    > other machines with the same results. Thanks for the help!
    >
    > "ralph" <nt_consulting32@hotmal.com> wrote:
    > >
    > >360 records doesn't sound like too many. Are you sure it is this code

    that
    > >is slow?
    > >
    > >1) Turn sort off.
    > >2) Make recordset "forward-only"
    > >3) Use the "With .. End With" block on rs
    > >4) Not sure if the field variant rs!City is slower or not,
    > > but might be worth a look.
    > >
    > >Does anyone know if the 'move' method is faster on a 'forward-only'

    recordset.
    > >(I have no reason to believe it is.)
    > >
    > >Take a look at some of the 3rd party toys out there that take a straight
    > >recordset assignment.
    > >
    > >

    >




  5. #5
    Ross Gilbert Guest

    Re: Speed issues with combo box


    Thanks Matthew
    I inserted your code but I'm still getting 4.2 second. It has to be something
    else, maybe something in the recordset itself. I think I'm going to load
    the data into an access table and see what happens. Thanks Again!

    "Matthew Solnit" <nospam@nospam.com> wrote:
    >Try this:
    >
    >Dim fldCity As ADODB.Field
    >
    >Set fldCity = rs.Fields("City")
    >
    >Do Until rs.EOF
    > cmbCity.AddItem fldCity.Value
    > rs.MoveNext
    >Loop
    >
    >"Ross Gilbert" <rgilbert@drc.com> wrote in message
    >news:3993f929$1@news.devx.com...
    >>
    >> Thanks Ralph
    >> I didn't think it was that many records either. But even after making
    >> the changes you suggested it takes 4.2 seconds to load. I've tried it

    on
    >> other machines with the same results. Thanks for the help!
    >>
    >> "ralph" <nt_consulting32@hotmal.com> wrote:
    >> >
    >> >360 records doesn't sound like too many. Are you sure it is this code

    >that
    >> >is slow?
    >> >
    >> >1) Turn sort off.
    >> >2) Make recordset "forward-only"
    >> >3) Use the "With .. End With" block on rs
    >> >4) Not sure if the field variant rs!City is slower or not,
    >> > but might be worth a look.
    >> >
    >> >Does anyone know if the 'move' method is faster on a 'forward-only'

    >recordset.
    >> >(I have no reason to believe it is.)
    >> >
    >> >Take a look at some of the 3rd party toys out there that take a straight
    >> >recordset assignment.
    >> >
    >> >

    >>

    >
    >



  6. #6
    Lenny Toulson Guest

    Re: Speed issues with combo box

    If you're having to hit the DB server for each record as you request it, you
    may not be able to squeeze much more performance from your routine. It's
    just a guess, but give this a shot and let me know how it turns out:

    Private Sub LoadCombo()
    Dim acnDBConn As ADODB.Connection
    Dim arsData As ADODB.Recordset
    Dim strValue As String
    Dim lngStart As Long
    Dim lngStop As Long

    '---------------------------
    ' Open DB connection
    '---------------------------
    Set acnDBConn = New ADODB.Connection
    With acnDBConn
    '---------------------------
    ' Make sure to use your correct values here!!!
    '---------------------------
    .ConnectionString = "DRIVER=SQL Server;" & _
    "UID=sa;PWD=;SERVER=;DATABASE="
    .CursorLocation = adUseClient
    .Mode = adModeRead
    .Open
    End With

    '---------------------------
    ' Get desired data
    '---------------------------
    Set arsData = New ADODB.Recordset
    With arsData
    Set .ActiveConnection = acnDBConn
    .Source = "SELECT * FROM TableName"
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .Open
    End With

    '---------------------------
    ' Load into combo box
    '---------------------------
    Me.Combo1.Clear
    With arsData
    Do Until .EOF
    With .Fields
    strValue = .Item("FieldName")
    Me.Combo1.AddItem strValue
    End With

    .MoveNext
    Loop
    End With
    End Sub

    I ran this sub against a remote SQL Server database over a 56K Internet
    connection and it completed the routine in about 250-260 milliseconds after
    opening the connection and recordset. The whole routine ran in 2.5 seconds
    or less.

    Good luck.

    --
    Lenny
    __________


    "Ross Gilbert" <rgilbert@drc.com> wrote in message
    news:39940f7c$1@news.devx.com...

    I inserted your code but I'm still getting 4.2 second. It has to be
    something
    else, maybe something in the recordset itself. I think I'm going to load
    the data into an access table and see what happens. Thanks Again!





  7. #7
    Joel Ryan Guest

    Re: Speed issues with combo box

    How are you opening the recordset.

    rs.Open "cities"

    or

    rs.Open "select city_name, city_id from cities"

    -- Joel

    Ross Gilbert wrote:

    > Thanks Matthew
    > I inserted your code but I'm still getting 4.2 second. It has to be something
    > else, maybe something in the recordset itself. I think I'm going to load
    > the data into an access table and see what happens. Thanks Again!
    >
    > "Matthew Solnit" <nospam@nospam.com> wrote:
    > >Try this:
    > >
    > >Dim fldCity As ADODB.Field
    > >
    > >Set fldCity = rs.Fields("City")
    > >
    > >Do Until rs.EOF
    > > cmbCity.AddItem fldCity.Value
    > > rs.MoveNext
    > >Loop
    > >
    > >"Ross Gilbert" <rgilbert@drc.com> wrote in message
    > >news:3993f929$1@news.devx.com...
    > >>
    > >> Thanks Ralph
    > >> I didn't think it was that many records either. But even after making
    > >> the changes you suggested it takes 4.2 seconds to load. I've tried it

    > on
    > >> other machines with the same results. Thanks for the help!
    > >>
    > >> "ralph" <nt_consulting32@hotmal.com> wrote:
    > >> >
    > >> >360 records doesn't sound like too many. Are you sure it is this code

    > >that
    > >> >is slow?
    > >> >
    > >> >1) Turn sort off.
    > >> >2) Make recordset "forward-only"
    > >> >3) Use the "With .. End With" block on rs
    > >> >4) Not sure if the field variant rs!City is slower or not,
    > >> > but might be worth a look.
    > >> >
    > >> >Does anyone know if the 'move' method is faster on a 'forward-only'

    > >recordset.
    > >> >(I have no reason to believe it is.)
    > >> >
    > >> >Take a look at some of the 3rd party toys out there that take a straight
    > >> >recordset assignment.
    > >> >
    > >> >
    > >>

    > >
    > >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center