UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Performance With SQL Server Back End., Access 2016    
 
   
shwan
post Jul 11 2019, 02:36 AM
Post#1



Posts: 26
Joined: 2-December 17



Hello everyone,
In SQL server backend and access front end, is there a difference, performance wise, between filtering the form records by:
A- Setting the form filter property in the form onload event.
B- Using a query with where clause as a record source on runtime.
I know it is recommended to pull the least amount of data necessary from SQL server backend to increase performance, option A is what I'm implementing now, but I wonder if this option is actually pulling all the records and then filtering them in the front end side, which is of course not wanted.

Thank you


Go to the top of the page
 
DanielPineault
post Jul 11 2019, 05:11 AM
Post#2


UtterAccess VIP
Posts: 6,709
Joined: 30-June 11



You're best to use pass-through queries which does the processing on the server rather than pushing all the records to Access to then be processed.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dale.fye
post Jul 11 2019, 05:45 AM
Post#3



Posts: 66
Joined: 28-March 18
From: Virginia


As much as I like to use the filter property, the best method is to change the RecordSource to include the filter criteria.

The down side of pass-through queries is that they are not updateable, so if you want to be able to update the data that is displayed you will simply need to update the SQL of your RecordSource to include the criteria. I frequently do this by storing the basic query as a saved query. Then, when I want to filter the recordset, I use something like:
CODE
strSQL = "SELECT * FROM [savedQueryName] WHERE " & strCriteria
me.RecordSource = strSQL
As long as your where clause includes only actual values and no user defined functions, SQL Server will likely be able to process the query and return only the applicable results.


--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
gemmathehusky
post Jul 11 2019, 06:48 AM
Post#4


UtterAccess VIP
Posts: 4,720
Joined: 5-June 07
From: UK


If you are filtering interactively, I don't think the database gets requeried - so given a form you can filter the visible records (ie the record set in memory) by using various ribbon and right-click options, or even purpose made buttons to sort and filter.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
GroverParkGeorge
post Jul 11 2019, 08:04 AM
Post#5


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


As noted, there are two aspects to your question:

Performance speed
Where filtering is performed.

As noted, performance speed with a SQL Server back end is impacted by the way you retrieve records, i.e. "Get 'em All and Then Filter", or "Get only the One I Need"

The other aspect has to do with the way Access processes requests for records from its back end tables. "... I wonder if this option is actually pulling all the records and then filtering them in the front end side, which is of course not wanted."

And the answer to that is probably that's how it's happening, depending on exactly how and when you apply the filter.

There is a common misunderstanding, I think, about whether Access is smart enough to optimize data requests. People sometimes assume that Access always downloads the entire recordset before applying filters. That can definitely happen, but not always. The ways you get around that have been outlined, use passthrus of paramaterized queries.

But there are cases where the way the parameterized are applied make it impossible for Access to ask for the filtered recordset, as Dale pointed out. So, doing the filtering on the Access side BEFORE asking for the recordset should be the most efficient. If you see slow performance, it's time to revisit the criteria in that recordset.

BTW. As a kind of tangentially related example, I recently ran into a situation where switching a Back End from Access JET to SQL Server created an entirely unexpected problem in this area.

The original mdb was designed to use the built-in Search facility:
Attached File  CtrlFSearch.jpg ( 57.7K )Number of downloads: 0


This approach, with the Access BE, worked perfectly well on a recordset returning over 30,000 records. I was suprised, based on my experience and understanding of good design, but it worked that way.

However, when we replaced Access BE for SQL Server, this search approach was ludicrously slow. I mean 10 or 15 MINUTES searching on an autonumber field.

After some research and discussion with MS people, we came to the conclusion that the problem was that highlighted option "Search Fields as Formatted". With it UNchecked, search times went back down to expected sub-second performance.

Our hypothesis was that telling Access to search using "formatting" meant every single record was evaluated differently than it would have been simply looking to see if the value matched the "Find What" criteria. Having found an approach that gave us the desired results, we didn't dig further into the why of it, although I've remained curious about exactly how that works.

My point is that we should not take for granted that performance will be one way or the other based on our intuitive understanding. Actual performance TESTING is a critical part of what we do.

As the acronym says, "YMMV"
This post has been edited by GroverParkGeorge: Jul 11 2019, 08:07 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Jul 11 2019, 08:19 AM
Post#6


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


So with Search Fields as Formatted works fast with Jet both checked and un-checked but with SQL Server it must be left un-checked?

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Jul 11 2019, 08:37 AM
Post#7


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


That was our experience, yes. BUT, I can't explain the underlying reason beyond an educated guess, so I can't be sure it will always be the case. Testing is the only sure way to know.

And, I should note that, while searches were most frequently done on the autonumber field, the form to which the table is bound does have many other text fields, so it may be that was also a factor.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nvogel
post Jul 11 2019, 12:52 PM
Post#8



Posts: 966
Joined: 26-January 14
From: London, UK


I suggest the OP may want to test this out for himself by using SQL Server Profiler. When I have tried setting the form filter property the filter criteria seems to be passed through and executed on the SQL Server side, which is what you might hope and expect. I'm sure there are scenarios when that doesn't happen but perhaps it's worth testing out to be sure.
Go to the top of the page
 
GroverParkGeorge
post Jul 11 2019, 02:51 PM
Post#9


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


That was indeed my expectation and I was quite surprised it didn't turn out that way in this case. Hence my waffling a bit.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AlbertKallal
post Jul 12 2019, 01:17 AM
Post#10


UtterAccess VIP
Posts: 2,819
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok a few things:


No, you don’t want to do that. You use the “where” clause of the forms (docmd.OpenForm “formname”,,,”where clause goes here”

If you open the form with no record source, and then set the filter then you would be ok (but the form don’t have a record source just yet, does it???).

You don’t want to open the form (it will start pulling records), and THEN set the filter.

However, you CAN safe set a forms filter command after the form has been loaded – a new SQL query is sent server side and it ONLY pulls the records that match the filter (so the filter is NOT occurring local against the given already loaded reocrdset). You will get a fresh new data load when setting the filter. So this approach is ok if you needing to re-filter the form after it been loaded.

So, using the form filter on the opencommand is the best way to go.

There is no benefit to use say some pass-through or other means to limit records (and besides a PT query is read only).

It also VERY important to note that REALLY (but REALLY REALLY) want to avoid attempting to filter ANY thing or ANY object based on a PT query (the access client cannot, and will not filter such PT queries).

So for example, NEVER based a bound combo box on a PT query and assume the “client” side will filter by the first column of the combo box. The client cannot resolve and filter a PT query.

So, for example, basing a report on a PT query, and THEN using the OpenReport “where” clause is a mess and does NOT filter the PT query server side.

Now, of course if you modify and setup the PT query BEFORE you open the report, then fine. But, in the quest to save world poverty and save you time?

If the query is complex, then create a view server side and AGAIN use the “where” clause of the open report. The HUGE added bonus here is that past “complex” filtering you did in VBA will continue to work as before, but only pull the records you require. So, at the end of the day, use GREAT caution with a PT query.

Again:
The access client in NO WAY can filter a PT query. You MUST provide the criteria in the PT query – not ever attempt to let the access client filter that PT query. The nastiest example is of course using a PT query to drive a bound combo box.

Rather amazing, but you can launch a bound form with say a combo box that is also linked to SQL server. The Access client will use it “brains” and of course not pull the whole table for the SQL that drives the combo box. It will pull only the FK id, and 99% of time of course your “text description” column that typical combo boxes displays (but with a PT query, it will NOT filter correctly).

While a PT query is likely the MOST high performance option you have with Access, the above shortcomings are significant and thus in most cases I avoid a PT query to drive things like a combo box, or say a report.

So, yes, if you have a bound form (to linked SQL server table) loaded, then you can most certainly add a filter in VBA, and the access client will send a NEW query and pull to the server – you ONLY get the records that meet this criteria.

However, even without SQL server, you really never wanted to load a form and THEN add the query (edit: add the filter). You want to use the “where” clause so the filter is applied before the form starts pulling data.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
shwan
post Jul 12 2019, 05:47 PM
Post#11



Posts: 26
Joined: 2-December 17



Thank you for all this valuable information. I love this forum.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 05:14 AM