Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
A hypothetical question... 2
- Thread starter wm2005
- Start date
hmm, generally I'd say on the backend because (and I'm not sure I'm 100% correct about these points so correct me if I'm wrong)
1) queries on the front end are executed by the client machine, by pulling the data across, then running the query, so is generally slower
2) queries on the back end are executed by the server and only the results are returned, thus less network traffic
3) generally the server is faster than the client pc, so queries run faster
however do remember that you cannot link queries like you can with tables, so if you want to link to queries in the back then you'll have to set them up in the front end as linked tables.
This isn't a huge issue during operation, but during development it's a major drag having to go back to the back end every time you want to do a new query, plus there's potentially a LOT of queries, and this could do a lot to mess up your table listings...
--------------------
Procrastinate Now!
1) queries on the front end are executed by the client machine, by pulling the data across, then running the query, so is generally slower
2) queries on the back end are executed by the server and only the results are returned, thus less network traffic
3) generally the server is faster than the client pc, so queries run faster
however do remember that you cannot link queries like you can with tables, so if you want to link to queries in the back then you'll have to set them up in the front end as linked tables.
This isn't a huge issue during operation, but during development it's a major drag having to go back to the back end every time you want to do a new query, plus there's potentially a LOT of queries, and this could do a lot to mess up your table listings...
--------------------
Procrastinate Now!
-
1
- #3
Sorry to have to disagree with the first response. But you should store everything that processes data in the front-end and store that front-end on the user's machine. For two reasons:-
ALL the processing in an Access application takes place on the user's machine. There is NO server process.
Therefore for (probably marginal) performance reasons you should remove the need for any user objects to be fetched from another machine.
Second,, maintaining the application is simpler with a split database,and this logic applies just as much for queries as it does for forms, reports and code.
ALL the processing in an Access application takes place on the user's machine. There is NO server process.
Therefore for (probably marginal) performance reasons you should remove the need for any user objects to be fetched from another machine.
Second,, maintaining the application is simpler with a split database,and this logic applies just as much for queries as it does for forms, reports and code.
-
1
- #4
Crowley,
I second what lupins46 said, and add that often queries can by dynamic, ie, many apps change the .sql property of the query based on search criteria, report criteria, etc. You don't want contention on this, it could cause disastrous results.
Bottom line general rule for Access is: Tables ONLY on the backend.
--Jim
I second what lupins46 said, and add that often queries can by dynamic, ie, many apps change the .sql property of the query based on search criteria, report criteria, etc. You don't want contention on this, it could cause disastrous results.
Bottom line general rule for Access is: Tables ONLY on the backend.
--Jim
- Thread starter
- #5
Firstly thanks for all your comments everyone - much appreciated.
Ok Im just playing devil's avocate here, but there may be merits to having the queries shared backend.
1. Security
By not storing queries (or tables) in the frontend, and encrypting all the other objects into an MDE there is no way of tracing down the backend database.
2. Efficiency
- Sharing the queries means sharing the efficiency benefits of a wider execution plan.
- The frontends never need be Compact/Repaired.
3. Maintainability
As all are using the same queries, they can easily be updated, in one place without the need for a rollout of the a new frontend. Although this could be done by copying all query objects on startup if an update is necessary.
I'd really like to find out more on this issue of contention. I assume were talking about record locking?
WM
Ok Im just playing devil's avocate here, but there may be merits to having the queries shared backend.
1. Security
By not storing queries (or tables) in the frontend, and encrypting all the other objects into an MDE there is no way of tracing down the backend database.
2. Efficiency
- Sharing the queries means sharing the efficiency benefits of a wider execution plan.
- The frontends never need be Compact/Repaired.
3. Maintainability
As all are using the same queries, they can easily be updated, in one place without the need for a rollout of the a new frontend. Although this could be done by copying all query objects on startup if an update is necessary.
I'd really like to find out more on this issue of contention. I assume were talking about record locking?
WM
they can easily be updated
ALL the users should log off from the DB for that ...
Another note:
You NEVER use maketable query nor temporary table.
You NEVER alter any QueryDef by code.
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
ALL the users should log off from the DB for that ...
Another note:
You NEVER use maketable query nor temporary table.
You NEVER alter any QueryDef by code.
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
WM,
In my context of this, I'm talking about contention of the Object--the querydef. Many apps do change querydefs by code, though obviously there are differing opinions on whether this is a good practice.
With an mde, ironically, you do not have invisibility on the tables source--you can easily trace the backend.
The execution plan would be part of the client (front-end), and typically all would be the same when coming from what you might call the 'master' front-end--the one you develop and distribute to each client/front-end.
--Jim
- Thread starter
- #8
Sorry for the delay in replying, I was away over the weekend.
I come to this from a web background and have used action and parameterised queries stored in the backend for some time, all accessed via ADO execute commands (and snapshot, read-only recordsets where necessary). Now Im new to access in a corporate production environment, but I dont see why if im using an unbound form it should be any less efficient, or are the issues referred to here just a problem with DAO?
Will
I come to this from a web background and have used action and parameterised queries stored in the backend for some time, all accessed via ADO execute commands (and snapshot, read-only recordsets where necessary). Now Im new to access in a corporate production environment, but I dont see why if im using an unbound form it should be any less efficient, or are the issues referred to here just a problem with DAO?
Will
Will,
In your web apps, using Access--was Access on the same box as IIS? If so, then it's really similar to it being a 'local' access app. In this setup IIS is running Access queries via it's local Access.Application Object, but if the 'backend' is right there, then the performance will be similar to if it the tables were on the front-end, because from a disk io standpoint, they are.
Now that you're putting the 'Application object', which is the front-end, on a different physical machine, that's when you'll see the performance issues.
--Jim
In your web apps, using Access--was Access on the same box as IIS? If so, then it's really similar to it being a 'local' access app. In this setup IIS is running Access queries via it's local Access.Application Object, but if the 'backend' is right there, then the performance will be similar to if it the tables were on the front-end, because from a disk io standpoint, they are.
Now that you're putting the 'Application object', which is the front-end, on a different physical machine, that's when you'll see the performance issues.
--Jim
- Thread starter
- #11
I see. Thats enlightening, thanks Jim.
Thanks Lupins. So I guess you could say that if using a shared access backend its best to connect to it via DAO rather than ADO, and if connecting to any other database (eg MSSQL or MySQL) then ADO connections would be better suited. You see I always thought that DAO was on the 'out', to be superceeded by the more efficient ADO.
Will
Thanks Lupins. So I guess you could say that if using a shared access backend its best to connect to it via DAO rather than ADO, and if connecting to any other database (eg MSSQL or MySQL) then ADO connections would be better suited. You see I always thought that DAO was on the 'out', to be superceeded by the more efficient ADO.
Will
Well, personally, I'd use linked tables and bound forms - which I regard as playing to Access's strengths.
If you're more comfortable with unbound forms then that's fine, but most Access developers would probably see that as increasing the development time. Using unbound forms (and ADO) throughout an application can be quite effective if you want to switch the database engine you're using quickly and there are some situations where unbound forms are very useful in handling non-standard data input requirements.
MS intended ADO to replace DAO, but I think more from a flexibilty of data sources viewpoint than a performance one. Performance on many Access apps is not a serious issue so it will not make any noticeable difference to the user which you use. Newer developers tend to be ADO-trained so that is what they use. Us old-timers are the only one who can do DAO
-).
Using saved Access queries is faster than editing via DAO or ADO recordsets.
If you're more comfortable with unbound forms then that's fine, but most Access developers would probably see that as increasing the development time. Using unbound forms (and ADO) throughout an application can be quite effective if you want to switch the database engine you're using quickly and there are some situations where unbound forms are very useful in handling non-standard data input requirements.
MS intended ADO to replace DAO, but I think more from a flexibilty of data sources viewpoint than a performance one. Performance on many Access apps is not a serious issue so it will not make any noticeable difference to the user which you use. Newer developers tend to be ADO-trained so that is what they use. Us old-timers are the only one who can do DAO
-).Using saved Access queries is faster than editing via DAO or ADO recordsets.
- Thread starter
- #13
- Status
Similar threads
