Results 1 to 8 of 8
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    137

    Primary keys vs indexes

    Can someone help me better understand how the data is pulled from the backend to the front end over a network?

    My Backend Dbase has a table with approx 20k records in it
    If only one user is on the network and they're front end application is pulling data off the BackEnd , things go rather quickly.
    As soon as another user is on the network the speed greatly slows down.

    I've ruled out any networking issues (i'm running a gigabit switch and have pretty beefy hardware/speed etc)

    I found out last week the value of the Primary key setting in each table. Just to make sure i have it correctly, the primary key is used for the field you are most likely going to be searching by - correct?
    There are also Indexing options in the table which seem to allow me to choose which fields would be typically searched and "index" them with an ascending or decending order option. From what i understand this will also greatly speed up the searches bused by the FrontEnd via queries etc.



    So getting back to my question, when the Frontend asks the backend for a record is the front end pulling all the records in the specific table or tables and finding the specific record once the data is on the Frontend computer? or is the Frontend asking for a record and the backend is only looking at the primary key field and then the indexed fields only - finding the correct record than sending only that single record back to the front end?

    Does it slow things down if my table has 1 primary key field and 3 or 4 indexed fields?

    Lastly, when the Frontend is running the query i can see the lower right hand of the screen showing the progress of the query but it doesn't show which specific query is bring run. Is there a way to find out the query names as a couple of these queries are taking a long time to process (especially with more than 1 user on the network)

    thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,335
    Primary key is there to prevent duplicates...like tStates table. key the ST field
    NY,FL,etc....so you can't enter a duplicate.

    INdex a field to link to another table to lookup faster.
    say the tEmployee table,..index the ST field to join to tStates.

    (but you wouldn't really do it for states.)

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,387
    I found out last week the value of the Primary key setting in each table. Just to make sure i have it correctly, the primary key is used for the field you are most likely going to be searching by - correct?
    No - it is used to uniquely identify a record and is usually an autonumber. Other than identifying the record it has no meaning and is not normally visible to the user.
    There are also Indexing options in the table which seem to allow me to choose which fields would be typically searched and "index" them with an ascending or decending order option. From what i understand this will also greatly speed up the searches bused by the FrontEnd via queries etc.
    you just index the field, ascending and descending is a matter for presentation. All fields which you regularly search/join/sort on should be indexed - the exception being fields with a very limited range of values such as booleans.

    So getting back to my question, when the Frontend asks the backend for a record is the front end pulling all the records in the specific table or tables and finding the specific record once the data is on the Frontend computer? or is the Frontend asking for a record and the backend is only looking at the primary key field and then the indexed fields only - finding the correct record than sending only that single record back to the front end?
    It depends on how it asks - if the front end is a form and the form recoursource is a table or query name, all records will be pulled to be subsequently filtered. It will only use the PK field if specifically used i.e. WHERE PK=123. A criteria WHERE lastname='smith' does not use the PK field.

    example

    you have a form with a recordsource of 'TableA' - all records will be pulled when the form is opened
    you have a form with a recordsource of 'SELECT * FROM TableA' - all records will be pulled when the form is opened
    you have a form with either of the above, but open with docmd.openform and set the WHERE parameter - all records will be pulled when the form is opened, but the records selected will be displayed first - this is actually a filter, not a criteria

    To solve the problem,

    1. give the form an empty recordset - i.e. your recordsource is something like 'select * from TableA WHERE False'
    2. now set a criteria - if using docmd.openform, pass what you had in the WHERE parameter in the OpenArgs parameter
    3. In the form load event put some code along the lines of

    Note- make sure you do not sort a form until you have retrieved the data

    me.recordsource=replace(me.recordsource, "False",me.openargs)

    Alternatively perhaps your form has a combo to select a record and code along the lines of

    me.filter="ID=" & cboSearch
    me.filteron=true

    just replace the code with

    me.recordsource=replace(me.recordsource, "False",me.openargs)

    clearly if you want to do multiple searches then the code can't replace False the second time because you've already replace it the first time. So instead your code might be something like

    me.recordsource=replace(me.recordsource, mid(me.recordsource,instr(me.recordsource,"WHERE") +6),"ID=" & cboSearch)

    Other factors such as using lookups in tables will slow thing down because when you retrieve the primary table, all the lookup tables are retrieved as well - the same goes for subdatasheets.

    And on your forms you may have combo's, listboxes and subforms. Usually the first two will be relatively short lists but I've seen combos where user is required to select a customer from a table with 25k records. Subforms also load the entire dataset and subsquently filter using the linkMaster/Child properties. Instead use code on the main form current event similar to the above to modify the subform recordsource.


    Does it slow things down if my table has 1 primary key field and 3 or 4 indexed fields?
    No, indexes speed retrieval. If you have many indexes then inserting/updating can take a bit longer because the db has to update the indexes as well as the data.

    As far as query performance is concerned - indexing is the most important. This is followed by not using the initial * in a Like criteria (prevents the index being used), use of domain functions, use of subqueries. Also look at what is being processed - a 'view' i.e. a 'master' query that has the results you want to be subsequently filtered elsewhere in your application will be a lot slower than if you can apply the criteria first

    see this link about indexing https://www.access-programmers.co.uk...rmance.291268/

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,939
    See Primary Key

    You can put some Debug.Print statements into your code (eg. before and after a query execution) to show how much time the query took to run.

    Here's a sample:

    Code:
    .....
    20       Debug.Print Now & "  -  " & "Starting to process tblTelemetryEvents "
              Dim intFile As Integer
              Dim DelSQL As String
    30       DelSQL = "DELETE From tblTelemetryEvents;"
    .....

    And some output from the Debug.Prints:
    Code:
    27-Jul-20 6:24:07 PM  -  Starting to process tblTelemetryEvents 
    27-Jul-20 6:24:07 PM  -  Deleted old contents of the tblTelemetryEvents 
    27-Jul-20 6:24:07 PM  -  Checking to ensure no contiguous State codes false/false|ture/true
     record # 1 has desc false  OK to review remaining records
    27-Jul-20 6:24:07 PM ** contiguous True found at record 17014 so Delete it
    27-Jul-20 6:24:07 PM  -  Creating the output file 'c:\users\jack\documents\BellInternetDrops...txt'
    27-Jul-20 6:24:07 PM  -  Outages processed: 388
    27-Jul-20 6:24:07 PM  -  Finished
    Good luck.
    Last edited by orange; 07-27-2020 at 05:43 PM. Reason: spelling

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,865
    Quote Originally Posted by fishhead View Post
    Just to make sure i have it correctly, the primary key is used for the field you are most likely going to be searching by - correct?
    No, the PK field is used mostly to link tables and is set to be unique values. The PK field in one table links to the FK field in a different table. (one to many relationship)
    I use an autonumber type field as the PK field in all of my tables.

    Maybe start by watch Indexing Fields in Microsoft Access Database Tables and Databases with Microsoft Access 10 –Field Indexing, Primary Keys, Index Creation

    Also see Microsoft Access Tables: Primary Key Tips and Techniques
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    Join Date
    Apr 2017
    Posts
    1,133
    Does every user have his/her own FE?

    Are users all in same LAN as BE?

  7. #7
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    137
    I'm sort of following what your saying here....

    Here's what im seeing at my end.

    On the Frontend i'm running a query asking for 4 fields of data and it is returning +27k records very fast.
    So that tells me all the records are being pulled from the backend into my front end very fast OR it means my front end is showing me the +27k records that are sitting on the back end.

    I then run a 2nd query based on the 1st query asking for a specific record only. The thinking is if the data is on my Frontend already and i'm only looking for 1 of the +27k records this should also happen quickly but it doesn't - this query will take approx 3 seconds to run. (which doesn't seem like a lot but this is only one part of a larger function that is taking over 10 seconds to run).

    If the 2nd query is sorting through the +27k records while they are still on the backend computer and only showing me the result on the front end i don't understand why it takes the 3 seconds to do this when pulling all the records is taking only 1 second. seems to me the "load on the system" is in the volume of records not the 1 specific record?

    Can you help me understand what's happening here?
    btw i've also indexed the table on the backend by the field that i'm searching in the FE query.

    thanks in advance.

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,387
    On the Frontend i'm running a query asking for 4 fields of data and it is returning +27k records very fast.
    are you sure it is fast? - all records have not be returned until the recordcount figure in the navigation bar at the bottom of the query has been populated. Access will show the first few records - typically a 'screenful' as soon as it can, but that does not mean the entire recordset has been returned.

    I then run a 2nd query based on the 1st query asking for a specific record only. The thinking is if the data is on my Frontend already and i'm only looking for 1 of the +27k records this should also happen quickly but it doesn't - this query will take approx 3 seconds to run.
    because access has to rerun the first query. The fact you have already run it is irrelevant. Think of it this way. You have previously taken a train from London to Edinburgh (your first query). Now you are catching a train from London to a station on the Edinburgh route (your second query). What impact does your earlier train journey have on your current journey?

    Your objective should be to retrieve as few records as possible - so your criteria should be applied as soon as possibel i.e. within your first query, so the second query does not need any criteria - and probably isn't even necessary.

    Another example - you have a bucket of different coloured balls and want to know how many green balls are in the bucket. Your first query takes each ball out and separates the balls into each colour group (your first query). Then you count the green balls (your second query). Wouldn't it be quicker to take each ball out and if it is green, count it?

    To refine this slightly, this example assumes no indexing - the balls are randomly located in the bucket. If the bucket was indexed, the balls would be in separate layers for each colour so you could just go to the green layer and count what is there.

    it might be an idea if you post the sql to your two queries - we may be able to suggest improvements

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Use of primary keys
    By Homegrownandy in forum Access
    Replies: 6
    Last Post: 06-29-2015, 01:17 AM
  2. Changing primary keys
    By ksammie01 in forum Database Design
    Replies: 4
    Last Post: 02-07-2013, 07:56 PM
  3. Primary Keys & Relationships
    By Njliven in forum Programming
    Replies: 4
    Last Post: 12-17-2012, 09:42 AM
  4. How to create two primary keys
    By Shabana123 in forum Database Design
    Replies: 1
    Last Post: 09-08-2012, 05:55 AM
  5. Multiple primary keys
    By elektrisk in forum Access
    Replies: 5
    Last Post: 02-11-2010, 04:39 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 - Senior Forums