asked on

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

ASKER CERTIFIED SOLUTION

frodoman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rhys_kirk

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.

ayerbozan

ASKER

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

frodoman

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.

ayerbozan

ASKER

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

frodoman

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

ayerbozan

ASKER

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.

frodoman

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

ayerbozan

ASKER

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

frodoman

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.

ayerbozan

ASKER

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

frodoman

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"

ayerbozan

ASKER

still same error

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

Howard Cantrell

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