Solved

Need to improve form filtering via combo boxes

Posted on 2012-03-28
7
280 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
Comment Utility
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
Comment Utility
<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
Comment Utility
It takes about 4 seconds to filter the form after a selection is made via a combo box
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how the fundamental information of how to create a table.

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

13 Experts available now in Live!

Get 1:1 Help Now