[RESOLVED] Slow Database retreivals

[Rudie]

Hello,

This is my first post on here and am in need of some advice. I normally work with Oracle databases but have been asked to write a VB6/MS Access program.

Im using Visual Basic 6.0 Professional. Using Visual Data Manager v6.0. Im using data1.recordset usage to access the data. The only large table I have has 5000 records and it takes approximately 1 minute to load the initial window with all these records. I have tried to create indexes on the fields which I think will be in the "where" clause, as in Oracle, but response is still slow.

My question is, what am I doing wrong and how do I see the "pure SQL" like SELECT name FROM customer WHERE name="Hibbs", using VB so I can create the index accordingly?

Many Thanks.

[techgnome]

Short answer: Don't use the VDM.
Longer answer... code the queries yourself, or build the views right in the Access database and query those.

-tg

[Rudie]

So I should use MS Access instead or convert the VDM to Access?
Have you got an example code that I can view for the information given below?

[si_the_geek]

Welcome to the Forums!

Instead of using VDM or Data Controls, I would recommend creating the connections/recordsets yourself - you have far more control over what happens, and you can use SQL for virtually every piece of work you do with the database.

A good way to go is ADODB, as this should be supported for a long time (including in the next version of Windows). You can find a good example of the basics in the "ADO Tutorial" link in my signature.

As for Indexes, try not to have too many as they can slow things down if you go OTT - just have the ones that are most relevant (eg: primary keys).

[dee-u]

The only large table I have has 5000 records and it takes approximately 1 minute to load the initial window with all these records.

Why are you loading them all at once?

[Rudie]

Im loading all the records into a list box cos I need to show all the customers that are in debt, credit etc. up to 5000 records.

Also, is there a faster way to select/deselect all the records within a listbox?

[Hack]

Also, is there a faster way to select/deselect all the records within a listbox?

Welcome to the forums.

Private Sub cmdSelectAll_Click()
Dim i As Long
For i = 0 To List1.ListCount - 1
    List1.Selected(i) = True
Next
End Sub
 
Private Sub cmdUnSelectAll_Click()
Dim i As Long
For i = 0 To List1.ListCount - 1
    List1.Selected(i) = False
Next
End Sub

[Rudie]

Thanks for this Hack, but I forgot to add that this takes ages on 5000 records. As it has to go through each one in the listbox. Does anyone know of a faster way?

[Hack]

Thanks for this Hack, but I forgot to add that this takes ages on 5000 records. As it has to go through each one in the listbox. Does anyone know of a faster way?

Try this

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long 
 
Private Const LB_SETSEL = &H185& 
 
Private Sub cmdSelectAll_Click() 
Call SendMessage(List1.hwnd, LB_SETSEL, True, ByVal -1) 
End Sub 
 
Private Sub cmdUnSelectAll_Click() 
Call SendMessage(List1.hwnd, LB_SETSEL, False, ByVal -1) 
End Sub

[Rudie]

Thanks, that works excellent - select/deselect.

Ive managed to come up with a solution for the slow loading rather than recoding. Im refreshing the listbox every 100 records so at least it loads every couple of seconds rather than hanging for half a minute.

Next problem is that ive created a window to show to progress using the progressbar1, but how do i continue to load the listbox and control the progressbar at the same time?

At the moment, when the progress bar is progressing back and forth, the listbox is not loading until the progress window is closed.

Ive set an Interval on the Timer of 10

Within the Timer event, I got 'DoEvents' which I thought would release the loop to other processes.

Is what I am trying possible?

[Hack]

Post what you are doing. I'd like to take a look.

[Rudie]

Not sure whast you mean mate, do you mean screenshots or code shots?

[Hack]

Not sure whast you mean mate, do you mean screenshots or code shots?

Your code. Like I posted listbox code as an example. I want to see the code you are using to do your record loading and progress bar showing. To make things easier to ready, please encapsulate your code in [vbcode][/vbcode] tags. (See my signature)

[Rudie]

Heres the progress bar code that will go back and forth

Private Sub Timer1_Timer()
'This procedure will show the progress while its printing
On Error Resume Next
    'Increment the progress bar
    If check = 0 Then
        ProgressBar1.Value = ProgressBar1.Value + 1
        If ProgressBar1.Value >= 100 Then
            check = 1
            countr = countr + 1
        End If
    Else
        ProgressBar1.Value = ProgressBar1.Value - 1
        If ProgressBar1.Value <= 0 Then
            check = 0
            countr = countr + 1
        End If
    End If
    DoEvents
End Sub

I want to call this to start this off but want to load the listbox within another form whilst showing the above...

[Hack]

I want to call this to start this off but want to load the listbox within another form whilst showing the above...

Ah...didn't actually need to see the code after all. I just needed to know this.

These are two separate activities each of which will execute within the same memory thread (VB does not do multithreading). Therefore, one can not execute until the other is finished. You would need to combine the two into a single routine in which the records where loading, and within the loop you have for that, your would progress your progressbar.

[Rudie]

Ok, I see what I will have to do. I will have to incorporate the above within the listbox loading. I currently have a seperate window to control the progressbar, but can easily have that on the same window and make it visible/invisible.


I will let you know how I get on. Thanks to everyone for their help.

[Rudie]

Sorted, as per Hack's reply.