Solved

Fastest most efficient way to populate an MS Access 2007 Listbox control

Posted on 2014-04-15
16
817 Views
Last Modified: 2014-04-20
Hello all,
I have a form with a listbox on it that is currently being populated by a somewhat kludgy SQL statement as it's rowsource.  The problem is that it's kind of slow loading.  This list box will also be filtered by a wildcard textbox control as the end user sees fit.  In an attempt to streamline the use of this form, I want to have the listbox load as efficiently as possible.  Any help would greatly be appreciated.

Thanks

PS - I'm using a concatenated field to search filter via the wildcard textbox control filter.
Here's the where clause I'm using:
WHERE (((tblAssetAuditLog.UDb & tblAssetAuditLog.UNum & " - " & tblAssets.Desc) Like (IIf(nz([Forms]![frmAssetAuditLogSearch]![txtFilter],"")="","*","*" & [Forms]![frmAssetAuditLogSearch]![txtFilter] & "*"))))
0
Comment
Question by:shannonds
  • 5
  • 3
  • 3
  • +2
16 Comments
 
LVL 57
ID: 40002548
1. Consider a continuous form rather than a list box.

2. Do a separate filter for UDb, UNum, and Desc (Bad field name choice by the way - DESC is a SQL verb) and index on one or all of the fields.

Before doing that however, I'd find where the problem is.  Construct a query of the SQL and time how long it takes alone with the filtering you have now.

Then time how long it takes on the form.  Subtract the two and you'll have your load time for the list control and will know where most of the problem lies.

Jim.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40002577
You should be able to drop your IIF() function.
WHERE (tblAssetAuditLog.UDb & tblAssetAuditLog.UNum & " - " & tblAssets.Desc) Like "*" & [Forms]![frmAssetAuditLogSearch]![txtFilter] & "*"

Open in new window


Since the ampersand concatenator is Null-tolerant, your like clause will either resolve to
"**"
or
"*valueofyourtxtFiltercontrol*"
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40002583
Take your current SQL statement and make it a stored query.  Change the rowsource of the listbox to the queryname.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:shannonds
ID: 40002631
The iif statement returns all rows when there's nothing entered in the text box.  The after update event of the text box simply requeries the listbox control.  If any thing is entered the rowsource will be filtered, otherwise it returns all rows.
0
 

Author Comment

by:shannonds
ID: 40002646
Jim I'm looking into the continuous form vs a listbox and will see if that has any effect, but I tend to doubt it, as my guess is that it's more of a source issue.  I'm thinking that populating it via a Recordset might be a possiblity.  Again, I won't know until I test it.
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40002647
Your criteria is preventing the query engine from using any indexes.  That means that each time you run the query, Access must read every single row in the table to determine if it fits the criteria!!!!  The query will be more efficient if you use the individual columns.

WHERE (tblAssetAuditLog.UDb = [Forms]![frmAssetAuditLogSearch]![txtUDb] OR [Forms]![frmAssetAuditLogSearch]![txtUDb] Is Null)
AND (tblAssetAuditLog.UNum  = [Forms]![frmAssetAuditLogSearch]![txtUNum] OR  [Forms]![frmAssetAuditLogSearch]![txtUNum] Is Null)
AND (tblAssets.Desc = [Forms]![frmAssetAuditLogSearch]![txtDesc] OR [Forms]![frmAssetAuditLogSearch]![txtDesc] Is Null);

This WHERE clause allows any/all of the search fields to be null.  If all the search fields are null, all records will be returned.  You can add indexes to the three search fields (if they are not foreign keys) and that will speed up the process.  Access automatically creates hidden indexes on foreign keys when you define them in the relationship window.

If you still need to use LIKE because you have partial strings, Access will probably not be able to use any of the indexes you created and so you will be back to reading all rows of the base table.  The query engine may be able to use an index if the wild card comes at the end of the string - LIKE somefield & "*" but it absolutely cannot use an index if the wild card comes first - LIKE "*" & somefield

LIKE is an extremely expensive search option since it frequently forces full table scans and the bigger your table, the longer the scan will take.  So, NEVER use LIKE just to support optional arguments.  Use the method I suggested above.  ONLY use LIKE when you actually have partial strings as you would if you were searching a name field.  If you are using a combo to select a search value, you always have a complete string and so would never use LIKE.  And finally, LIKE is a string operation and so is intended to operate against strings.  That means you would not use LIKE to search date fields or numeric fields.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40002693
The iif statement returns all rows when there's nothing entered in the text box

Try my version of the SQL.  That is how it should behave.  A Like operator with two asterisks should behave the same as a single asterisk.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40002745
And if you insist on using the syntax that concatenates the fields together, you should probably insert a space between the first two fields in that process, something like:

WHERE (tblAssetAuditLog.UDb & " " &  tblAssetAuditLog.UNum & " - " & tblAssets.Desc) Like "*" & ([Forms]![frmAssetAuditLogSearch]![txtFilter] + "*")

I also think you could wrap the last part of the LIKE clause in ( ) and replace the last & with a + .  If your txtFilter control is NULL, this would return:

LIKE "*" as everything inside the parentheses would evaluate to NULL
0
 

Assisted Solution

by:shannonds
shannonds earned 0 total points
ID: 40002772
Thank you all for your input, but I figured it out.  Something in what Pat Hartman wrote jogged my memory.  What I was doing was specifying a criteria in the row source of the list book when the form opened.  On open of the form, it's never going to have a filtered value, so I removed the criteria from the listbox rowsorce and built it on the fly in the afterupdate event of the textbox.  It works perfectly fine and there's no need to specify a Like parameter if no value is entered.

Thanks for all your help...
0
 
LVL 57
ID: 40003675
@Pat

<<The query will be more efficient if you use the individual columns.>>

Funny, that's exactly what I said in my very first comment:

"2. Do a separate filter for UDb, UNum, and Desc (Bad field name choice by the way - DESC is a SQL verb) and index on one or all of the fields."

  I and other experts certainly don't mind a different point of view, a correction to something that's been said, or even adding on to what's been said (acknowledging that you are adding on), but more then a few times now, you've commented in on threads by saying exactly what has already been said.

 If your going to comment in a question,  please add to the conversation in some way rather then simply repeating what has already been said.

 Sometimes there is overlap and similar comments are posted minute/seconds apart, and that cannot be avoided.  But if at all possible, we like to have threads clean and concise rather then having a bunch of experts all piling in and saying the exact same thing.

Thanks,
Jim.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40004700
@Jim,

You're acting like I took food out of your mouth.

Occasionally the OP needs reinforcement (based on his closing comments, it still sounds like he's doing the wrong thing) and normally when I repeat advice already offered, I mention that I am doing so as a way of emphasis.  Shoot me, I missed your comment about the separate columns but the rest of your advice was off the mark which is why I commented at all.  

I do believe I added to the conversation since I explained WHY what he was doing was wrong and WHY it was so slow.  I also gave him an ALTERNATIVE method of supplying optional parameters so he didn't have to use LIKE as well as a suggestion on how to speed up the search even if he had to stick with the LIKE operator.  Switching to a subform has nothing to do with anything.  In most cases, your advice is sound.  In this case, your explanation was inadequate which is not to say it was incorrect.  It simply didn't go far enough to explain why the OP should change his approach.  Way too many "experts" post "try this" answers and never explain why.  I guess it is a way to garner more POINTS with less effort.  You should also notice that although the OP said he gave me all the points, he didn't so I have none and I am not complaining.
0
 

Author Comment

by:shannonds
ID: 40009494
Pat, I did actually give you all of the points, but it's still sitting with a "close request pending" message.  I'm not sure why, but this is what it says:
________________________________________________________________________
Close Request Pending
shannonds requested that this question be closed by accepting PatHartman's comment #a40002647 (500 points) as the solution and shannonds's comment #a40002772 (0 points) as the assisted solution for the following reason:

Pat's solution jogged my memory as to what I needed to do.  My last comment is what I actually ended up doing.  I gave Pat all the points for his knowledgeable approach to the topic.  I selected mine as well, so that if someone is searching for the solution in the future, they can see what I ended up doing...

To cancel this request and generate a request for Moderator review, state your reason for objecting in the standard comment box and click the 'Object' button. This question will be closed on 4/19/2014 if there are no objections.
___________________________________________________________________________

As for Jim, I chose Pat's answer, because it's the one that caused me to head down the right road.  If you're solution was the same as Pat's then I would have chosen yours.  Maybe the way you worded it was unclear or maybe if you spent more time focusing on the issue, rather than commenting on the field name...  All in all, I didn't do what you suggested, because it wasn't what I was looking for.  The fastest way to populate a listbox control isn't by not using one...  LOL.  Thanks for the effort by all, but if you want the points, make sure the one who's giving them out has a reason for selecting your answer.
0
 
LVL 57
ID: 40009547
OK now with the Expert hat on:


<<As for Jim, I chose Pat's answer, because it's the one that caused me to head down the right road.  If you're solution was the same as Pat's then I would have chosen yours.  Maybe the way you worded it was unclear or maybe if you spent more time focusing on the issue, rather than commenting on the field name... >>

  For the record, I have no issue with the way points were assigned and I've been in the situation where the shoe has been on the opposite foot.   It's always a guessing game as to how much or how little to comment at first.   Sometimes you win, sometimes you loose.  Beyond that, sometimes your words are clearer then someone else's and sometimes not.

 With that said, it's usually best if there is a little back and forth to feel out exactly what the issue is, otherwise the thread gets cluttered up with a lot of comments that may or may not pertain at all.   Right now, the PAQ is loaded with questions like that, but the effort to go back and clean them all up would be astronomical.

 For example, you didn't mentioned record set size.   If you were trying to stuff more then 50,000 records into a a list box (and believe me, it's been tried), then it would never work no matter what the search was like.   That's why I suggested figuring out where the problem was first.  But I also did say "Do a separate filter for", which you found not to be clear.  I have no problem with that.

 The only job at the end of the day in my book is try and get questions answered as quickly and clearly as possible.    That's often a thorny task, but we try to do our best

Jim.
0
 

Author Closing Comment

by:shannonds
ID: 40011198
Pat's solution jogged my memory as to what I needed to do.  My last comment is what I actually ended up doing.  I gave Pat all the points for his knowledgeable approach to the topic.  I selected mine as well, so that if someone is searching for the solution in the future, they can see what I ended up doing...
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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