[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

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
0
markchan
Asked:
markchan
  • 5
  • 5
  • 2
  • +4
2 Solutions
 
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.
0
 
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*
0
 
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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
0
 
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.

0
 
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
0
 
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.
0
 
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.
0
 
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
0
 
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.
0
 
hatem72Commented:


try to make List.Visible = False First

... Add Job

then List.Visible = True

it will save time 4 u
0
 
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
0
 
hatem72Commented:

change CursorLocation to adUseClient

rsClaims.CursorLocation = adUseClient
0
 
markchanAuthor Commented:
This worked, although performance is still slow as the recordset is still ~210,000.  
0
 
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
0
 
markchanAuthor Commented:
Ah, sorry, somehow this question was listed under VB.Net.
0
 
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
0
 
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!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now