Wrox Programmer Forums
|
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 March 9th, 2004, 07:45 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
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
 
Old March 9th, 2004, 09:09 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
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
 
Old March 9th, 2004, 09:32 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
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
 
Old March 9th, 2004, 10:28 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
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
 
Old March 9th, 2004, 10:45 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
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
 
Old March 9th, 2004, 11:00 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
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).
 
Old March 9th, 2004, 12:12 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
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.
 
Old March 9th, 2004, 12:14 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
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?????
 
Old March 9th, 2004, 04:04 PM
Authorized User
 
Join Date: Feb 2004
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
 
Old March 9th, 2004, 04:52 PM
Authorized User
 
Join Date: Feb 2004
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





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 03: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 12:08 AM





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