Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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?

Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:34 PM.


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