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
> Remote Querying, Access 2013    
 
   
payfast8898
post May 12 2018, 09:50 AM
Post#1



Posts: 144
Joined: 23-April 15



I have a question and wanted to see if anyone else has tried this to see if it works.

say we have a database with 500000 records and want to pull them over the network.

if I set up a query on the back end where the data is stored that would filter 100 out of them 500000 records and use the SELECT [RemoteQueryname].* FROM [RemoteQueryname] IN 'C:\RemoteDatabase.mdb' on my local database would it make it faster?

In theory doing the calculating 100 records on the back end seems like it should work and I can send a single command to a temp table that is used for filtering purpose over the network.

example: say I have a temp table called temp and an temp1 field that I send a company id number to. Company 1.

company table invoices has 500000 records but I only want to see company 1 records so I send a command over the network to temp table 1 and put a 1 in the company id

I make a query on the backend machine called remote query which temp table and invoice table are linked by company querying 100 records.

now I send the command and pull from the remote query the 100 records onto my network machine.

will doing all the calculating on the server side speed up my data basing and if anyone has tried it is there any pit falls?
Go to the top of the page
 
GroverParkGeorge
post May 12 2018, 10:51 AM
Post#2


UA Admin
Posts: 33,488
Joined: 20-June 02
From: Newcastle, WA


What RDBMS is the Back End? Access accdb, SQL Server, Oracle, MySQL?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
payfast8898
post May 12 2018, 03:11 PM
Post#3



Posts: 144
Joined: 23-April 15



sorry I thought it showed I choose Access 13 but yes Access backend
Go to the top of the page
 
GroverParkGeorge
post May 12 2018, 03:20 PM
Post#4


UA Admin
Posts: 33,488
Joined: 20-June 02
From: Newcastle, WA


Well, you can actually use Access as the front end to a wide variety of other RDBMSs, and that can impact things like this, so we want to be sure.

I guess this is a case where trial and error is probably going to be your best teacher. It might be more efficient to pre-filter, but Access is actually quite good at optimizing queries unless you do things to make it use a brute force approach.

If you are just pulling raw rows from a single table, I would be surprised if a parameterized query in the FE runs any slower.

Give it a try and tell us what happens.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
AlbertKallal
post May 12 2018, 05:47 PM
Post#5


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


It will not make a difference if you use a linked table, or some query with “path” to back end.

If an index is on the column you filter, then in ALL cases the indexing system will be used and you ONLY pull the 100 records.

So really, likely just a standard linked table in your front end will work fine. Adopting some query with “path” to the back end will not change or help speed.

So at the end of the day, just a linked table is fine and you not have to mess with “path” and some query with “external” path names which can be a pain.

So no matter which way you query the back end, the “code” for the database engine is running ALWAYS on the workstation. However, Access is smart, and if an index can be used for the query, then only the 100 records are pulled across the network.

In fact you can build a form and bind it to a linked table that points to the back end database with that huge table. If you launch the form with a “where” clause, EVEN if the form is bound directly to that large table then ONLY the 100 records will come down the network pipe – not the whole table.

So you don’t need any fancy query or some query with external path name to the back end database. A simple standard linked table to that large database will suffice. And that form bound directly to the linked table will NOT pull all records if you open that form with a simple “where” clause. As long as an index is available for your criteria, then ONLY those records come across the network.

Edit:
What this means is if the 100 records can be indexed, then even with a large table, the form should load near instant.


Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
John Vinson
post May 15 2018, 12:51 AM
Post#6


UtterAccess VIP
Posts: 4,264
Joined: 6-January 07
From: Parma, Idaho, US


Just to clarify - what do you mean by "remote"? If you're pulling data across a fast, stable Local Area Network well and good - but Access does not "play nice" over the internet! If your "remote" data is only accessible by a packet switched Wide Area Network connection (cloud storage, VPN, or the like) you'll need to find alternate approaches!

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
payfast8898
post May 15 2018, 06:24 AM
Post#7



Posts: 144
Joined: 23-April 15



no just local networking, I was working with another kind of software that ask when setting it up do you want to do the querying on the front end or back so I was thinking can Access be faster if you linked to the query that is already filtered on the backend instead of the table that is not and you have to query it first on the front end before viewing it. In my head it seems it should be much faster to only pull 100 records instead of half a million for example.
Go to the top of the page
 
projecttoday
post May 15 2018, 06:36 AM
Post#8


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


It should not matter if you are only pulling 100 records anyway. Re-read Albert Kallal's post (#5).

I'm wondering what is this "other software" that you mention? What does it do?

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post May 15 2018, 07:03 AM
Post#9


UA Admin
Posts: 33,488
Joined: 20-June 02
From: Newcastle, WA


Right. As Albert directly stated and I hinted, Access is indeed smart enough not to pull all of the records across the wire unless YOU, the writer of the SQL, force it to do that with syntax that can't be resolved in the back end. In other words, one could say the folks who designed the ACE (then JET) database engine in Access understood this problem nearly 30 years ago and designed their product to be as efficient as possible way back when.

It's a fairly common myth--intentional or unintentional--that Access retrieves all of the records all of the time, but that's just what it is, a myth.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
GroverParkGeorge
post May 15 2018, 07:06 AM
Post#10


UA Admin
Posts: 33,488
Joined: 20-June 02
From: Newcastle, WA


There is another scenario where it can make a difference where you handle processes. When you are using linked tables and views in a SQL Server (or other RDBMS).

In those cases, there may very well be optimization possible in the SERVER environment that would not be as efficiently done locally in Access, but that's not the scenario here.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2018 - 05:34 PM