Solved

Slow user function in MS Access

Posted on 2014-04-14
14
543 Views
Last Modified: 2014-04-14
I have a database containing 10s of thousands of product details. In order to import these into our online store, I use some VBA functions to format the data for our site. The problem is that it is incredibly slow.

How can I speed things up?

Public Function GenShortDesc(ftTableName As String, _
    ftProductID As String, _
    ftFeatureValue As String, _
    ftSequence As String, _
    ProdID As String) As Variant

On Error GoTo Err_Handler

    Set dbs = CurrentDb
    
    'ftTableName - Name of the features table
    'ftProductID - Features table Product ID field name
    'ftFeatureValue - Features table vale field name
    'ftSequence - Features table order
        
    Dim rs As DAO.Recordset         'Related records
    Dim strSql As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
   
    'Initialize to Null
    GenShortDesc = Null
      
    
    'Build SQL string, and get the records.
    strSql = "SELECT * FROM " & ftTableName
    strSql = strSql & " WHERE " & ftProductID & " = " & ProdID
    strSql = strSql & " ORDER BY " & ftSequence
    
    Set rs = dbs.OpenRecordset(strSql, dbOpenDynaset)
      
    
    
    strOut = "<ul>"
    'Loop through the matching records
    Do While Not rs.EOF
        strOut = strOut & "<li>" & rs(ftFeatureValue) & "</li>"
        rs.MoveNext
    Loop
    rs.Close
    strOut = strOut & "</ul>"
   
    'Return the string
  
        GenShortDesc = strOut
  

Exit_Handler:
    'Clean up
    Set rs = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "GenShortDesc()"
    Resume Exit_Handler
End Function

Open in new window


SELECT Products.ProductID AS ID, Products.*, genTable("CompAttributes","ProductID","Name","Value","Group","Sequence",[id]) AS LongDesc, GenShortDesc("Features","ProductID","Description","Sequence",[id]) AS ShortDesc
INTO [Excel 8.0;Database=I:\Products-txt.xls;].NewExcelTable FROM Products;

Open in new window

0
Comment
Question by:Rabbit80
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 26

Accepted Solution

by:
MacroShadow earned 150 total points
ID: 39998727
1. The function should be returning a string and not variant. (Public Function GenShortDesc(ftTableName As String, _
    ftProductID As String, _
    ftFeatureValue As String, _
    ftSequence As String, _
    ProdID As String) As String)
2. The code should not be slow unless the data is stored on a slow server. In other words it's more likely a problem with the environment than with the code.
0
 
LVL 84
ID: 39998734
I don't know what genTable does, but does genShortDesc create/get a single item from the ftTableName table for each ftProductID? Or could there be more than one ftProductID item?

As a general statement, you can sometimes find things to perform better if you use temporary tables. To do that, first create a table that would store the information returned from genShortDesc, and then Join the Products Table to that temporary table. Then, instead of re-running the function for each record, Access could intelligently pull records from the temporary table.

A simple SELECT INTO or INSERT INTO could do the tricK

Currentdb.Execute "SELECT ProdID, '<li>' & FeatureValue & '</li>' AS FeatureVal INTO tmpShortDes FROM [ftProductTable]"

Then just join tmpShortDesc to Production on ProductID

You seem to have multiple tables and/or fields that could be used to obtain the ShortDesc, so just add each of those table's data into tmpShortDesc.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39998751
You have a 2nd function embedded in that query as well.  What does the genTable( ) function look like?

Any time you embed a function in a query and pass it unique values, that function is going to fire for every record in your main query.  If that function creates a separate recordset, then that process takes time.

One thing I would do is move your:

    Set dbs = CurrentDb

line outside of that function.  Declare dbs as a public variable to the form or module you are working in.  Then set the value of that object to CurrentDb outside of the function call, so it doesn't have to be created for every record.  There is a very miniscule amount of overhead associate with this process, but if you have 10's of thousands of records in your [Products] table, this will save you some time.
0
 
LVL 1

Author Comment

by:Rabbit80
ID: 39998778
OK - thanks for the advice, I will take a further look this afternoon.

GenTable and GenShortDesc are very similar functions designed to build up a list of product features. They both operate on different tables within the database though (GenTable uses a complete list of attributes and builds a html specs sheet, GenShortDesc builds a list of highlights/features for the product)

I notice MacroShadow mentions the data being stored on a slow server - although this is not the case, I am using linked tables that are generated from text files. Would this have an impact on speed? These files are updated on a daily basis and I don't want for the end user (my boss) to have to update the tables manually.

Scott - for each ftProductID there may well be 5-10 records.

Fyed - will do as you suggest.

I'll update this question shortly as I change things around a bit!
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 50 total points
ID: 39998801
1. an alternative to Dale's suggestion. Use dbEngine(0)(0) instead of currentdb.  Also, use the forward only snapshot type of recordset.
Static rs As Recordset

If rs is Nothing Then
    Set rs = dbEngine(0)(0).OpenRecordset(strSql, dbOpenForwardOnly)
End If

Open in new window


2. reduce the retrieved columns.  continue the line or combine into a single line statement
    'Build SQL string, and get the records.
    strSql = "SELECT " & ftFeatureValue & " FROM " & ftTableName _
                   & " WHERE " & ftProductID & " = " & ProdID _
                   & " ORDER BY " & ftSequence

Open in new window


3. Eliminate the duplicate and unnecessary columns in your invoking SQL. For instance, you are getting duplicate ID columns.  You might not need all the columns from the Products table, so you should replace the * with field names.
SELECT Products.ProductID AS ID, Products.*, genTable("CompAttributes","ProductID","Name","Value","Group","Sequence",[id]) AS LongDesc, GenShortDesc("Features","ProductID","Description","Sequence",[id]) AS ShortDesc
INTO [Excel 8.0;Database=I:\Products-txt.xls;].NewExcelTable FROM Products;

Open in new window


4. Build an ADO recordset with the invoking SQL (#3) and then use Excel automation and the .CopyFromRecordset range method to transfer the data.  This will push all the data at once.  This is one of the methods I describe in my Fast Data Push to Excel article:
http://www.experts-exchange.com/A_2253.html

=============
If you have a lot of records in your product descriptions, you might replace your string concatenation iteration with something like the .Net stringbuilder class.  I wrote an article on such a class in the VBA environment:
http://www.experts-exchange.com/A_8311.html
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 39998802
In regards to fyed's comment:

"Then set the value of that object to CurrentDb outside of the function call, so it doesn't have to be created for every record.  "

See the article here:

http://www.experts-exchange.com/Database/MS_Access/A_2072-CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

Which gives you some drop in code to replace the CurrentDB() call which is much faster.

His comment is accurate and will result in faster execution, but the code gives you the best of both worlds without a lot of extra work.  You can drop in the code, then do a mass update on the code with Rick Fisher's Find and Replace:

www.rickworld.com

Replacing CurrentDB() with CurDB().

Would be interesting to see what genTable is like.   LSMConsulting is probably looking along the right lines; you'll need to refactor the operation to get substantially more performance out of it.

Jim.
0
 
LVL 1

Author Comment

by:Rabbit80
ID: 39998831
I just realised - my output is also being truncated to 255 characters :(
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

by:aikimark
ID: 39998878
@Rabbit80

What version of Excel and Access are you using?
0
 
LVL 1

Author Comment

by:Rabbit80
ID: 39998888
@Aikimark 2010
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 total points
ID: 39998928
I am using linked tables that are generated from text files.
I'd be curious if an imported text file would be quicker. I realize that would mean creating a routine to import the data before using it, but that's a fairly trivial matter. My concern is if a linked text file (if that's what it is) would be unindexed, whereas you could create and use indexes on the imported table.
0
 
LVL 1

Author Closing Comment

by:Rabbit80
ID: 39998985
It would seem the main issue was because I was using linked tables. MacroShadow picked up on the slow performance issue in the first response which led to the solution so I assigned him the highest points. I have taken on board other comments and have assigned points to others as well.
0
 
LVL 84
ID: 39998994
It would seem the main issue was because I was using linked tables
I don't see where the first comment mentioned linked tables at all. MacroShadow suggested you change the output to a String, and also that your environment might be at fault. Unless I missed something in earlier comments, my last comment suggested that you import the data instead of link to it.

So I'm curious as to exactly what the resolution was ...
0
 
LVL 1

Author Comment

by:Rabbit80
ID: 39999014
MacroShadow
2. The code should not be slow unless the data is stored on a slow server. In other words it's more likely a problem with the environment than with the code.

Me
I notice MacroShadow mentions the data being stored on a slow server - although this is not the case, I am using linked tables that are generated from text files. Would this have an impact on speed? These files are updated on a daily basis and I don't want for the end user (my boss) to have to update the tables manually.

The resolution is not to use linked tables in this case. MacroShadows first response made me realise that the use of linked tables could be the 'problem with the environment'

I was already on to converting the tables when you posted your suggestion - I have given credit to you however as you were correct.
0
 
LVL 84
ID: 39999214
Thanks for clarifying that. It was somewhat confusing as to what exactly you accepted as the solution, since MacroShadow did not mention getting rid of linked tables, and you indicated that was the ultimate solution.

I was already on to converting the tables when you posted your suggestion
Just an FYI: We have no way of knowing what you've tried (or are trying) unless you tell us this, so a correct answer posted after you began trying a different route is still a correct answer. That's sort of like taking your car to the mechanic when you can't diagnose a problem, and then refusing to pay your bill after the fact because you now know what the problem was!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now