Using Access as FE and external database BE - queries performance

[jaryszek]

Hi Guys,

where do you write queries when you are working with BE like SQL server ?

Are you using pass through queries ? And select only data?
Or you are referring to functions from FE in BE but all aggregations you are doing in BE?

Queries in Access are temporary tables which are taking space.
i mean i can have all tables in BE but writing queries in FE will take space...
Maybe all queries and views should be in BE and in FE i should only show snapshots from BE?
What are your approaches?

Best Wishes,
Jacek

 

[The_Doc_Man]

Actually, on this fine point, the space taken by a query is just a list of pointers used for data retrieval, nothing else. A query is NOT a temporary table.

Having said that, there is nothing wrong with the idea of having passthru queries and there is nothing wrong with having stored views on the BE server. Either way works. What is far more important is to assure that you have indexes on the tables you want to query.

I can't tell you much about SQL Server - in my site, we used ORACLE as our BE and had to use SQLnet or ODBC linkage to get to the data. Didn't use SQL server that much. At least, I didn't. But others here will tell you lots more. And I have seen articles on making an SQL Server query more efficient.

Use the SEARCH option of this forum to look up "Query speed" and "Query efficiency" as sources for many articles, some quite recent, on the topic. They will discuss things in greater detail than I could.

 

[Cronk]

My experience is that pass through queries will be generally faster - for small data sets, insignificant but for larger ones, very significant.

 

[jaryszek]

thank you Guys,

what is alternative for SQL pass-through query?
With pass-through query you can send command directly to BE, but what if you are creating forms. Still will you use pass-through query?

How to change in real time data in tables via for example input forms. Using pass-through?

Best Wishes,
Jacek

 

[NauticalGent]

what is alternative for SQL pass-through query?
With pass-through query you can send command directly to BE, but what if you are creating forms. Still will you use pass-through query?

Here is a link to Steve Bishop's channel. In this particular video, he connects a form directly to the SQL Server. I myself have been wanting to try it but have not made the time.

If you give it a go, come back here and let us know!

https://youtu.be/0Qi_EGitNhk

 

[pbaldy]

I generally start with forms bound to linked tables and Access queries for reports, etc. I switch to pass through queries, views, etc when performance is an issue. Access is pretty good about passing SQL back to the server and getting back just the necessary records, so a good deal of the time an Access query can be just as fast. It's when Access can't pass the work back to the server that performance suffers and we have to find alternate methods.

 

[isladogs]

The attached example uses a similar idea to that in Steve Bishop's video but for an Access BE.
Full explanation given in the Word doc included in the zip file

The FE contains 2 forms, 1 report but no linked tables.
The record source for each form & the report are created when the object is opened & destroyed when it is closed.
So there are no connection strings visible anywhere

The same idea can be used with SQL Server tables.
This obviously is another way of adding security if needed.
I doubt performance is noticeably different to using standard linked tables
If using a passthrough query, it should be faster

 

[The_Doc_Man]

To amplify/clarify Paul's comment,...

If you create a query that CAN be passed back to the BE, that is what will happen. BUT if you have a query that contains a function defined in the FE, the BE cannot possibly execute it, so the whole domain has to be brought back to the FE for processing. Moral of the story? Look up all of the functions available on your BE server and use THEM in the queries intended as passthru. If you cannot limit yourself to such functions, expect any SQL to be slow when it has an FE-resident function to evaluation in the query.

 

[jaryszek]

Thank you!

It's when Access can't pass the work back to the server that performance suffers and we have to find alternate methods.

Ok, so when i have a lot of queries with Access specific functions like IIF the problem can be to pass this query to BE yes? What methods are you using to do this?

Access query design editor is so intuitive and simple and creating queries is easy.
In other words transform Access Queries to MS SQL can be the issue?

---

Colin,

how to unhide deep hidden table? can you help with this, very interesting approach generally!

Best Wishes,
Jacek

 

[isladogs]

Hi Jacek
The purpose of 'deep hidden tables' is for security.
On principle I never explain how to unhide them or the security would become worthless.

 

[pbaldy]

Ok, so when i have a lot of queries with Access specific functions like IIF the problem can be to pass this query to BE yes? What methods are you using to do this?

There are many tools available including stored procedures, views, table valued functions, CTE's, etc. I can't say I have a rule as to when I'll use each, perhaps somebody else does. I've used all of those except CTE's, and for no better reason than I learned other tools first so I'm more comfortable with them.

There are some rules that drive which I'll choose. A view can't be parameterized, a stored procedure can't be called in the FROM clause of a query, etc.

I won't disagree that the Access query design grid is simple to use, but as you work with SQL more it becomes almost easier to read SQL than the design grid.

FYI, the IIf() function is valid in later versions of SQL Server (starting with 2012).

 

[jaryszek]

Thank you Paul,

stored procedures, views, table valued functions are called with pass through queries or vba yes ?

I won't disagree that the Access query design grid is simple to use, but as you work with SQL more it becomes almost easier to read SQL than the design grid.

hmm so how can i move Access sqls to SQL server BE? Did you try somethinf like that?

Best,
Jacek

 

[pbaldy]

A view can be linked to an Access front end just like a table, or used in the other methods. The others are probably predominately used from those methods. A table valued function could be used within a stored procedure or anyplace else you might use a table.

I am definitely not an authority, I'm somebody who's beat their head against problems until they found something that worked. It may not always have been the best solution. The Steve Bishop videos look like they're pretty good.

 

[pbaldy]

By the way, I recall Leigh's FAQ here being very informative:

http://www.utteraccess.com/forum/t1732935.html

 

[jaryszek]

thank you pbaldy!

Awesome info, i will review this faq.

Best Wishes,
Jacek

 

[pbaldy]

No problemo! The info was worth what you paid for it.

 

[isladogs]

I hadn't read Leigh's article for several years and it provided useful information to refresh my memory about certain aspects in SS.

Some of the info is out of date e.g. The Express edition of SS now supports up to 10GB (previously 4GB) but it remains an excellent resource for intermittent users as well as beginners

Thanks for the link Paul.

 

[pbaldy]

I should have reread it to make sure it was still relevant, but I remembered it being a big help. It is older though. Like me.