Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    Relationships

    I have just been introduced to the concept of creating relationships between my BE tables and am wondering the following:



    1) if i create the relationships in the database tools/relationships area will this speed up my applications performance

    2) i have been designing my forms based on queries and have created these relationships in the query instead of the "relationships" section above. Will I run into problems having the relationships established in both of these areas or do i have to go an remove them in the queries now?

    3) how much of a performance improvement can i expect by converting my BE dbases to SQL?

    Thanks in advance,

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if i create the relationships in the database tools/relationships area will this speed up my applications performance
    Not at the time of transactions - but used correctly you will have a better designed database which will almost certainly perform better
    i have been designing my forms based on queries and have created these relationships in the query instead of the "relationships" section above.
    relationships are relationships, in queries you use joins. They are different things that happen to use the same graphical notation. Joins will often be between the same fields as the relationship by its very nature (e.g. invoice header linked to invoice lines) - so access tries to be helpful and show the relationships when you create a query. And by the way, the rule is one form, one table - yes use a query, but only for criteria purposes, not joins to other tables
    how much of a performance improvement can i expect by converting my BE dbases to SQL?
    SQL is a language, not a database. If you mean SQL Server and by performance you mean speed probably not a lot and more likely slower. The JET/ACE engine is just as efficient as SQL Server. Main reasons for upsizing are data volumes (require over 2Gb), very large numbers of users (ACE/JET is limited to 255), improved data security and/or very complex data manipulations which would require extensive use of vba in Access. Consider which is faster - a 1 tonne pickup or a 20 tonne lorry? If the loads you are required to deliver are 1-2 tonnes - which has the better performance? Also be aware that to get the performance benefits of SQL Server almost certainly requires complete rewriting of queries to make use of TSQL functionality.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You may find it useful to read the following articles from my website
    1. Relationships and referential integrity - http://www.mendipdatasystems.co.uk/r...ps1/4594533224
    2. Speed Comparison tests - http://www.mendipdatasystems.co.uk/s...sts/4594424200
    There are many different tests here - test 6 includes a comparison of Access vs SQL Server
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    "relationships are relationships, in queries you use joins. They are different things that happen to use the same graphical notation. Joins will often be between the same fields as the relationship by its very nature (e.g. invoice header linked to invoice lines) - so access tries to be helpful and show the relationships when you create a query. And by the way, the rule is one form, one table - yes use a query, but only for criteria purposes, not joins to other tables"

    so there shouldn't be any problems having tables with relationships to other tables and queries based on those tables with "Joins" at the same time then?

    regarding 1 table for 1 form how would you build a form showing both receivables and payables in the same form (each subform being a continuous form within the main form) so the user can see the individual invoices (receivables) and bills (payables) for a particular customer? I built mine by matching outstanding AR and AP by the invoice # (which is also stated on each payable to show which invoice the particular payable is matched to. This works but isn't as fast as i'd like it to be (sluggish)

  5. #5
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    thanks Colin

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As my article explains, the main purpose of creating relationships is to set referential integrity
    If a relationship exists, Access will automatically apply a join in those tables in a query ...but you can modify that join or replace it completely if you wish.

    Many developers apply the 1 table per form 'rule' in all cases.
    However a subform is then used to show related records in the 'linked table'.

    Other developers including myself are more relaxed about using more than one table in a form
    The main thing you need to be aware of is that if you aren't careful, forms based on 3 or more tables may become read only.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so there shouldn't be any problems having tables with relationships to other tables and queries based on those tables with "Joins" at the same time then?
    No

    regarding 1 table for 1 form how would you build a form showing both receivables and payables in the same form (each subform being a continuous form within the main form) so the user can see the individual invoices (receivables) and bills (payables) for a particular customer? I built mine by matching outstanding AR and AP by the invoice # (which is also stated on each payable to show which invoice the particular payable is matched to. This works but isn't as fast as i'd like it to be (sluggish)
    As Colin says, mainform one table, subform another table. With regards receivables/payables, depends on your business model and preferences. Under the general heading of 'receivables/payables' I would normally expect them to be in the same table - either one positive and the other negative, or with a flag to indicate the type of transaction. Then for matching purposes, you have a simple group by query, anything unmatched will have a balance.

  8. #8
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    I've just adjusted my BE data to link all the tables (where possible) - through the Relationships screen and put the BE on the server. With only 1 user the application is very fast (which is great) . As soon as i have another user log in though it slows down dramatically. this has been a thorn in our sides for a long time now.....

    What i did notice is that I am logged in with my Microsoft Account (top right of screen) and the user is not logged in at all. ALso note the user is running on MSRuntime and not a functioning copy of MSAccess. Is it possible the network is slowing things down when two users are on it with one of them not being logged in to a MS account ?

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Does each user have their own copy of the FE on their own computer ...or are they sharing the same FE over a network?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    Each has his own FE
    still scratching my head about this speed issue ....

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    As soon as i have another user log in though it slows down dramatically
    this is usually due to a design that does not account for concurrent users and each user is using more BE or network resource than perhaps is required - or maybe your server needs tuning. You need to mimic how a website works - minimum data brought through, both in terms of fields and rows. Also proper indexing of fields will make a big difference to how fast a query works

  12. #12
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    I've just tried something that is possibly leading somewhere....

    the back end has the tables only
    i've created a query in the FE that only has 3 fields and is specifically pulling 1 record from the BE. The fields are 2 number fields and 1 short text field.

    When i open my form in the FE (that is based on this small/simple query) it opens at a good speed (this is the speed i'm trying to maintain)

    As soon as another user opens his FE (linked to the same backend) my form opens very slowly - application lags. The user hasn't done anything except open the front end. He hasn't opened any forms and run any queries yet. When the user closes the application (FE) i am now the only user accessing the simple 3 field query from the BE with my application and i still run slowly. When i close my FE and reopen it i get full speed again. It seems like there is something "clogging" the the network as soon as a 2nd user opens his FE - and stays "clogged" even when the 2nd user closes his FE completely.

    Does this point to anything obvious to anyone????

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are using this over a LAN, and not WAN, Cloud/One Drive or WiFi, yes? If LAN, Ajax is possibly correct.
    Maybe
    - too many records are being loaded into forms as opposed to queries that filter accordingly
    - there are too many calculations, subqueries involved at times
    - lack of indexing
    - long text (memo) fields being mined for data
    etc

    Some of these things could come in to play even if user doesn't open a form. The fact that you can only regain speed by closing Access suggests some of the above. Are the BE tables Access or something else, or are connections to external sources being made in code?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    we are connected to the BE via a LAN (not WAN)

    - too many records are being loaded into forms as opposed to queries that filter accordingly
    I think i can rule this out as the query i created for testing is pulling 3 fields of 1 specific record only out of the BE . The form in question only has these 3 fields (2 number and 1 short text) .

    - there are too many calculations, subqueries involved at times
    ruling this out too as the query has a search criteria for "Shan*" and the REF LINK field has a criteria of "29863" which is a specific record only in the BE table (which has 22000 records in it)

    - lack of indexing
    ruling this out as the two tables in the query are indexed and their relationship properties have a 1 to many connection with enforced referential integrity and cascade update related fields boxes checked.


    - long text (memo) fields being mined for data
    i'm not sure if there is any 'mining ' going on behind the scenes but i'm not asking the query to do anything with any memo fields.


    the BE tables are .accdb files (access) and i have considered moving over to mysql but at this stage i don't believe this is the case of my 'lagging' problem.

    doesn't anything else come to mind i can look at?




  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    then if you have no connections (e.g. ADOB) then I'm fresh out - except maybe see what happens if you reverse the order of opening that you described previously. It would be one of those tests that will tell you nothing if it's the same result, but if not...

    I did my own searching as well, and while not rare, the situations I read about involve variables that are likely irrelevant, such as other apps being involved. Sorry.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  2. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  3. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  4. one-to-one relationships
    By bonecone in forum Database Design
    Replies: 3
    Last Post: 01-25-2012, 02:34 PM
  5. Too Many Relationships
    By MikeT in forum Database Design
    Replies: 4
    Last Post: 08-25-2010, 07:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums