Access Native Query vs sql View

[KitaYama]

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.

 

[June7]

Did you search this topic? Review https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query

 

[GPGeorge]

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]

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.

 

[KitaYama]

Did you search this topic? Review https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query

No. I'm reading it now and will be back soon.
thanks.

 

[KitaYama]

Did you search this topic? Review https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query

@June7 that article was great. I didn't know I can set indexes on views.
I think I need to research and study more about it.

thanks again.

 

[GPGeorge]

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]

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.

 

[isladogs]

For info, you can add an index to a SQL view from Access which means the view data is then editable

Create SQL View Index

This article explains how to create an index on a SQL Server view in Access so that the data can be edited

www.isladogs.co.uk

 

[oleronesoftwares]

All forms are bound to queries

Consider using an unbound form, i.e a form that the record source is inserted in VBA,and not the form property.

 

[isladogs]

Consider using an unbound form, i.e a form that the record source is inserted in VBA,and not the form property.

Why?

 

[oleronesoftwares]

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]

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.

 

[oleronesoftwares]

Unless you load them with every underlying record, which is not how they should be used

So are you suggesting a query?

 

[isladogs]

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

 

[oleronesoftwares]

Unbound forms require far more code to manage and their use loses most of the RAD advantages provided by Access

I agree with it losing most of RAD advantages.

 

[CJ_London]

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]

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]

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]

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?