?
Solved

Need to improve form filtering via combo boxes

Posted on 2012-03-28
7
Medium Priority
?
286 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 1200 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
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!

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 800 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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