?
Solved

Slow user function in MS Access

Posted on 2014-04-14
14
Medium Priority
?
552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 600 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 85
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 48

Expert Comment

by:Dale Fye
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 46

Assisted Solution

by:aikimark
aikimark earned 200 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 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
 
LVL 46

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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 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 85
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 85
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

649 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