Access 2007 What Is best for large table queries?

[joel009]

I am bumping a rather large recordset (8000 + records) to find records in another table using DAO. What I have set up will take 30 hours to run. I think there has to be a better way.

<code>
Set rsSource = db.OpenRecordset("SELECT TNResidentsTest.*" _
& " FROM TNResidentsTest" _
& " ORDER BY TNResidentsTest.GPGROUP;", dbOpenSnapshot)

If rsSource.EOF = True And rsSource.BOF = True Then
'close objects in reverse order to release variable
rsSource.Close
Set rsSource = Nothing
Set rsAdd = Nothing
Set db = Nothing
Set tdf = Nothing

Exit Function
End If

'open recordset for add
Set rsAdd = db.OpenRecordset("SELECT [TNResidentClaimNumbers].*" _
& " FROM [TNResidentClaimNumbers];")

Do Until rsSource.EOF = True
'set start time
varStartTime2 = Timer

Select Case Trim(rsSource!GPGROUP)
Case "30"
'get claims from specific claims table
Set rsGrpClms = db.OpenRecordset("SELECT SMARTHEALTH.CLYEAR, SMARTHEALTH.CLMONTH, SMARTHEALTH.CLDAY, SMARTHEALTH.CLACLMNO" _
& " FROM SMARTHEALTH" _
& " WHERE (((SMARTHEALTH.CLPARTIC)= " & "'" & rsSource!GPPARTIC & "'));", dbOpenDynaset)

If rsGrpClms.EOF = False And rsGrpClms.BOF = False Then
Do Until rsGrpClms.EOF = True
With rsAdd
.AddNew
!CLGROUP = Trim(rsSource!GPGROUP)
!CLPARTIC = Trim(rsSource!GPPARTIC)
!CLYEAR = Trim(rsGrpClms!CLYEAR)
!CLMONTH = Trim(rsGrpClms!CLMONTH)
!CLDAY = Trim(rsGrpClms!CLDAY)
!CLACLMNO = Trim(rsGrpClms!CLACLMNO)
.Update
End With
rsGrpClms.MoveNext
Loop
End If
<code>
The slow down is the query to the SMARTHEALTH table which has almost 1 million records and the return is so slow that it takes about 2 minutes to process each row from TNResidents. I can create a snapshot of the entire SMARTHEALTH table in less than 2 seconds but do not know how to extract the information. I have looked at SEEK and FindFirst but they are not as efficient as I'd like. Any suggestions will be appreciated.
Thanks.

Joel

 

[PHV]

Is SMARTHEALTH.CLPARTIC indexed ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jrbarnett]

In addition to what PHV says, some code refactoring could have a major performance improvement here.
Firstly, you don't need the overhead of opening a recordset just to check for the existence of a related record, look at the dcount function.
Secondly, you don't need to declare and open your recordset to cppy the details across one row at a time. Have a look at the currentdb.execute vba construct and the insert .. select sql statement.

John

 

[joel009]

PHV - No SMARTHEALTH.CLPARTIC is not indexed. But it should for this operation. SMARTHEALTH is the first of 10+ tables I have to bump against. None have an index on CLPARTIC. Is it possible to add an index to a recordset? The PK on the table is SMARTHEALTH.CLYEAR, SMARTHEALTH.CLMONTH, SMARTHEALTH.CLDAY, SMARTHEALTH.CLACLMNO, SMARTHEALTH.CLLINE.



jrbarnet - the records found and written have a many to one relationship. I will likely find multiple rows with SMARTHEALTH.CLYEAR, SMARTHEALTH.CLMONTH, SMARTHEALTH.CLDAY, SMARTHEALTH.CLACLMNO as there are multpiple SMARTHEALTH.CLLINE's per record but I am only interested in finding the unique SMARTHEALTH.CLYEAR, SMARTHEALTH.CLMONTH, SMARTHEALTH.CLDAY, SMARTHEALTH.CLACLMNO per SMARTHEALTH.CLPARTIC. I write these to a table and will process these further later on. I will take a look at writing the retreived records in one step as that will save a little time.

The biggest time hit is when rsGrpClms is created.

Joel

 

[MajP]

I would think it would be a lot faster to run insert queries than to use a recordset.

 

[TheAceMan1]

How are ya joel009 . . .

Agree with MajP. An insert query would be a slam dunk for you. However I notice your adding from two tables. Namely TNResidents and SmartHealth . Is it possible to get the data from the TNResidents table, from the SmartHealth table instead? ... were just trying to facilitate the insert query here. And don't forget those indexes.

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[joel009]

Thanks to the suggestions on this site the process is down to about 2 hours!!!! I changed to an update query:
<code>
strSQL = "INSERT INTO TNResidentClaimNumbers ( CLYEAR, CLMONTH, CLDAY, CLACLMNO, CLPARTIC, CLGROUP )" _
& " SELECT CLAIMCTL.CNYEAR, CLAIMCTL.CNMONTH, CLAIMCTL.CNDAY, CLAIMCTL.CNACLMNO, CLAIMCTL.CNPARTIC, CLAIMCTL.CNGROUP" _
& " FROM TNResidents LEFT JOIN CLAIMCTL ON (TNResidents.GPGROUP = CLAIMCTL.CNGROUP)" _
& " AND (TNResidents.GPPARTIC = CLAIMCTL.CNPARTIC)" _
& " WHERE (((CLAIMCTL.CNYEAR) Between '2009' And '2012'));"


CurrentDb.Execute strSQL
<code>
But all this talk of indexes got me to wondering:

The Key on CLAIMCTL is CNYEAR, CNMONTH, CNDAY, CNCLMNO and CNLINE - the CNLINE is extraneous as I am looking for all unique CNYEAR, CNMONTH, CNDAY, CNCLMNO and CNPARTIC, CNGROUP.

In reality if I was setting up this table I would have made the key CNYEAR, CNMONTH, CNDAY, CNCLMNO, CNLINE and CNPARTIC, CNGROUP. Most of the time we are looking at the CNPARTIC and CNGROUP for the CNYEAR, CNMONTH, CNDAY, CNCLMNO, CNLINE.

CNGROUP and CNPARTIC is not part of the key and they are not indexed. I can not figure out how to create a temporary key or alter the structure on a linked ODBC table (can this be done? I have seen a lot of examples online to do this but get a 3057 error). I will have to go to the folks who create/maintain the table and suggest they change the key to include CNGROUP and CNPARTIC unless someone can guide me to creating a temporary index on a linked ODBC table????

Thanks Again!!!

Joel