Solved

Need to improve form filtering via combo boxes

Posted on 2012-03-28
7
285 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

717 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