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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Some Fe/be Clarifications Please, Access 2016    
 
   
firlandsfarm
post May 25 2019, 12:49 AM
Post#1



Posts: 366
Joined: 28-April 02
From: Heathfield, England


I understand an FE/BE structure but what goes on within such an arrangement.

If I run a query from the FE that interrogates only BE held tables does the whole query get sent to the BE to resolve like a Pass-Through or does it perhaps request the complete data sets required and then filtering etc within the FE ... or maybe something completely different? Speed is of the essence here because I have large tables.

Is there any difference in the relationship between a FE/BE arrangement and simply linking a table from another Access database using the External Data options?

Is the slowest link going to be where some of the data being queried is in the BE and some in the FE, or when linking data in a query on the FE with data in a table in the BE? I find Access often asks me to build my queries in stages presumably because it finds my request too complicated and needs to resolve it one layer at a time.

Are there any other considerations to think about before making the split?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Phil_cattivocara...
post May 25 2019, 02:33 AM
Post#2



Posts: 267
Joined: 2-April 18



For this thread, what kind of BE do you refer to? an Access file or another RDMBS (SQL Server, MySql and many other)?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
firlandsfarm
post May 25 2019, 05:30 AM
Post#3



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Sorry Phil, I'm referring to an Access FE and BE ... I didn't think to clarify because I tend to see anything on UA is Access unless specified not! smile.gif The position with Pass Throughs would be different because with Access --> SQL Server you can assign that a quesry should be Pass Through or not. Maybe you can if it's Access --> Access, I had not thought to explore that ... maybe I should! smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Phil_cattivocara...
post May 25 2019, 06:25 AM
Post#4



Posts: 267
Joined: 2-April 18



I asked only to be sure, do not warry. I learned me too only some days ago that query pass through can be done with any kind of BE, with Access file Back End too (I did not know) but this is unusual because a query pass through with an Access BE does not give any valuable advantage. With other RDBMS you can view a great difference.
This post has been edited by Phil_cattivocarattere: May 25 2019, 06:31 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
firlandsfarm
post May 25 2019, 06:31 AM
Post#5



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Well now I know I can do my own Pass Through to an Access BE I will do some time trials. Thanks Phil.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Phil_cattivocara...
post May 25 2019, 06:35 AM
Post#6



Posts: 267
Joined: 2-April 18



I was trying to find a page that explained advantages and tips when there is a FE and BE with Access FE but... I cannot find it any more. Sorry.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
gemmathehusky
post May 25 2019, 07:11 AM
Post#7


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


the FE query produces a query plan, which determines the best way to retrieve the data from the backend - so it tries to restrict the amount of data moving around your network. For this reason using a stored query is slightly more efficient than putting the query as an inline expression within say a combo box record source - although Access may be smart enough to sort this.

Therefore to try to minimise the size of your query.

Try to add criteria to minimise the number of rows returned.
Selecting certain fields/columns rather than all fields.
Adding indexes judiciously to assist the query plan design.
Try to avoid functions that require every row of the target table to be evaluated. If you must do this (ie have to, because you can't see a way to index what you need), then this is one occasion where it is worth archiving data into an archive table.
Avoiding domain expressions within queries

All of these will speed up data management. Try googling "writing efficient queries", or similar.

eg https://searchsqlserver.techtarget.com/feat...ficient-queries

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

(Gemma was my dog)
Go to the top of the page
 
Jeff B.
post May 25 2019, 07:44 AM
Post#8


UtterAccess VIP
Posts: 10,254
Joined: 30-April 10
From: Pacific NorthWet


When you say "large tables", would you describe what that means to you?

If performance is an issue, are you saying human users can notice the performance issue, or that there are millisecond differences?

More info, please...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post May 25 2019, 08:05 AM
Post#9


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


The short answer to the first question is that it depends to some extent on how you write the query. Access will normally request only the data required and will not bring the entire recordset over to do the filtering. However, you can, I understand, write queries in such a way as to force Access to retrieve all of the records first. I don't recall examples off the top of my head, but I've seen discussions of that.

"Is there any difference in the relationship between a FE/BE arrangement and simply linking a table from another Access database using the External Data options?"

An FE/BE arranging arrangement IS when you link tables from another Access database using the External Data feature. In other words, FE /BE is merely a convenient shorthand for "Interface and logic objects in one accdb and data objects in another accdb." We use Front End and Back End because that's the term commonly employed among Access developers.

It's likely that you'll see the slowest performance when joining tables in the local (FE) accdb with tables in a remote (BE) accdb. If you run queries on tables only in one or the other, that performance hit is likely to be less.

The problem of subqueries is not unique to Access. It's a matter of the complexity of the logic needed to compile and aggregate data. Moving queries to a server-based database like SQL Server can't change that, although there might be more ways to get the job done, such as the use of Common Table Expressions, or CTEs.

Other considerations? The only one I can think of at the moment is that you may find a traditional Access FE design to be inefficient when linking to a remote server. Specifically, if your forms load an entire table as a recordset and then apply filters to find one record in it, you're bound to be disappointed in performance when changing to a SQL Server backend.

As Phil pointed out, writing pass-thru queries against accdb tables is not likely to offer any advantage. However, when working with SQL Server tables, they can be very effective. Keep in mind that they are always read-only.
This post has been edited by GroverParkGeorge: May 25 2019, 10:14 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
 
isladogs
post May 25 2019, 08:52 AM
Post#10


UtterAccess VIP
Posts: 1,413
Joined: 4-June 18
From: Somerset, UK


You might find this article on my website useful Optimising Queries
This covers a number of different steps you can use and measures the times taken in each case for comparison.
Although not specifically for split databases, most of the info is still relevant.

You can also use the undocumented JET ShowPlan feature to view the query execution plans. See this article and utility Jet Show Plan Manager

--------------------
Go to the top of the page
 
firlandsfarm
post May 25 2019, 10:58 AM
Post#11



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Jeff B ... the main tables are ...

tbl1: 3,877,004 records x 31 fields
tbl2: 2,278,164 records x 125 fields
tbl3: 226,606 records x 38 fields
tbl4: 182284 records x 18 fields

There are various look-ups supporting these files but they are tiny in comparison.

tbl2, tbl3, and tbl4 are linked from an SQL Server 2008 R2 database, and that's how they will stay because it is 3rd party supplied and updated.

tbl1 is an Access table but takes up so much room that many queries exceed resources, sometimes even when just sorting the table, probably because of clutter (the db is compacted whenever closed). Grouping and sorting is a definite no-no! As this table consists of 5 mutually exclusive categories relating to countries (GB, Ire, USA, Aus and RSA) and I never have a need to combine/mix the data from any two or more countries I'm thinking of splitting it and maybe that will also have a speed advantage when looking up what will be smaller tables. Should I need to merge the countries later I can always do a Union Query.

This is why I was asking if to get Access to perform the split has any advantage over just simply putting the split tables into 2 or more databases and just linking to them from the FE database.



--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
firlandsfarm
post May 25 2019, 11:28 AM
Post#12



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Thanks for responding George. I know a FE/BE setup is where the BE tables are linked to the FE. As I said "I understand an FE/BE structure". I was asking what's underneath the obvious ... does Access add any go faster, perform better tweaks if you do it via the Access facility rather than just manually divide and link? From your response I assume not.

"It's likely that you'll see the slowest performance when joining tables in the local (FE) accdb with tables in a remote (BE) accdb. If you run queries on tables only in one or the other, that performance hit is likely to be less." That's what I expected from my linking of SQL tables to Access so I planned on putting all tables in the BE's. But sometimes it will be joining a query in the FE with a table in the BE because Access says it cannot do something in one sweep. frown.gif If I have to put queries in the BE's it rather makes having a central FE pointless, I might as well just have different databases with queries and split solely to distribute space requirements.

"The problem of subqueries is not unique to Access." I know but that doesn't make it any less frustrating when you are dealing with different datasources as I am (see my response to Phil).

"you may find a traditional Access FE design to be inefficient when linking to a remote server." The SQL Server in on my computer. smile.gif

"Keep in mind that they are always read-only." I'm not so sure, I have been able to manually highlight and delete records and use append queries on linked SQL Server database tables from an Access FE. But I have not tried actual manual record entry. You can also use Truncate Table from an Access FE query.
This post has been edited by firlandsfarm: May 25 2019, 11:36 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post May 25 2019, 11:37 AM
Post#13


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


I see. Thanks for setting me straight.

I think you won't find splitting one table or tables into two or more tables in different accdbs might not make much difference in performance, although it will probably lessen the problem of any one accdb exceeding limits on file size.

This whole thing probably belongs in SQL Server, given the problems you're encountering with Access tables, but I would consider installing a newer version of SQL Server Express(free download) for that.

--------------------
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
 
isladogs
post May 25 2019, 11:53 AM
Post#14


UtterAccess VIP
Posts: 1,413
Joined: 4-June 18
From: Somerset, UK


I thought George was referring to passthrough queries which AFAIK are always or at least normally read only.
However Access queries based on SQL linked tables are editable (providing the same query would be editable based on a local table)
SQL Server views are read only in Access unless a PK field is assigned to the view when linked in Access.

--------------------
Go to the top of the page
 
firlandsfarm
post May 25 2019, 12:15 PM
Post#15



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Thanks Colin but sorry, what is "AFAIK" and "PK field"?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
isladogs
post May 25 2019, 12:17 PM
Post#16


UtterAccess VIP
Posts: 1,413
Joined: 4-June 18
From: Somerset, UK


AFAIK =as far as I know
PK = primary key

--------------------
Go to the top of the page
 
firlandsfarm
post May 25 2019, 12:28 PM
Post#17



Posts: 366
Joined: 28-April 02
From: Heathfield, England


"This whole thing probably belongs in SQL Server" Yep, it probably does George but even that's not straighforward. The current SQL data is held in a 3rd party installed and updated SQL database and I don't want to mess with that so to emigrate the current Access data to the SQL server is possible but it would require me to open another database. I have tried that and used SQL views when tables from each database were queried (sunonyms did not work because you can't link a synonym table to Access, it can't see it! So having decided I would just have to work around that (shortcoming!) I next became bored with the difficulty in getting SQL to even present a date in the format I wanted! Yes I know they say you can choose the format but despite raising it here and trying every bit of advice the Internet could throw at me it wouldn't reformat ... but then as if it was playing with me, if I open the same date field through Access the date format is perfect without me doing anything. If at first you don't succeed try try again but don't be stupid banging your head against a brick wall that will not budge! pullhair.gif


--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
firlandsfarm
post May 25 2019, 12:31 PM
Post#18



Posts: 366
Joined: 28-April 02
From: Heathfield, England


Thanks Colin, never heard those abbreviations before ... for all I knew PK is a brand of peanuts and AFAIK sounded more like a Middle East town! smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
nvogel
post May 26 2019, 04:25 AM
Post#19



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


I also suggest you migrate all the data to SQL Server. Sounds like you have done that already. Trying to combine data from Access and SQL Server is probably about the worst way to proceed because it seems likely you'll erase any advantage you might have got from using SQL Server in the first place.

The date formatting problem should be very easy to fix but we may need more details. Understand that SQL Server dates or datetimes do not have any format in themselves. The formatting is not controlled by SQL Server, it is controlled by Access or any other application you use. You should be able to set the date format for SQL Server dates exactly as you can for Access dates.


This post has been edited by nvogel: May 26 2019, 04:48 AM
Go to the top of the page
 
gemmathehusky
post May 26 2019, 06:50 AM
Post#20


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


In passing, I can't see any reason why a datetime in access would behave any differently to a datetime in SQLServer.

A datetime is only a double, when all's said and done.

How exactly are you trying to format it?

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

(Gemma was my dog)
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 04:01 PM