Solved

Need to improve form filtering via combo boxes

Posted on 2012-03-28
7
284 Views
Last Modified: 2012-04-02
Hello ,

I'm working on a form (Default View is Continuous Forms" that is populated at form load time via the following
trRecordSource = "SELECT dbo_vwPivotedReviews.USI, dbo_vwPivotedReviews.WorkStream, dbo_vwPivotedReviews.GFP, " & _
                       "dbo_vwPivotedReviews.Review1 , dbo_vwPivotedReviews.Status1, " & _
                       "dbo_vwPivotedReviews.Review2 , dbo_vwPivotedReviews.Status2, " & _
                       "dbo_vwPivotedReviews.Review3 , dbo_vwPivotedReviews.Status3, " & _
                       "dbo_vwPivotedReviews.Review4 , dbo_vwPivotedReviews.Status4, " & _
                       "dbo_vwPivotedReviews.Review5 , dbo_vwPivotedReviews.Status5, " & _
                       "dbo_vwPivotedReviews.Review6 , dbo_vwPivotedReviews.Status6, " & _
                       "dbo_vwPivotedReviews.Review7 , dbo_vwPivotedReviews.Status7, " & _
                       "dbo_vwPivotedReviews.Review8 , dbo_vwPivotedReviews.Status8 " & _
                       "FROM dbo_vwPivotedReviews "
                       
    strClearFilterSource = strRecordSource
                       
    Me.RecordSource = strRecordSource

dbo_vwPivotedReviews is a linked view to a sql server database.  The form seems to be populated quickly enough.  The problem lies in filtering the form
I have about 10 comboboxes on the form which allows the user to filter the forms recordsource via selections made from the comboboxes in the following manner

  If strWhereClause <> "" Then
        strWhereClause = strWhereClause & " AND " & "[Review1] = '" & cmbFilterByReview1 & "'"
    Else
        strWhereClause = " WHERE " & "[Review1] = '" & Me.cmbFilterByReview1 & "'"
    End If
   
    Me.RecordSource = strRecordSource & strWhereClause

This is the part that is running a bit slow.  I'm thinking that perhaps the filtering would have less of an impact on performance, if I do it at the server via T-SQL.  Would that be the way to go.  By the way the application is an Access 2007/Sql Server 2005 application
0
Comment
Question by:chtullu135
[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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 300 total points
ID: 37777525
Let me suggest you read the EE article below, by EE's Harfang ... and his other articles also.  They will contain everything you need to know re how to improve performance in this regard:

Start HERE

I can assure that this is as good as it's going to get.  I have tested and implemented these concepts.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37777714
<This is the part that is running a bit slow. >
Please quantify "a bit slow",  in real chronological terms.
"A bit" is not that much in my opinion...

It might be a bit much to investigate a 100ms time difference, that might just be "what it is"

I know that running a query on the server can be faster, I am not sure about a form's recordsource, though...?
0
 

Author Comment

by:chtullu135
ID: 37777909
It takes about 4 seconds to filter the form after a selection is made via a combo box
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 37778584
The issue is that while you seem to be just "Filtering", you are also "reloading" the entire recordsource.
This is like doing a requery in terms of the resources Acess needs to do this.
So you are pulling the entire new recordsource over from the server each time.

The "Multiple comboboxes" used for this will also slow things down.

The network itself may also be a factor,...only testing will tell...

Try this...
Put a button on the form that simply does this:
    me.requery

...(see how long it takes)

Then try this:
me.recordsource=me.recordsource
me.requery

Then try this:
me.recordsource=SELECT * FROM dbo_vwPivotedReviews
me.requery

JeffCoachman
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37780290
You can also try filtering the form instead of reloading the recordsource.
Me.Filter =  "[Review1] = '" & cmbFilterByReview1 & "' [Review2] = ...
Me.FilterOn = True

Another way is to index your fields but in your case, indexing 10 fields may impose some overhead whenever you try to update, insert to, or delete from your table.
0
 

Author Closing Comment

by:chtullu135
ID: 37796575
Thanks everyone.  I particulary liked the link to the article posted by DatabaseMX.  boag2000 test suggestions were also very helpful.  I've decided to create a stored procedure to do the actual filtering. In addition, the tables in question will get additional indexes to speed filtering
0
 
LVL 75
ID: 37797063
I highly recommend reading ALL the articles by Marcus, as I consider him and Leigh Purvis (LPurvis) to be the top two Access minds on the planet.

mx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

730 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