Go Premium for a chance to win a PS4. Enter to Win

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

Access Database speed issue

       Dim objVisHist As New AnamneseViHi
        Dim objInvoiceDet As New InvoiceDetail

        Dim strEmployee As String
        Dim strInvoicePosCount As String
        Dim itmX As ListItem
        Dim i As Long

100     objListView.ListItems.Clear
        DoEvents

        'Enumerate LvwCustomer Record and Populate Listview

'101     objVisHist.MoveFirst
102     objVisHist.SQL = "SELECT * FROM AnamneseViHi WHERE AnamneseViHi.AddressID=" & lngAddressID
103     objVisHist.MoveFirst

104     Do Until objVisHist.EOF

            'Get Count of all Invoice Detail assigned to this History and if these was billed
'116         objInvoiceDet.MoveFirst
118         objInvoiceDet.SQL = "SELECT * FROM InvoiceDetail WHERE InvoiceDetail.VisitID=" & objVisHist.FID & " AND InvoiceDetail.IsVisitItem=1"
120         objInvoiceDet.MoveFirst

'122         If (Not objInvoiceDet.BOF And Not objInvoiceDet.EOF) Then

'124             strInvoicePosCount = objInvoiceDet.RecordCount '& ""

 '           Else

'126             strInvoicePosCount = "0"

 '           End If

            'Fill the ListView
130             Set itmX = objListView.ListItems.Add(, , , , 1)

132             With objVisHist

134                 itmX.Text = .VisitDate '& ""
136                 itmX.SubItems(1) = Replace(.ShortDescription, vbCrLf, " ") '& ""
138                 itmX.SubItems(2) = GetEmployeeFullName(.EmpoyeeID)
140                 itmX.SubItems(3) = strInvoicePosCount '& ""
142                 itmX.SubItems(4) = " "
144                 itmX.Tag = .FID 'Set Tag to FID used for Editing

                End With

            ' Optimize ListView Loading
146    '     ValidateRect objListView.hWnd, 0&

148        ' If (i Mod 10) = 0 Then InvalidateRect objListView.hWnd, 0&, 0&

            'DoEvents

150         objVisHist.MoveNext

        Loop

152     InvalidateRect objListView.hWnd, 0&, 0&
        Dim x As ListItem
        Set x = objListView.ListItems.Item(objListView.SelectedItem.Index).ListSubItems(4)
154     If objListView.ListItems.Count > 0 Then

156         For i = 1 To objListView.ListItems.Count

158         '    objListView.ListItems.Item(i).Selected = True

                'Set Yes or No to Column billed
'160             objInvoiceDet.MoveFirst
162             objInvoiceDet.SQL = "SELECT * FROM InvoiceDetail WHERE InvoiceDetail.VisitID=" & objListView.SelectedItem.Tag & " AND InvoiceDetail.IsVisitItem=1" & " AND InvoiceDetail.InvoiceID>0"
164             objInvoiceDet.MoveFirst
               
166             If CLng(objListView.SelectedItem.ListSubItems(3).Text) > 0 Then

168                 If (Not objInvoiceDet.BOF And Not objInvoiceDet.EOF) Then

170                   '  objListView.ListItems.Item(objListView.SelectedItem.Index).ListSubItems(4).Text = "ok"
172                    ' objListView.ListItems.Item(objListView.SelectedItem.Index).ListSubItems(4).Bold = True
174                     'objListView.ListItems.Item(objListView.SelectedItem.Index).ListSubItems(4).ForeColor = &H8000&
                        x.Text = "ok"
                        x.Bold = True
                        x.ForeColor = &H8000&
                    Else

176                     'objListView.ListItems.Item(objListView.SelectedItem.Index).ListSubItems(4).Text = "X"
178                     'objListView.ListItems.Item(objListView.SelectedItem.Index).ListSubItems(4).Bold = True
180                     'objListView.ListItems.Item(objListView.SelectedItem.Index).ListSubItems(4).ForeColor = vbRed
                        x.Text = "X"
                        x.Bold = True
                        x.ForeColor = vbRed

                    End If

                End If

182         Next i

        End If

184     Set objVisHist = Nothing


Hello the code above never completes on my p 200 mmx . Its really slow and i have to find a way to speed it up. Both loops should fetch up 500 records from my access database . I waited almost ten minutes  but it didnt complete . Im using ado 2.71. The class files such as AnamneseViHi and InvoiceDetail are automaticaly created by data class builder add-in of AJE software

Web : http://www.ajevans.com/components_dataclassbuilder.shtml

I can also send you  the class files or the whole project if you want to take a look at it

Ive cropped do loop to this


objVisHist.SQL = "SELECT * FROM AnamneseViHi WHERE AnamneseViHi.AddressID=" & lngAddressID

objVisHist.MoveFirst

Do Until objVisHist.EOF
      
      objInvoiceDet.SQL = "SELECT * FROM InvoiceDetail WHERE InvoiceDetail.VisitID=" &       objVisHist.FID & " AND InvoiceDetail.IsVisitItem=1"
      
      objInvoiceDet.MoveFirst

      objVisHist.MoveNext

Loop

even this code take a large amount of time to complete

Please take a look this is really urgent
0
ayerbozan
Asked:
ayerbozan
1 Solution
 
frodomanCommented:
Looking at the 2nd code sample...

Are AnamneseViHi.AddressID, InvoiceDetail.VisitID, InvoiceDetail.IsVisitItem all indexed fields?  If not, indexing them will probably have a significant speed improvement.


Otherwise, can you eliminate the loop within a loop by combining the queries?  I'm not sure what you're trying to do, but if possible I'd go for something more like this:
--------------
Select * FROM AnamneseViHi INNER JOIN InvoiceDetail ON AnamneseViHi.objVisHist.FID = InvoiceDetail.VisitID WHERE AnamneseViHi.AddressID=" & lngAddressID & " AND InvoiceDetail.IsVisitItem=1
--------------
This gives you the same data in a single recordset so you don't have to loop within a loop.

Hope this helps...
0
 
rhys_kirkCommented:
Do you really need to select all columns (*) from your table?  Try fetching only relevant columns to your code.

Also, rather than reading in one table and then querying another, join the tables together and read through data.

0
 
ayerbozanAuthor Commented:
Hello

This is actually not my code and im new to db area. Can you please be more specific.

For example how can i know if AnamneseViHi.AddressID, InvoiceDetail.VisitID, InvoiceDetail.IsVisitItem  are indexed and how can i index them if they are not
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
frodomanCommented:
Open the access database and open the table with the design tab.  Click on the appropriate field and one of the properties for the field will be "indexed".  You have 3 choices: "no" "yes (no duplicates)" or "yes (duplicates allowed)".  I suspect that you're going to want the 3rd choice - yes with duplicates allowed.

Simply put, this will allow the database engine to read an index to find the records where IsVisitItem = 1 instead of reading through the entire table.  Think of it as though you use an index in a book to find something which is much faster than reading every page until you find what you need.

0
 
ayerbozanAuthor Commented:
Thanks for your replies frodoman. Il try your suggestions as soon as i install office

And rhys_kirk: Can you also provide give me some specific details of your idea. How can i fetch only relevant code and how can i join the tables

Thank you all
0
 
frodomanCommented:
Sounds good.  

rhys_kirk's comment about "joining tables" is the code I gave you in my first post - note the "inner join" within the sql command to join the two tables together.

When he said fetch only relevant data he means SELECT field1, field2, etc. instead of selecting all of them (which is what SELECT * does).  I have to say though that I doubt this will make any appreciable difference unless the columns you aren't using are embedded oledb objects (unlikely in this context).
0
 
ayerbozanAuthor Commented:
do you want me to send the project . that will be very good if you can test it on your machine i didnt test it on a fast computer and id like to know the difference.
0
 
frodomanCommented:
No - for one thing that's a violation of EE membership agreement.

Just try this in your code to select everything at once w/ only 1 loop.  This is very, very basic code.  Work from this and add the indexes when you get the chance - that's essentially going to give you the best performance you can get.  Unfortunately with Access on a P200 it isn't going to be zippy no matter what you do :-(

objVisHist.SQL = Select AnamneseViHi.*, InvoiceDetail.* FROM AnamneseViHi INNER JOIN InvoiceDetail ON AnamneseViHi.objVisHist.FID = InvoiceDetail.VisitID WHERE InvoiceDetail.IsVisitItem=1

objVisHist.MoveFirst
Do Until objVisHist.EOF
     << Whatever you need to do w/ the data here >>     
     objVisHist.MoveNext
Loop

0
 
ayerbozanAuthor Commented:
objVisHist.SQL = "Select AnamneseViHi.*, InvoiceDetail.* FROM AnamneseViHi INNER JOIN InvoiceDetail ON AnamneseViHi.objVisHist.FID = InvoiceDetail.VisitID WHERE InvoiceDetail.IsVisitItem=1"

the querry raises this error

Data Engine Misbehaved on OpenTable, Error Number: [-2147217904]
Description: No value given for one or more required parameters.

do you know which value is missing
0
 
frodomanCommented:
Do you know the datatype of IsVisitItem?  If it's a text-type try ".....WHERE InvoiceDetail.IsVisitItem='1'" or even try taking off every from 'WHERE' to the end just to see what you get.
0
 
ayerbozanAuthor Commented:
IsVisitItem is a single

Can you type the full querry please . Sorry if i m asking too much but this is really urgent and should work

thanks again
0
 
frodomanCommented:
Just try this - this should return every record in the database.  Get this working, then we can go forward from there.

objVisHist.SQL = "Select AnamneseViHi.*, InvoiceDetail.* FROM AnamneseViHi INNER JOIN InvoiceDetail ON AnamneseViHi.objVisHist.FID = InvoiceDetail.VisitID"

0
 
ayerbozanAuthor Commented:
still same error

Data Engine Misbehaved on OpenTable, Error Number: [-2147217904]
Description: No value given for one or more required parameters.
0
 
planoczCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
No response from ayerbozan from 12/12/2003 comment

Award points to frodoman is recommend.

Is sounds like your problem maybe in the data class builder add-in of AJE software.
It is best to build app with vb code then once it works, switch to your add-in's.

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

planocz
EE Cleanup Volunteer
0

Featured Post

Independent Software Vendors: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now