Access Native Query vs sql View (1 Viewer)

KitaYama

Well-known member
Local time
Tomorrow, 03:20
Joined
Jan 6, 2022
Messages
1,489
scenario:
FE : Microsoft Access 365
BE : SQL Server Express
Clients count : 10. But it may be more in a while.
Using DSNless linked tables
Orders table has more than a million records. Other tables several thousands records.
All forms are bound to queries.

Which one is faster? A query or a linked view? (for Select & update actions)

Any insight or link to study is much appreciated.
 
Last edited:

GPGeorge

Grover Park George
Local time
Today, 11:20
Joined
Nov 25, 2004
Messages
1,776
scenario:
FE : Microsoft Access 365
BE : SQL Server Express
Clients count : 10. But it may be more in a while.
Using DSNless linked tables
Orders table has more than a million records. Other tables several thousands records.
All forms are bound to queries.

Which one is faster? A query or a linked view? (for Select & update actions)

Any insight or link to study is much appreciated.
Unfortunately, like many, if not most such tasks in Access, it depends.

Access is actually pretty darn smart about basic queries. For example,

SELECT ProductID, ProductName
FROM TblProducts
WHERE tblProducts.ProductID = 923456

That query will return one record or no records from the linked server. I say "no records" because, of course, 923456 must actually exist in the table as a ProductID.

A view created in SQL Server with that SQL hard-coded into it (which, of course, we would not do), will also return one record or no records from the linked server if you link to that view as you would any other SQL Server table.

However, if the query requires a lot of complexity, involving multiple joins and other complex calculations, it's going to perform faster as a view on the server most of the time. (Never say "never" or "always".)

So pick those queries which appear to be the most complex and start trying them out as views on the server and stop moving them when you no longer see speed differentials between those linked view and local queries.
 

KitaYama

Well-known member
Local time
Tomorrow, 03:20
Joined
Jan 6, 2022
Messages
1,489
A view created in SQL Server with that SQL hard-coded into it (which, of course, we would not do), will also return one record or no records from the linked server if you link to that view as you would any other SQL Server table.
Why should I hard code a view with a where clause? Maybe my question wasn't clear enough.

It's what I can do:
Create a view in SQL server that returns all orders : "SELECT * FROM tblOrders"
Then import the view into FE as a linked view.
Now I can use the view to open a filtered recordset in vba to work on it or use it as a record source of a form.

OR

Create necessary native queries based on linked tables in FE
And work on these queries.

Now my question:
Which one is faster or more efficient?

Thanks.
 
Last edited:

GPGeorge

Grover Park George
Local time
Today, 11:20
Joined
Nov 25, 2004
Messages
1,776
Why should I hard code a view with a where clause? Maybe my question wasn't clear enough.

It's what I can do:
Create a view in SQL server that returns all orders : "SELECT * FROM tblOrders"
Then import the view into FE as a linked view.
Now I can use the view to open a filtered recordset in vba to work on it or use it as a record source of a form.

OR

Create necessary native queries based on linked tables in FE
And work on these queries.

Now my question:
Which one is faster or more efficient?

Thanks.
My answer stands, although the example might have escaped you somewhat. What I said, exactly, was this.
A view created in SQL Server with that SQL hard-coded into it (which, of course, we would not do),
Only by limiting the returned rows can most straight select queries improve speed significantly. That's because Access is very good at marshalling such SQL calls. A parameterized Access query, whether based on a view like the one in your example or based on the table itself, will perform really well.

Only when your view involves rather more complex SQL will the improved performance of the server manifest itself.

That said, you can verify this by performing some performance testing with your data, along the lines you've outlined. Create a view and link it to an accdb along with the base table. Write various queries against them both. Time them. You'll answer the question better in two ways. FIrst, you'll know first hand what YOUR data is going to do in YOUR environment, and second, you won't have to rely on opinions, however well founded, of others.
 

KitaYama

Well-known member
Local time
Tomorrow, 03:20
Joined
Jan 6, 2022
Messages
1,489
Only by limiting the returned rows can most straight select queries improve speed significantly.
So if both (a saved query def in access and a linked view) return the same result (record count), is it safe if I assume they are the same (performance wise)?

In case of the test you mentioned, I can time the result, but there MAY be a lot of other benefits that one may have over the other (If any). For example the load on the server, network terffic, the load on domain controller etc.
I don’t know what I’m talking about makes sense or not but take it this way. Most Access developers are against table level lookups, or attachment fields or even multi data type fields. It’s because they cause a lot of troubles in future.

I just wanted to see what experienced developers here (I believe you one of them) have to say.
But per your advice, I do some tests and will be back with results.

Thanks for your help and advice.
 

oleronesoftwares

Passionate Learner
Local time
Today, 11:20
Joined
Sep 22, 2014
Messages
1,159
Hi @isladogs , using unbound forms is one of the major reasons enterprise applications development framework like .Net, Java(Net beans) etc handles several multiple users simultaneously very well.

This approach if used in ms access, will make the processing faster, generally, for robust access applications, i hard code both the form's record source and the form controls control source.

Processes, properties etc done in codes tend to make the application lighter and processing faster.
 

Minty

AWF VIP
Local time
Today, 18:20
Joined
Jul 26, 2013
Messages
10,355
Bound data entry forms aren't resource heavy?
Unless you load them with every underlying record, which is not how they should be used.

I almost exclusively work with cloud-based Azure SQL datasources, and use bound forms all the time.
It's only normally very strange circumstances that require an unbound form solution, and I try and keep them to a minimum.
By hand manoeuvring all the data input, you are creating a significant coding overhead.
 

isladogs

MVP / VIP
Local time
Today, 18:20
Joined
Jan 14, 2017
Messages
18,186
Using a record source based on SQL rather than a table or query is a perfectly good solution that I use regularly.
Whether it will be faster is another matter entirely.

However, doing that is not un unbound form. An unbound form has no record source and is only rarely beneficial as a solution in Access.
Unbound forms require far more code to manage and their use loses most of the RAD advantages provided by Access
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:20
Joined
Feb 19, 2013
Messages
16,553
All forms and reports should be using a query - unless for some rare reason you want all the records. Most of mine return only one record for a single form and for continuous forms I try to return only a pageful at a time with next page/previous page navigation. Basically my rule of thumb is to avoid the need for scrollbars. I try to avoid using filters and instead modify the recordsource criteria.

In reality, how many users want to manually scroll through thousands of records? I suggest they want to filter or find to find the records required - so use criteria rather than the form filter. Imagine a table with 26,000 records and the user want to filter on a name. Very simplistically, using a table recordsource, 26,000 records are brought through before any filtering takes place. If the form 'knows' the name (though a pre filtering process) begins with 'A', only a thousand records are brought through. If the user has typed 'AC' only 38 records are brought through - simplistically (again) so some 650 times faster. The overhead is creating the connection, but easy enough to keep an open connection if required.

It's not that noticeable with tables with relatively few records, but if there are perhaps 20,000 maybe more, then the effects become noticeable. As a test, open a table and note how long it takes for the recordcount at the bottom to be populated. Simplistically, that is how long it takes before your form opens or becomes useable. You can use .movelast but in my experience there is no discernible difference, it doesn't speed the time the form takes to open.

There is a lot more to it than that with regards performance. Indexing in particular. But why do you think web sites show single items and short lists? it's to keep the amount of network traffic down.

i hard code both the form's record source and the form controls control source.
How is that not a bound form? An unbound form would not have a recordsource or any value in the controlsource
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:20
Joined
Feb 19, 2002
Messages
42,971
Access makes every effort to make every query a pass through query. That doesn't mean you will get identical performance but in most cases, you won't be able to distinguish a difference UNLESS, you have used something in your Access querydef that prevents ODBC from sending the query directly to the server.

Views would not logically be faster than tables. However views that join several tables might be faster.

Tables/views that have useful indexes will always be faster than those without, unless of course, you don't use criteria.

And that leads us to what the others have said, to get the best performance from your Access FE, you want the SQL Server BE to do the heavy lifting and so therefore, you ALWAYS want to use criteria in the bound RecordSource query AND you want to limit the columns selected to only those you need so no queries of "Select * from tableA" should ever be used as the RecordSource.

I've been using Access as a FE since the early 90's. Originally against IBM's DB2. Mostly these days it's SQL Server or occasionally Oracle. In ALL cases, I use bound forms/reports and Access querydefs. If something is slow and I can't fix it with an index or an updateable view, I will look into other options. The key is always limiting your data selection to the absolute minimum. It is better to go back to the well 25 times or even a hundred during a session than to bring down 50,000 records and filter locally. The larger your tables, the more important this concept is. Also, be aware of the VBA functions that have direct translations to T-SQL because anything else will have to be processed locally. If your UDF or VBA function is in the Select clause, that's not a big deal, Access brings back the requested record and then applys your function locally. However, if the function is in the where clause or order by for example, you force Access to send the query to the server with no criteria and then perform the selection itself locally.
 

oleronesoftwares

Passionate Learner
Local time
Today, 11:20
Joined
Sep 22, 2014
Messages
1,159
How is that not a bound form? An unbound form would not have a recordsource or any value in the controlsource
That the reason i wrote. "i hard code" meaning the control source property in the design view is empty, its in vba that the control source is set .
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:20
Joined
Feb 19, 2013
Messages
16,553
so what do you put in the controlsource? the name of a field? or it's value? If the latter, how do you deal with continuous forms/datasheets?
 

Users who are viewing this thread

Top Bottom