none
Different Db for performance improvement to Azure RRS feed

  • Question

  • Hi MSDN, 

    In the continuing upsizing from Access BE / Access FE to 

    Access FE / Azure BE.

    A number of improvements in speed have taken place.  Now for forms with many combo boxes.

    I read an article, 

    Keep Static Data Local
    Keep static data, such as lookup tables, on the local machine. Update the local tables as necessary
    from the server. For example, a lookup table containing the two-letter abbreviations for American
    states is not likely to change anytime soon. When such a table is used in a data entry application, it
    is a performance bottleneck to retrieve that data from the server every time it is needed. Instead,
    copy that table to your application's local database.

    http://www.fmsinc.com/microsoftaccess/performance.html

    Hence I have have kept transactional moving data in Azure, and the master data, static data, to the local SQL Server back end

    But when I ran the code, because the 2 sources of data, this code will not work as current syntax

    [code]

        Set db = CurrentDb()
        
         strSQL1 = "SELECT Calls.[Job Number], Calls.ID FROM Calls WHERE (((Calls.[Job Number]) = " & """" & Job_Number_Label & """" & "))"
        
        'Converted to SQL Server Backend
        'Set rs1 = db.OpenRecordset(strSQL1)
        Set rs1 = db.OpenRecordset(strSQL1, dbOpenSnapshot)
        
        With rs1
            ID_Label = ![ID]
        End With
        

    [/code]

    Hence, is the only way to get the data first from the first data source.

    The first is

    ODBC;Description=FMS on Azure using SQL Server 2017;DRIVER=ODBC Driver 13 for SQL Server;SERVER=abaplumbing..net;Trusted_Connection=No;APP=Microsoft® Windows® Operating System;DATABASE=ABAPlumbinge;Encrypt=Yes;;TABLE=dbo

    while the local sql server is 

    ODBC;DRIVER=SQL Server;SERVER=ABAPLUMBINGPC\SQLEXPRESS;Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=ABA PLUMBING Call Tracker V7;TABLE=dbo.Discount

    Should I simply employ a technique such as in the following link I chanced upon?

    Usually the common solution would be to:
    1. Get the first recordset...
    2. Doing some processing w ith it...
    3. Close it and get the second recor dset...
    4. Process it, close it, get the third, ... and so on...

    http://www.4guysfromrolla.com/webtech/083101-1.shtml

    Will I be taking the correct approach?

    Thank you kindly


    Thank you in Advance

    Monday, August 6, 2018 10:54 AM

Answers

All replies

  • For such data, local mean in the FE typically.  At the startup of your app you can such such tables and then use the local tables.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, August 6, 2018 11:51 AM
  • Thank you kindly Daniel, 

    Hence a couple of questions that came to mind, 

    1) If we have the tables on 2 environments, Azure, and Access FE (or SQL Server local copy for that matter), 

    where does the database schema reside.

    From a copy of the Access FE / Access BE, I migrated to SQL Server and then to the Azure environment.

    And so did the schema get migrated using the SSMA for Access.  It then removed all the database schema from the Access BE.

    I presume now the way was to only migrate the 'transactional' tables (not the master data) tables into Azure, and maintaining the database schema in the Access BE, but replacing the linked tables in the schema, which was previously local?

    Is it such that the schema should reside no in the Access FE across the tables and the the linked Azure tables / pass through links... 

    Or rather if I can pose the question to you, where should the database schema get created now?

    2) I have not yet dealt with temporary tables, as I have not done a lot of development in access, 

    I found this article to create a temp table?

    http://www.utteraccess.com/forum/index.php?showtopic=428374&hl=temp

    or would this technique be a better

    https://msdn.microsoft.com/en-us/vba/access-vba/articles/extract-data-from-a-record-in-a-dao-recordset

    Or if you can kindly point me to an example that I can use to manipulate the data between these tables.

    Thank you kindly

    Simon


    Thank you in Advance


    Tuesday, August 7, 2018 12:27 AM
  • Here is an update on time improvements.

    First Screen of Crosstab.  initially 20 seconds plus.

    By moving to pass through queries, plus all the other initiatives mentioned in the other forum question,  this has come down to about 3 -5 seconds.

    For the second screen with many comboboxes, 

    by removing as many of them that were not needed.  Thee set up was when you double clicked on the area of the crosstab above, this would pre-populate and set the combo boxes.

    These were changed to text boxes and DLookup to retrieve the single value.

    And moving all the 'master data' that did not change, or very rarely, to local FE

    And removing some redundant VBA Code that made RecordSet calls

    This dropped down from about 26 seconds to about 8 - 10 seconds.

    I am still working on the 100 point list to see where else the largest time savings can be made.

    Thank you kindly


    Thank you in Advance

    Tuesday, August 7, 2018 6:19 AM
  • Hi MVP's, 

    Here is the view of the Database Schema in the Access FE. The database Schema was dismantled as SSMA for Access ran, and moved all the tables/relationships into SQL Server.

    And to the left are those tables, some local, some on Azure / SQL Server (I have 4 different copies to compare performance)

    1 - Access FE / Access BE

    2- Access FE / SQL Server BE (Local Copy) - All tables relationships in SQL

    3- Hybrid: Access FE / SQL Server BE (Local Copy) - Master Data tables in the Access FE, Transactional Data in the SQL Server BE (Local Copy)  - relationships in SQL.  Do I now recreate those same relationships in the Access FE

    4- Hybrid: Access FE / Azure SQL Server BE (Cloud Copy) - Master Data tables in the Access FE, Transactional Data in the Azure SQL Server BE (Cloud Copy)  - relationships in Azure SQL.  Do I now recreate those same relationships in the Access FE between the 2 different databases

    Here is the original, and here is the SQL, and the current non linked schema at the Fronted.

    .... when I was taking a screen capture of the SQL Server migrated schema by SSMA for Access , I just noticed while creating this post that some of the relationships were not migrated.... something overlooked?

    eg. Calls to Employees, or Calls to Management, or Calls to Rate Method

    Hence my question about recreating the schema between the tables again in the Access FE now?  Will this help performance, let alone keep relationships working correctly?

    eg. what is the post migration experience and hybrid tables between systems, and resultant database schema required at an access front end?

    Answer: 

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_win10-mso_365hp/where-should-the-database-schema-be-recreated-for/05dd2832-c36f-4f0a-9fba-f4bc0c56279c

    Thank you.

    Simon


    Thank you in Advance


    Tuesday, August 7, 2018 8:46 PM
  • Update:

    I have recreated the relationship between the Linked tables at the FE as indicated by MVP.

    The relationship between linked tables and local tables does not allow referential integrity as 2 disparate systems.


    Thank you in Advance

    Sunday, August 12, 2018 12:27 AM