Performance Design: two backend databases?

[Max1979]

I maintain an Access application "3-front ends" and "1 networked shared backend". I need to add a new application with the following design options:

1) Create a new Front-end and add the new tables to the existing backend database

2) Create a new Front-end and create a new (second) backend database with the new tables.

The new application only needs a read reference of one table in the first backend database, no update/insert is required. It will need to write and update to the new tables.

The reason I am leaning towards option #2 is to prevent I/O contention and record locking. I worked on SQL Server/Oracle/DB2 and I am familiar with their record/page locking architecture. I am trying to find information on these:

A) How does Access resolve enforce integrity/locking in a multi-user environment
B) Option #1 (one backend) or Option #2 (two backends)?

Any info is appreciated.

Thank You

 

[Ranman256]

you cant add tables to the backend via the FE. you can only do it in the BE.

you can create a new db with the table inside. then when the design is complete, split the db into FE & BE.

 

[theDBguy]

Hi. Please don't quote me, because I am no expert on this, but I believe I/O contention is managed down to the table level. So, having either one or two backends won't really come into play, because the table with the contention would always be only in one of them. Just my 2 cents...

 

[isladogs]

@Max1979
Either option is possible. You didn't say whether these were Access or SQL BE files.
However do bear in mind you can only create relationships between tables in the same BE database.

you cant add tables to the backend via the FE. you can only do it in the BE.

Incorrect.
You can do this using code such as CREATE TABLE with a passthrough query.
Similarly you can alter or delete tables in an external database.
For example:

Sub SQLTestCreate()

'Used to create a table in SQL datafile
'If the table already exists it will first be deleted

On Error GoTo Err_SQLTestCreate

    'define table name
    strText2 = "_ABCDEFG_TEST"
    'strText2 = "tblGCSEStudentSummaryNEW"

    '================================
    'Check link details for SDABE datafile
    strSQL1 = "SELECT tblTableLinks.TableName, tblTableLinks.TableAlias, tblTableLinks.LinkActive, tblTableLinkTypes.LinkType, tblTableLinkTypes.LinkServer, tblTableLinkTypes.LinkDatabase, tblTableLinkTypes.LinkUsernamePassword, tblTableLinkTypes.LinkUsername, tblTableLinkTypes.LinkPassword" & _
                " FROM tblTableLinkTypes INNER JOIN tblTableLinks ON tblTableLinkTypes.TableLinkType = tblTableLinks.LinkType" & _
                " WHERE tblTableLinks.TableAlias='PupilData';"
        
    Set db = CurrentDb
    Set MyRset = db.OpenRecordset(strSQL1, dbOpenSnapshot)
    
    'Identify SQL link parameters for SDABE datafile
    strCurrentLink = "ODBC;DRIVER=SQL Server;SERVER=" & MyRset!LinkServer & ";Database=" & MyRset!LinkDatabase & ";UID=" & MyRset!LinkUsername & ";PWD=" & MyRset!LinkPassword
    'Debug.Print strCurrentLink

    '================================
    'Check if qryTempPassthrough already exists
    N = 0
    
    For Each qdfTemp In db.QueryDefs
        If qdfTemp.Name = "qryTempPassthrough" Then N = 1
    Next
    
    'If query exists . . .delete it!"
    If N = 1 Then
        db.QueryDefs.Delete "qryTempPassthrough"
    End If
    '================================
    
    Set qdfPassThrough = db.CreateQueryDef("qryTempPassthrough")
      
    'Setup query conditions based on datafile connection & type
    qdfPassThrough.Connect = strCurrentLink
        
    'delete table if it already exists
    strSQL1 = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_ABCDEFG_TEST]') AND type in (N'U'))" & _
        " DROP TABLE [dbo].[_ABCDEFG_TEST];"
        
    'Run the query..
    qdfPassThrough.SQL = strSQL1
    qdfPassThrough.ReturnsRecords = False
    qdfPassThrough.Execute
    
    'define SQL for pass through query
    strSQL2 = "CREATE TABLE [dbo].[_ABCDEFG_TEST](" & _
            " [MyText] [varchar](50) NULL," & _
            " [MyMemo] [text] NULL," & _
            " [MyByte] [bit] NULL," & _
            " [MyInteger] [int] NULL," & _
            " [MyDateTime] [datetime] NULL," & _
            " [MyYesNo] [bit] NULL," & _
            " [MyOleObject] [binary](1) NULL," & _
            " [MyBinary] [binary](50) NULL" & _
            " ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];"
      
  
    'Run the create table query . . .
    qdfPassThrough.SQL = strSQL2
    qdfPassThrough.ReturnsRecords = False
    qdfPassThrough.Execute
  
    'If strText1 = "" Then
        MsgBox "The SQL table " & strText2 & " has been successfully created.", vbInformation, "SQL table created"
    'Else
    '    MsgBox "The SQL table " & strText2 & " was deleted and then successfully re-created.", vbInformation, "SQL table re-created"
    'End If
  
'Delete the temp query & close the database
   db.QueryDefs.Delete "qryTempPassthrough"
   db.Close

Exit_SQLTestCreate:
    Exit Sub

Err_SQLTestCreate:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbNewLine & _
        "The SQL table " & strText2 & " could not be created.", vbCritical, "SQL table not created"
    Resume Exit_SQLTestCreate

End Sub

 

[The_Doc_Man]

With regard to split BE case and file locking interference, there are layers within layers here. Let's start with how LOCKING works.

Layer 1 - Windows File I/O - if you open two files, you take out two SEPARATE and non-overlapping file locks. Because they are different files and would have to be opened via different file handles, they would not share any internal support structures in common. Disk buffers would not overlap because they are allocated per file handle. There could be MINOR levels of usage contention (NOT locking) for the device if the files are on the same physical disk because neither hard disks nor solid-state disks are multi-threaded under Windows. The device driver linearizes operations for single devices because of the need to keep the I/O context "pure" for signaling purposes. Note that if the two files are on physically different disks, however, the I/O can truly overlap because separate device driver structures are associated with the separate hardware. I.e. each separate device has a separate I/O context.

Layer 2 - Network I/O - Access uses Server Message Block protocols to get files if they require network I/O. For Access, the SMB protocol doesn't go by file; it goes within the file to grab logical blocks within the file. In order to be able to do SMB transfers, you need the file to be opened in order to be able to "map" the file for the purposes of SMB data put/get. (As an aside, SMB isn't limited to files because printers can talk via SMB. User processes can talk to each other using SMB.)

The network-related resources that would become significant would be disk buffers representing mid-file "snapshots." But again, there can be no conflict involved for simultaneous access to the two proposed BE files because they have no disk blocks in common (being from two separate files). Even if SMB used a single buffer set for ALL connections (which it doesn't), the buffers STILL would not overlap, so ... no contention.

Layer 3 - Access Lock File - the contents of the lock file correspond to entries made regarding table locks within a specific BE file. If you have two BE files opened by the same FE, you will have THREE lock files. The FE file and two BE files. And the record-level locking cannot overlap between the two files because their locks are kept separately within the specific lock files.

So - there will be no locking contention between the two files if you went that way. Note, however, that if you go multi-user, two users touching the same file CAN interfere with each other at layers 2 and 3. Access file locks are generally open for shared read/write so represent at most a speed bump. But at layers 2 & 3, buffers are typically opened for exclusive write so in a multi-user environment, you can get blocked. Here is where stuff can get tricky. Access normally blocks by disk buffer, not by record. So for the default case, the interference comes when two users are creating records for the same BE table, because the odds are that both users tried to get to the same place in the BE file, namely the first free block in that file. But they can't both have it simultaneously. Note that this case will have NO BENEFIT from splitting the BE file since you won't split one table across two back-ends. That way lies madness.

You also asked about Relational Integrity in this multi-file context. Colin already noted that you cannot completely enforce relational integrity if you go the multi-file method. I'll amplify that statement.

If you look at the relationship table, it names the table and the field, but it doesn't include a way to name the FILE - i.e. the file containing the table. So if you have two tables in two different Access BE files, they can't see each other because MSysRelationships can't make the cross-file reference. No reference? No relational integrity. And Colin says that the same concept applies to SQL BE files. There, I have to take his word for it.

You asked for info on how relational integrity actually works.

The answer is that in order to have RI, you must establish a relationship. IF you want to establish a relationship you must have an index on the ONE side of the 1/M relationship. (Or BOTH sides of the 1/1 relationship.) So if you have an index, you can make the relationship. When you go to insert a new record in the many-side table, RI can be enforced because Access can look at the relationship to find the field and then look at that field's index in the 1-side table to verify that the value is present.

 

[CJ_London]

IF you want to establish a relationship you must have an index on the ONE side of the 1/M relationship.

I might be wrong, but thought it has to be a unique index?

 

[Max1979]

The_Doc_Man, Thank you for the detailed explanation it makes more sense. The key I was looking for is the Access Lock file, it maintains who and what table/block is currently locked. Other question is when is the lock issued and released. Is an implicit lock issued before the insert/update and released after? Does Access support Transaction (start/commit)?

 

[CJ_London]

The key I was looking for is the Access Lock file,

I might be out of my depth but so far as I am aware it does not exist as a separate file unless Doc is referring to the laccdb file created when a user opens the file either directly or as a BE to a front end application (which is or should be the production setup). But that only tells you who, not what, table/block is currently locked - you can open the file with notepad if you want to take a look.

Suggest open access, go to file>options>client settings and scroll to the bottom to see the options for locking - default is record level, but they are specific to the user, not a specific BE.

Yes, access does support transaction start/commit

 

[The_Doc_Man]

Yes, CJL, I was indeed referring to the .LACCDB file that is used to track certain info about what each user is doing who has a presence in the file. This is over and above any file or network locks. There are THREE distinct layers and the Access layer is the lowest level. I am not sure I can tell you what is in the .LACCDB file in detail because the last time I looked at one, it was, well, semi-incomprehensible. Username and computer name were visible. Other things were binary and frequently appeared as nonsense characters like one would find in an extended character-set situation. I admit it has been a while since I was in the mood to do reverse engineering.

 

[The_Doc_Man]

when is the lock issued and released

Depends on database and query settings. Optimistic locks are opened at the last possible moment and closed immediate after they are done. Pessimistic locks are opened earlier and held longer. It is possible to have something with "No Locks" and that is taken literally, but really only should be used for things used in read-only mode or in databases intended only for a single user.

CJ already pointed out that Access supports begin/commit operations, though you need to use DBEngine(0) to exercise those features. They are methods of the engine, not of a particular query.

 

[Pat Hartman]

I've never had an Access application with a Jet/ACE BE that actually needed to worry about locking. The application needs to be very update intense and of a type that people are always working around the same few records. It is simply best to go with the optimistic locking which is the default of no locks. If you really think you must have locks (you probably don't), use edited record so that the record is locked only if a user dirties it.

The logic of your application can be problematic if you have lots of DAO/ADO code working with multiple recordsets are one time. You can create a pathological lock (in any RDBMS, not just Access) by having procedureA open/update recordsetA and then open/update recordsetB while procedureB does open/update recordsetB and then open/update recordsetA. And even this has to happen in an instant in time to be a problem.

You can watch how Access keeps track of things by opening two versions of the app on your desktop. AppA, open FormA. Then AppB, open FormA and modify record1 but don't save it. Looking back at AppA as long as you are displaying record selectors, you will see a pencil nest to record1 which indicates that someone has dirtied it. See what happens if AppA also dirties record1 and tries to save it.