Changing record source quicker than applying filter against view

[1DMF]

Hi,

I have a data source table set to a view in SQL, when the form first opens showing the record set via the attached query it is lightening fast.

However, i have some unbound text fields which are used as a free text search facility, which I use to filter the records.

However, when the filter is applied it is painfully slow to the point of unusable.

Why on earth is querying the view or changing the recordsource quicker than apply filtering against the record set?

How can i speed up the filtering?





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

That is not normal behavior, so it is hard to say what is the real cause without seeing the actual query and the filter applied. You can try instead to dynamically build the sql string and apply it as the recordsource.

 

[1DMF]

the query...

SELECT dbo_vAccountsSearch.CompanyName, dbo_vAccountsSearch.Sub_Route, dbo_vAccountsSearch.Prod_Provider, dbo_vAccountsSearch.Prod_Ref, dbo_vAccountsSearch.Prod_Type, dbo_vAccountsSearch.CNames, dbo_vAccountsSearch.Postcode, dbo_vAccountsSearch.Sub_Date, dbo_vAccountsSearch.Comp_Date, dbo_vAccountsSearch.Status, dbo_vAccountsSearch.Commission, dbo_vAccountsSearch.Regulated, [Firstname] & " " & [Lastname] AS Adviser
FROM dbo_vAccountsSearch
WHERE (((dbo_vAccountsSearch.Ins_Lnk) Is Null))
GROUP BY dbo_vAccountsSearch.CompanyName, dbo_vAccountsSearch.Sub_Route, dbo_vAccountsSearch.Prod_Provider, dbo_vAccountsSearch.Prod_Ref, dbo_vAccountsSearch.Prod_Type, dbo_vAccountsSearch.CNames, dbo_vAccountsSearch.Postcode, dbo_vAccountsSearch.Sub_Date, dbo_vAccountsSearch.Comp_Date, dbo_vAccountsSearch.Status, dbo_vAccountsSearch.Commission, dbo_vAccountsSearch.Regulated, dbo_vAccountsSearch.Role, dbo_vAccountsSearch.Lender_Date, dbo_vAccountsSearch.Adv_MemNo, dbo_vAccountsSearch.FirstName, dbo_vAccountsSearch.LastName
HAVING (((dbo_vAccountsSearch.CompanyName)<>'' And (dbo_vAccountsSearch.CompanyName) Is Not Null) AND ((dbo_vAccountsSearch.Status)<>"NPW") AND ((dbo_vAccountsSearch.Role)<>"Locked") AND ((dbo_vAccountsSearch.Lender_Date) Is Null) AND ((dbo_vAccountsSearch.Adv_MemNo)<>"ITC_NBCS"));

The Filter code (applied via unbound text boxes onchange event)

Sub Do_Search(var As String)

    Dim srch As String
    
    srch = ""
    
    
    'Company Name
    If var = "Company" Then
        If Nz(Me.Company.Text, "") <> "" Then
                srch = "CompanyName LIKE '*" & Me.Company.Text & "*'"
        End If
    Else
        If Nz(Me.Company, "") <> "" Then
                srch = "CompanyName LIKE '*" & Me.Company & "*'"
        End If
    End If
        
    'Provider
    If var = "Provider" Then
        If Nz(Me.Provider.Text, "") <> "" Then
            If srch <> "" Then
                srch = srch & " AND "
            End If
            srch = srch & "Prod_Provider LIKE '*" & Replace(Me.Provider.Text, "'", "''") & "*'"
        End If
    Else
        If Nz(Me.Provider, "") <> "" Then
            If srch <> "" Then
                srch = srch & " AND "
            End If
            srch = srch & "Prod_Provider LIKE '*" & Replace(Me.Provider, "'", "''") & "*'"
        End If
    End If
    
    'Client
    If var = "Client" Then
        If Nz(Me.Client.Text, "") <> "" Then
            If srch <> "" Then
                srch = srch & " AND "
            End If
            srch = srch & "CNames LIKE '*" & Me.Client.Text & "*'"
        End If
    Else
        If Nz(Me.Client, "") <> "" Then
            If srch <> "" Then
                srch = srch & " AND "
            End If
            srch = srch & "CNames LIKE '*" & Me.Client & "*'"
        End If
    End If
    
    If srch <> "" Then
       'Forms!Accounts_Search!accounts_search_results.Form.RecordSource = "SELECT * FROM dbo_vAccountsSearch WHERE " & srch
       Forms!Accounts_Search!accounts_search_results.Form.Filter = srch
       Forms!Accounts_Search!accounts_search_results.Form.FilterOn = True
    Else
       Forms!Accounts_Search!accounts_search_results.Form.FilterOn = False
    End If

End Sub

You'll see where I was playing with changing the recordsource which works faster than applying the filter?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

So your question is Why is it faster? Not how to make it faster?

I am not certain, but I think when you apply a filter to a form, it takes the original recordset and applies a filter to the recordset. It does not build a new recordsource. You can do this manually in dao to replicate, how it is done.

dim rs as dao.recordset
dim rsFilter as dao.recordset
dim strFilter as string

set rs = me.recordsetclone
'code to build filter string
rs.filter = strFilter
set rsFilter = rs.openrecordset
set me.recordset = rsFilter

Why that is slower in this specific case than just returning a clean set of records, I do not know. I do not find this to be the case in general.

 

[PHV]

Why using a GROUP BY clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

MajP -> I've decided to take the .NET disconnected model approach, so I created an actual query and have a local empty table, use an insert(select) and bound the form to the local table, now it's lightening fast!

PHV -> you tell me , the query wouldn't work with Access moaning that the fields weren't part of the group by clause?

I didn't write that SQL access did via the query builder screen!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

I don't see any aggregate function in your SELECT list, so why a GROUP BY clause ?

 

[1DMF]

It says i cannot do the 'Adviser' expression unless i include 'FirstName' & 'LastName' in the group by clause.

So what am i doing wrong?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

On a side note, I created the query as an append, to poulate the local table with the data , but it's very slow in running the query.

i tried to use VBA and an INSERT(SELECT) but I can't get the syntax right.

CurrentDB.Execute("INSERT INTO myTable (myQuery)")

Can you help with the correct VBA syntax please.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

What about simply this ?

SELECT CompanyName, Sub_Route, Prod_Provider, Prod_Ref, Prod_Type, CNames, Postcode, Sub_Date, Comp_Date, Status, Commission, Regulated, Firstname & ' ' & Lastname AS Adviser
FROM dbo_vAccountsSearch
WHERE Ins_Lnk Is Null AND CompanyName)<>'' AND Status<>'NPW' AND Role<>'Locked' AND Lender_Date Is Null AND Adv_MemNo<>'ITC_NBCS'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Yes, if I was writing the SQL, I don't understand what you are getting at PHV, access wrote the query using the query design sheet thingy.

Is there a problem designing queries using the query design wizard ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

using the query design wizard
I personally never use the broken query grid as it generates ugly SQL code.
Seems like you asked the query wizard for a Total query despite you didn't need it ...

 

[1DMF]

But if you don't click the (Z) totals button you cannot select 'Expression' or 'Where' from the drop down list?

anyway we digress, the point was why is filtering of a recordset so slow, when the initial query is so fast?

I bound the form to a query and when the form opens it's instant, as soon as i try to apply a filter it dies?

i thought all this had been sorted in Access 2010, but it hasn't it still takes an eon to delete a record from a bound sub form data view, something else MS claimed to a fixed but clearly haven't!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

Did you try to apply the filter with a NON-aggregate query as RecordSource ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Yes, it makes no difference.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

I thought I'd try MajP's method but all i get is 'object doesn't support this property or method'

Forms!Accounts_Search!accounts_search_results.Recordset = rs

or

Forms!Accounts_Search!accounts_search_results.Form.Recordset = rs

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Well not exactly. I wrote
set rs = me.recordsetclone
you wrote
Forms!Accounts_Search!accounts_search_results.Form.Recordset = rs

Your missing a Set. But again this was not meant to be a fix, but to show you how it applies the filter. Therefore it should be as slow, I would think.

However, when you compile a query it gets optimized through Rushmore optimization. So for example instead of returning a million records and then applying your where clause to return 10 records, it might filter the left side first and thus only ever return 10 records. So that is my guess of what is happening. By building the SQL string it returns a more optimized set of records, instead of returning all the records and then filtering. That is why I said it is case dependant and not always an issue.

One question, instead of the local table could you instead just return a recordset and bind it to the form? Could save a step and be even faster.

If you optimize the original query like PHV suggests, you may find the filter works fine also.