Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | 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 Display Modes
  #1 (permalink)  
Old March 9th, 2004, 07:45 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 Any faster?

I've created the below code to process imported data into the required format, currently it's taking just over 2 seconds per record, has anyone any idea's how I can speed it up - I have around 30,000 records to process.

Public Function CombineFields(UniqueCompositeKey)

Dim recTopics As Recordset, i As Integer

CombineFields = UniqueCompositeKey
Set recTopics = CurrentDb().OpenRecordset("SELECT Topics.Topic FROM Topics WHERE (((Topics.RecID) = '" & UniqueCompositeKey & "'));")

    For i = 1 To 8
         If recTopics.EOF Then GoTo NextStage:
         CombineFields = CombineFields & "," & recTopics(0).Value
         recTopics.MoveNext
    Next

NextStage:
recTopics.Close

    For i = i To 8
         CombineFields = CombineFields & ","
    Next

End Function

Thanks Ben
Reply With Quote
  #2 (permalink)  
Old March 9th, 2004, 09:09 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

I can think of a few things that might help. What database is it and can you provide an example of what UniqueCompositeKey looks like and an example of the desired output?

--

Joe
Reply With Quote
  #3 (permalink)  
Old March 9th, 2004, 09:32 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

Hi Joe,

Thanks for the reply, sample data is below, with desired output below that.

Unique Key Topic
01/01/2002,07:33:00,3730603,jglixxun Referred to MD team
01/01/2002,08:02:00,3780400,nveggehh Exceeded Credit Limit
01/01/2002,08:02:00,3780400,nveggehh OTS Discon/Canx query
01/01/2002,08:03:00,3760007,zejibodu Explaining # Button
01/01/2002,08:03:00,3730803,ddaarkal Ledger Balance

01/01/2002,07:33:00,3730603,jglixxun,Referred to MD team,,,,,,,
01/01/2002,08:02:00,3780400,nveggehh,Exceeded Credit Limit,OTS Discon/Canx query,,,,,,
01/01/2002,08:03:00,3760007,zejibodu,,,,,,,
01/01/2002,08:03:00,3730803,ddaarkal,,,,,,,

or

a table containing the following fields; date,time,[u]RecID</u>,reference,f1,f2,f3,f4,f5,f6,f7,f8
Reply With Quote
  #4 (permalink)  
Old March 9th, 2004, 10:28 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

I'll have a go, which version of Access are you using?
As a matter of interest your Table structure seems very strange and why do you want a non-normalised output like this anyway?



--

Joe
Reply With Quote
  #5 (permalink)  
Old March 9th, 2004, 10:45 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Try this, I'm a bit uncertain about the final part of your function, you may want to add that bit back in.
Code:
Public Function CombineFields(UniqueCompositeKey)
  Dim recTopics As ADODB.Recordset, sSql as string, i as Integer, sData As String
  sSql = "SELECT TOP 8 Topics.Topic FROM Topics WHERE (((Topics.RecID) = '" & UniqueCompositeKey & "'));"
  Set recTopics = New Adodb.recordset
  recTopics.open sSql, CurrentProject().Connection    
  sData = UniqueCompositeKey
 & "," & recTopics.getString(adClipString, , ",", "")
  recTopics.Close
  CombineFields = sData
  'Add final tidy up here
End Function
--

Joe
Reply With Quote
  #6 (permalink)  
Old March 9th, 2004, 11:00 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

Hi Joe

Thanks for that, I will give it a go and post the time saving.

I am using Access 2000, in this case I'm not using it as a relational database, I'm merely using it to parse some data I'm sent in Excel Format, I am cleansing the data and making a CSV file to import into my Main DB (which is relational).
Reply With Quote
  #7 (permalink)  
Old March 9th, 2004, 12:12 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The original method took 19.768 seconds to process 8 records
The method you proposed took 19.896 seconds to process 8 records

Not much in it really, but looking at the two methods, I do think your method is tidier and more scalable.
Reply With Quote
  #8 (permalink)  
Old March 9th, 2004, 12:14 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Strange - I was posting the results to the debug window to ensure they were correct and both methods seem to process the first record twice?????
Reply With Quote
  #9 (permalink)  
Old March 9th, 2004, 04:04 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

DAO has its speed advantages against an mdb backend. If you are calling this function from an external looping procedure to process the records, you would do well to create a database variable one time outside the loop in the calling function and pass it in as a parameter instead of using a static variable. I've used a static database variable in this example because calling CurrentDb 30,000 times over a LAN is going to be a killer. You can consider an optional parameter to kill the static variable, especially in an environment where there are many users and you will want to release the static pointer. Explicit type declarations help where possible, but GetRows returns a variant array (which is what a recordset is anyway) but it is strongly typed here for clarity. I would consider writing this procedure in line with a caller that is iterating 30,000 records. Unioning a literal apostrophe apostrophe empty string guarantees at least 8 records are returned and you avoid the loop concatenation and the EOF test. Let us know how this fares:

Public Function CombineFields(UniqueCompositeKey As String) As String

Dim recTopics As DAO.Recordset
Dim arr as Variant
Static db as DAO.Database

    If db Is Nothing Then Set db = CurrentDb

Set recTopics = db.OpenRecordset("SELECT TOP 8 Topics.Topic FROM Topics WHERE Topics.RecID = '" & UniqueCompositeKey & "' Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics Union All Select " & _
  "'' From Topics Union All Select '' From Topics Union All Select Union All Select '' " & _
  "From Topics Union All Select '' From Topics ")

     arr = recTopics.GetRows(8)
     CombineFields = UniqueCompositeKey & "," & arr(0, 0) & "," & arr(0, 1) & "," & arr(0, 2) & _
            "," & arr(0, 3) & "," & arr(0, 4) & "," & arr(0, 5) & "," & arr(0, 6) & "," & arr(0, 7)

recTopics.Close
End Function



Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
  #10 (permalink)  
Old March 9th, 2004, 04:52 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've messed up the recordset string a bit by leaving out a table name in one place. It's a bit more presentable below:

Set recTopics = db.OpenRecordset("SELECT TOP 8 Topics.Topic FROM Topics WHERE " & _
  "Topics.RecID = '" & UniqueCompositeKey & "' " & _
  "Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics")

The intent was to Union All the empty string 8 times.


Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
Reply


Thread Tools
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
Suggestion for better / faster processing elygp SQL Server 2000 2 February 13th, 2008 10:47 PM
which is faster pegasus51 Ajax 3 December 30th, 2007 08:56 AM
Which one is much faster in gridview, htmltable anshumannidhi BOOK: Beginning Visual Basic 2005 ISBN: 978-0-7645-7401-6 0 October 11th, 2006 04:50 AM
They Want It To Be Faster! kindler Access 7 December 20th, 2005 10:54 AM
c++ faster than Pascal IvAnR C++ Programming 5 August 6th, 2005 01:08 AM



All times are GMT -4. The time now is 11:17 PM.


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