VB Code running slow adding items to a listbox.

Hi,

I have the following code thats causing VB6 to be busy and stop with a error:

    Dim oClaimsConn As ADODB.Connection
    Dim oBirthsConn As ADODB.Connection

    Dim rsClaims As ADODB.Recordset
    Dim rsBirths As ADODB.Recordset

    Set oClaimsConn = New ADODB.Connection
    Set oBirthsConn = New ADODB.Connection
   
    Set rsClaims = New ADODB.Recordset
    Set rsBirths = New ADODB.Recordset
   
    oClaimsConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                            "Data Source=BOL16SCLAIMS01;Initial Catalog=NBSDatamart"

    oBirthsConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                            "Data Source=BOL16SCLAIMS01;Initial Catalog=MedicaidBirths"
    oClaimsConn.Open
    oBirthsConn.Open

    rsClaims.Open "Select * from tblClaimDetail", oClaimsConn
    rsBirths.Open "Select * from tblMedicaidBirths", oBirthsConn

Do While Not rsClaims.EOF       <--------- Here is where it is busy processing
    ClaimList.AddItem rsClaims.Fields(2).Value & " " & _
                      rsClaims.Fields(21).Value & " " & _
                      rsClaims.Fields(20).Value & " " & _
                      rsClaims.Fields(19).Value & " " & _
                      rsClaims.Fields(22).Value & " " & _
                      rsClaims.Fields(11).Value
    rsClaims.MoveNext
Loop

Do While Not rsBirths.EOF
    BirthsList.AddItem rsBirths.Fields(1).Value & " " & _
                      rsBirths.Fields(5).Value & " " & _
                      rsBirths.Fields(4).Value & " " & _
                      rsBirths.Fields(3).Value & " " & _
                      rsBirths.Fields(6).Value
    rsBirths.MoveNext
Loop
    oClaimsConn.Close
    oBirthsConn.Close

I am adding around 280,000 records to ClaimList Listbox and around 250,000 records to Birthlist Listbox.  Is this too large a recordset for VB to handle?

Mark
markchanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
Thats a lot of records, so it will take a while.

You might want to add a DoEvents inside the loop to allow other threads to process.

i.e.

    Do While Not rsClaims.EOF
         ClaimList.AddItem rsClaims.Fields(2).Value & " " & _
                      rsClaims.Fields(21).Value & " " & _
                      rsClaims.Fields(20).Value & " " & _
                      rsClaims.Fields(19).Value & " " & _
                      rsClaims.Fields(22).Value & " " & _
                      rsClaims.Fields(11).Value
        rsClaims.MoveNext
        DoEvents      '// Yield the thread
    Loop


If you are getting an error, what is it ?

Hope this helps.
PockyMasterCommented:
Yeah, it's too large to handle for a list box. and it's pretty useless as well. Remember a listbox is a GUI component. How much do you hate your users to present them with a 280.000 items picklist? *smile*
JRossi1Commented:
The size of the recordset is limited to the amount of memory on the machine that is running the application.

Some suggestions:

1) Avoid using "SELECT *".  Explicitly define the columns you need (SELECT Field1, Field2, Field3).  This will return a much smaller recordset, especially if the table has many columns.

2) Use a firehose cursor: Using firehose cursors to retrieve the data can significantly increase application access speed.  You can create a firehose cursor like this:


     Dim rst As Recordset
     Set rst = New Recordset
     rst.CursorLocation = adUseServer
     rst.Open SQLString, ConnectionString, adOpenForwardOnly, adLockReadOnly

3) Add 2 new variables: lngRecCount and lngCurrentRec. Set lngRecCount with the recordscount of each recordset: lngRecCount = rsClaims.RecordCount.  Set lngCurrentRec to 0.  Instead of using .EOF, try this:

Do until lngCurrentRec = lngRecCount
    lngRecCount = lngRecCount + 1
        <<<< add items here >>>>
    rsClaims.MoveNext
Loop

This is faster since the recordset object doesn't have to keep checking to see if it is at the end.

Hope this helps

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

PockyMasterCommented:
And carl : don't throw in a doEvents as well, it will update your listbox each time you insert a record... for 280.000 times ... that's not what you want. your pc might get bored doing that
Carl TawnSystems and Integration DeveloperCommented:
I only mentioned that because its sometimes preferable to your app having just seemingly locked and have it not respond to anything.

PockyMasterCommented:
yeah, but it mostly is a rescue in times you don't know whats happening exactly :D
but well, it's vb6 right?
I still think the encouragment should be, use GUI components for GUI and use data components for datahandling.
An average user has problems selecting 2 different items out of a pickbox of 3 already. Let alone 280.000
dancebertCommented:
One commonly seen GUI design guideline is that a lisbox should have no more than 200 items in it.  Your problem is that the the .Net designers were completly unconcerned with optimizing a listbox to contain 250k items.  Your solution is to redesign the GUI.
markchanAuthor Commented:
While running the program, VB is stuck in the busy state while looping through adding items to the lists eventually leading to the error "Microsoft has encountered a problem" and asks where I should send a error report to microsoft or not.

Eventually one list will be narrowed down to say a couple of thousand items each while the other might be a couple of hundred items.  I will give this a try.
SanclerCommented:
If you really have to load that number of records (and I would have thought that for any practical purpose, let alone just the GUI, they really ought to be filtered somehow) then, for my money, you ought to load them into a datatable using an ADO.NET datareader or dataadapter and then bind the datatable to a listbox (or datagrid?).  Concatenating the values and adding them to the listbox will be taking up (I reckon) a large part of your processing time.  A straight read of the data and letting databinding handle what goes in the display would - I imagine - be much faster.  

In any event, you only seem to be using 6 out of a minimum of 23 fields in tblClaimDetail and 6 fields out of a minimum of 7 in tblMedicaidBirths so why are using "SELECT *"?  The time taken for reads will be a function not just of how many records are read but of how much data is in each of those records.

Roger
PockyMasterCommented:
As well, somehow I tend to remember that the listboxes in VB6 only used to carry 1 unsigned integer amount of items .. so that's 65535.

But hey Sancler, nice idea to load the thing into a ADO.NET datareader in VB6.
hatem72Commented:


try to make List.Visible = False First

... Add Job

then List.Visible = True

it will save time 4 u
markchanAuthor Commented:
Now the recordset is returning -1 for the Recordcount.  I have the following code:

    oClaimsConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                            "Data Source=BOL16SCLAIMS01;Initial Catalog=NBSDatamart"
   
    oClaimsConn.Open
    rsClaims.CursorLocation = adUseServer
    rsClaims.Open "Select * from tblClaimDetail", oClaimsConn, adOpenForwardOnly, adLockReadOnly

    If rsClaims.RecordCount > 100 Then
        Do While Not iRecCounter < 100
            ClaimList.AddItem rsClaims.Fields(2).Value & " " & _
                      rsClaims.Fields(21).Value & " " & _
                      rsClaims.Fields(20).Value & " " & _
                      rsClaims.Fields(19).Value & " " & _
                      rsClaims.Fields(22).Value & " " & _
                      rsClaims.Fields(11).Value
                iRecCount = iRecCount + 1
                iRecCounter = iRecCounter + 1
            rsClaims.MoveNext
        Loop
    Else
hatem72Commented:

change CursorLocation to adUseClient

rsClaims.CursorLocation = adUseClient

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
markchanAuthor Commented:
This worked, although performance is still slow as the recordset is still ~210,000.  
SanclerCommented:
>>
But hey Sancler, nice idea to load the thing into a ADO.NET datareader in VB6.
<<

Yup, almost as good as putting a question about VB6 in the VB.NET Topic Area ;-)

In other words, I missed that it was VB6

Roger
markchanAuthor Commented:
Ah, sorry, somehow this question was listed under VB.Net.
PockyMasterCommented:
if you still want to follow that path, try considering to keep a dataset ready with all your data, and provide your own scrollbar, and load just something like 10 records each time a user scrolls
markchanAuthor Commented:
I ended up loading the data in a ADO Recordset and keeping the recordset in memory while displaying 100 records at a time in the listbox.  The second listbox populating dynamically with corresponding matching records when the user selects a item from the first listbox.  Thanks for everyone's suggestions!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.