Solved

Access Database speed issue

Posted on 2003-12-12
15
224 Views
Last Modified: 2010-05-01
       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
Comment
Question by:ayerbozan
15 Comments
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 9928611
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
 
LVL 2

Expert Comment

by:rhys_kirk
ID: 9928630
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
 

Author Comment

by:ayerbozan
ID: 9928838
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9928947
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
 

Author Comment

by:ayerbozan
ID: 9929524
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9929744
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
 

Author Comment

by:ayerbozan
ID: 9929823
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 42

Expert Comment

by:frodoman
ID: 9929893
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
 

Author Comment

by:ayerbozan
ID: 9929970
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9930002
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
 

Author Comment

by:ayerbozan
ID: 9930053
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9930317
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
 

Author Comment

by:ayerbozan
ID: 9930356
still same error

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

Expert Comment

by:planocz
ID: 10289595
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now