Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 7th, 2003, 03:35 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default Function Efficiency

The function I have below is taking between 6 and 8 seconds to process [u]per record</u>, has anyone any idea's how I can speed it up?

The main field being processed is a 1500 character memo field.

Thanks Ben



Public Function GoGadgetGo(activityCode, scheduleStart, scheduleCode, attributeValue, exceptID)

Dim db As Database
Dim rec As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim codeTrue() As Integer, codeFalse() As Integer, maxVal As Integer

Set db = CurrentDb
Set rec2 = db.OpenRecordset("SELECT Count(attrval.ATTR_VALUE_NAME) FROM attrval WHERE (((attrval.ATTR_ID) = 1));", dbOpenDynaset)

maxVal = rec2(0)

rec2.Close

ReDim codeTrue(maxVal)
ReDim codeFalse(maxVal)

For i = scheduleStart To (scheduleStart + (Len(scheduleCode) - 1))

activity = Right(Left(activityCode, i), 1)
schedule = Right(Left(scheduleCode, i), 1)

activity = Asc(activity)
schedule = Asc(schedule)

Set rec = db.OpenRecordset("SELECT attrmap.ATTR_VALUE_ID FROM attrmap WHERE (((attrmap.ATTR_ID) = 1) And ((attrmap.EXC_ID) =" & schedule & "));", dbOpenDynaset)

If activity = schedule Then
codeTrue(rec(0)) = codeTrue(rec(0)) + 1
Else: codeFalse(rec(0)) = codeFalse(rec(0)) + 1
End If

rec.Close

Next

Set db = Nothing

For i = 1 To maxVal

codeTrue(i) = IIf(codeTrue(i) = Empty, 0, codeTrue(i))
codeFalse(i) = IIf(codeFalse(i) = Empty, 0, codeFalse(i))

GoGadgetGo = GoGadgetGo & codeTrue(i) & "," & codeFalse(i) & ","

Next

i = Len(GoGadgetGo)
GoGadgetGo = Left(GoGadgetGo, i - 1)

End Function
 
Old July 8th, 2003, 05:38 AM
Authorized User
 
Join Date: Jul 2003
Location: Reading, Berkshire, United Kingdom.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alex_read
Default

Are you using Access97?

 
Old July 8th, 2003, 05:55 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm using Access 2000

I've cut the processing time down to about 20 seconds for the whole query, the latest function I have is:

Public Function AdherenceMeasure(activityCode, scheduleStart, scheduleCode)

Dim db As Database
Dim adhVal As DAO.Recordset
Dim maxVal As DAO.Recordset
Dim attVal As DAO.Recordset
Dim codeTrue() As Integer, codeFalse() As Integer, i As Integer
Dim strActivity As String, strSchedule As String, bytActivity As Byte, bytSchedule As Byte

Set db = CurrentDb
Set maxVal = db.OpenRecordset("SELECT Count(attrval.ATTR_VALUE_NAME) FROM attrval WHERE (((attrval.ATTR_ID) = 1));", dbOpenSnapshot)
Set adhVal = db.OpenRecordset("SELECT attrmap.EXC_ID, attrmap.ATTR_VALUE_ID FROM attrmap WHERE ((attrmap.ATTR_ID) = 1);", dbOpenSnapshot)
Set attVal = db.OpenRecordset("SELECT attrval.ATTR_VALUE_ID, attrval.ATTR_VALUE_NAME FROM attrval WHERE (((attrval.ATTR_ID)=1));", dbOpenSnapshot)

ReDim codeTrue(maxVal(0))
ReDim codeFalse(maxVal(0))

For i = scheduleStart To (scheduleStart + (Len(scheduleCode) - 1))

strActivity = Mid(activityCode, i, 1)
strSchedule = Mid(scheduleCode, (i - scheduleStart + 1), 1)

bytActivity = Asc(strActivity)
bytSchedule = Asc(strSchedule)

adhVal.FindFirst ("EXC_ID = " & bytSchedule)

If bytActivity = bytSchedule Then
codeTrue(adhVal(1)) = codeTrue(adhVal(1)) + 1
Else: codeFalse(adhVal(1)) = codeFalse(adhVal(1)) + 1
End If

Next

adhVal.Close

For i = 1 To maxVal(0)

attVal.FindFirst ("ATTR_VALUE_ID = " & i)

If i = maxVal(0) Then
AdherenceMeasure = AdherenceMeasure & attVal(1) & Chr$(44) & codeTrue(i) & Chr$(44) & codeFalse(i)
Else: AdherenceMeasure = AdherenceMeasure & attVal(1) & Chr$(44) & codeTrue(i) & Chr$(44) & codeFalse(i) & vbCrLf
End If

Next

attVal.Close
maxVal.Close
Set db = Nothing

End Function
 
Old November 20th, 2003, 04:08 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Madison, Wisconsin, USA.
Posts: 451
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Ben Horne
Default

Ben,

I would try including an index in one of your queries. I know that using indexes helps queries run faster from past experience because I had to use an index for a database that I am currently working on for my Advanced Access class

Quote:
quote:Originally posted by Ben
 I'm using Access 2000

I've cut the processing time down to about 20 seconds for the whole query, the latest function I have is:

Public Function AdherenceMeasure(activityCode, scheduleStart, scheduleCode)

Dim db As Database
Dim adhVal As DAO.Recordset
Dim maxVal As DAO.Recordset
Dim attVal As DAO.Recordset
Dim codeTrue() As Integer, codeFalse() As Integer, i As Integer
Dim strActivity As String, strSchedule As String, bytActivity As Byte, bytSchedule As Byte

Set db = CurrentDb
Set maxVal = db.OpenRecordset("SELECT Count(attrval.ATTR_VALUE_NAME) FROM attrval WHERE (((attrval.ATTR_ID) = 1));", dbOpenSnapshot)
Set adhVal = db.OpenRecordset("SELECT attrmap.EXC_ID, attrmap.ATTR_VALUE_ID FROM attrmap WHERE ((attrmap.ATTR_ID) = 1);", dbOpenSnapshot)
Set attVal = db.OpenRecordset("SELECT attrval.ATTR_VALUE_ID, attrval.ATTR_VALUE_NAME FROM attrval WHERE (((attrval.ATTR_ID)=1));", dbOpenSnapshot)

ReDim codeTrue(maxVal(0))
ReDim codeFalse(maxVal(0))

For i = scheduleStart To (scheduleStart + (Len(scheduleCode) - 1))

strActivity = Mid(activityCode, i, 1)
strSchedule = Mid(scheduleCode, (i - scheduleStart + 1), 1)

bytActivity = Asc(strActivity)
bytSchedule = Asc(strSchedule)

adhVal.FindFirst ("EXC_ID = " & bytSchedule)

If bytActivity = bytSchedule Then
codeTrue(adhVal(1)) = codeTrue(adhVal(1)) + 1
Else: codeFalse(adhVal(1)) = codeFalse(adhVal(1)) + 1
End If

Next

adhVal.Close

For i = 1 To maxVal(0)

attVal.FindFirst ("ATTR_VALUE_ID = " & i)

If i = maxVal(0) Then
AdherenceMeasure = AdherenceMeasure & attVal(1) & Chr$(44) & codeTrue(i) & Chr$(44) & codeFalse(i)
Else: AdherenceMeasure = AdherenceMeasure & attVal(1) & Chr$(44) & codeTrue(i) & Chr$(44) & codeFalse(i) & vbCrLf
End If

Next

attVal.Close
maxVal.Close
Set db = Nothing

End Function
Ben
Madison Area Technical College student
-------------------------
I am one of those people that you call "Microsoft Access Freaks". I'm addicted to Access




Similar Threads
Thread Thread Starter Forum Replies Last Post
Efficiency Kev_Akas PHP Databases 1 May 13th, 2008 09:24 AM
Code Efficiency rsm42 ASP.NET 1.0 and 1.1 Basics 12 August 8th, 2007 11:36 AM
code efficiency sxstat90 SQL Language 1 March 8th, 2005 04:42 PM
efficiency qn - writing and saving xml file ak Classic ASP XML 1 March 2nd, 2004 05:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.