Question Database design

[thechazm]

I am always looking for meathods to make my programs run better so I would like to throw an Idea out there and see if anyone has comments.

I would like to capture my linked tables into memory (like a snapshot) and let the user look at the data and querry the data without having to use the network connection to the backend to querry everything.

That being said I have utilized this type of system using Workspaces and using commiting but I was thinking maybe using the same type of thinking and doing it for the tables that each module of the program needs at the start of the program. Then when their done with opening the forms and viewing/making changes, commiting the changes back to the backend when the program gets closed.

Maybe theres a better way to aliviate bandwidth issue's but this is the only possibility I can think of right now. Any comments? Or other thoughts?

Thanks,

TheChazm

 

[gemma-the-husky]

you can really only do this with small lookup (ie non-volatile) tables.

even then, if a query needs to use this data, it will use the table on the disc, rather than the array in memory.

this idea will not achieve a great deal of improvement, i suspect. if you have slow performance, then the issues are elsewhere, i feel.

 

[thechazm]

Well I have done some intial testing using public varibles and functions that seem to work very well. The speed gain I get from this is roughly 10 times faster and my network utilization went from 4% to .37%.

Here is my work so far:
Public DAOwsSystem As DAO.Workspace
Public DAOrstblPersonal As DAO.Recordset
Public dbSystem As Database

Public Function InitializeWorkspace()
Set dbSystem = CurrentDb
Set DAOwsSystem = DBEngine.Workspaces(0)
DAOwsSystem.BeginTrans
Set DAOrstblPersonal = dbSystem.OpenRecordset("tblPersonal")
End Function

Public Function CleanupWorkspace()
Set DAOrstblPersonal = Nothing
Set dbSystem = Nothing
Set DAOwsSystem = Nothing
End Function

Like I said before if anyone has any input please reply. Thanks gemma-the-husky for the responce.

 

[Access_guy49]

So what happens when 2 people add new records ?? I assume you have a Primary key.. what if they each use the same key?

 

[thechazm]

Well I didn't think of that. Thats definatly a good thought but as a responce I would attack that in a few different methods.

1. Generate a specific workspace for each of the modules. Then commit the changes as they happen. Commiting only applies the changes to the backend.

2. Have the form based on a DAO recordset in the workspace memory and use a simple docmd.runsql insert query to insert the new record in real time. Then the number would be locked and also it would display correctly even if multiple people would insert a new record at the same time.

I dunno for sure but I think testing is in order. Thanks Access_guy49 for the responce.

TheChazm

 

[Access_guy49]

Be sure to post an update, I'm curious to know the results of your testing.

 

[thechazm]

Sure no problem. I mean after all this form is all about finding out things right?

Anyway I am not to the point of testing this part of it yet but I have been able to elimiate almost all of my sql queires that toggles data by using memory resident tables to query the data. Its really exciting for me because the speed is a lot better. Here is just one of the functions I have done today to produce numbers for over 25 dashboard items.

Function CountDashboardMod(ByVal strFilter As String) As Long
RStblZIDZoneInspection.Filter = strConnect
CountDashboardMod = RStblZIDZoneInspection.RecordCount
RStblZIDZoneInspection.Filter = ""
End Function

And the calling code is:

Forms![frmzidbackgroundmain]![frmZIDDashboard]!txtLevel2Total.Value = CountDashboardMod("[Level]=2 AND [Cleared_Deficiency]=No")

Forms![frmzidbackgroundmain]![frmZIDDashboard]!txtSafetyTotal.Value = CountDashboardMod("[Safety]=Yes And [Cleared_Deficiency]=No")

Forms![frmzidbackgroundmain]![frmZIDDashboard]!txtOutSafetyRelated.Value = CountDashboardMod("[Safety]=Yes AND [Due_Date]<Now() AND [Cleared_Deficiency]=No")

Forms![frmzidbackgroundmain]![frmZIDDashboard]!txtDef30DaysOut.Value = CountDashboardMod("[Date_Found]<DateAdd(""" & "d" & """,-30,Now()) AND [Cleared_Deficiency]=No")

Forms![frmzidbackgroundmain]![frmZIDDashboard]!txtDef7to30DaysOut.Value = CountDashboardMod("[Date_Found]<DateAdd(""" & "d" & """,-7,Now()) And [Date_Found]>DateAdd(""" & "d" & """,-30,Now())) " & _
"AND [Cleared_Deficiency]=No")

It would take approx. 1-5 minutes to load this form and all its subforms because of the network bandwidth and the amount of personnel I have working in the database.

It now only takes roughly 5-10 seconds.

Thanks again and I'll keep you all posted,

TheChazm

 

[gemma-the-husky]

I don't think what you are oding is what you said you were going to do

I think what you have done is established a permanent connection to the back end - which is a necessary for good performance.

just try opening a (hidden) form (or a recordset) connected to a dummy table - and keep it open all through your application - that should give you a performance lift.

 

[thechazm]

Actually I have eliminated almost all traffic to the backend by doing this. I watch my network monitor while I am doing the testing and there is no activity to the backend at all unless I commit the workspace. So It does work like I intended.

Here before long I will post my findings along with the code. Its still going to be another few days or so because of everything I have to change. I know it may not be as much as most but I have 7509 lines of code just in this project so its taking a while to get everything ported over.

The code I had posted earlier was not complete but it is now at least that portion of it. My goal is that all of the database be workspace driven but it is taking some time.

 

[thechazm]

Just an update. Still working on this project. I have been pulled a few different directions so its taking a bit. Here is some of the code that sets up the workspace for a lot of queries and counters.

Public Type TWorkSpaceInit
    System As Boolean
    SchoolCoordinator As Boolean
    SchoolArchive As Boolean
    ZID As Boolean
    SystemData As Boolean
End Type
Public WorkspaceInit As TWorkSpaceInit
 
Public WSZID As DAO.Workspace
Public RStblZIDCategory As DAO.Recordset
Public RStblZIDDivision As DAO.Recordset
Public RStblZIDLevel As DAO.Recordset
Public RStblZIDLocation As DAO.Recordset
Public RStblZIDZoneInspection As DAO.Recordset
Public RSZIDQuery As DAO.Recordset
Public RSZIDReport As DAO.Recordset
Public dbZID As Database
 
 
Public Function InitializeZIDWorkspace()
If WorkspaceInit.ZID = True Then
    Exit Function
End If
Set dbZID = CurrentDb
'Set WSZID = DBEngine.Workspaces(0)
Set WSZID = DBEngine.CreateWorkspace("ZID", "Username", "Password")
DBEngine.Workspaces.Append WSZID
WSZID.BeginTrans
Set RStblZIDCategory = dbZID.OpenRecordset("tblZIDCategory")
Set RStblZIDDivision = dbZID.OpenRecordset("tblZIDDivision")
Set RStblZIDLevel = dbZID.OpenRecordset("tblZIDLevel")
Set RStblZIDLocation = dbZID.OpenRecordset("tblZIDLocation")
Set RStblZIDZoneInspection = dbZID.OpenRecordset("tblZIDZoneInspection")
Set RSZIDReport = dbZID.OpenRecordset("SELECT tblZIDZoneInspection.Location, tblZIDZoneInspection.Deficiency, tblZIDZoneInspection.Category, tblZIDZoneInspection.Level, " & _
    "tblZIDZoneInspection.Date_Found, tblZIDZoneInspection.Corrective_Action_Short, tblZIDZoneInspection.Corrective_Action_Long, tblZIDZoneInspection.Due_Date, " & _
    "tblZIDZoneInspection.Entered_Deficiency, tblZIDLocation.BLDG, tblZIDLocation.RM, tblZIDLocation.Shop, tblZIDZoneInspection.Safety, tblPersonal.FullName, tblZIDLocation.Owner, " & _
    "tblZIDLocation.[Secondary Owner], tblPersonal_1.FullName, tblPersonal.Rate, tblPersonal_1.Rate, tblPersonal_2.FullName, tblPersonal_2.Rate, tblZIDZoneInspection.Division " & _
    "FROM (((((tblZIDZoneInspection INNER JOIN tblZIDLocation ON tblZIDZoneInspection.Location = tblZIDLocation.[Location Number]) INNER JOIN (tblPersonal INNER JOIN " & _
    "tblUserInterface ON tblPersonal.Autonumber = tblUserInterface.[Personnel Number]) ON tblZIDLocation.Owner = tblUserInterface.Username) INNER JOIN tblUserInterface " & _
    "AS tblUserInterface_1 ON tblZIDLocation.[Secondary Owner] = tblUserInterface_1.Username) INNER JOIN tblPersonal AS tblPersonal_1 ON tblUserInterface_1.[Personnel Number] " & _
    "= tblPersonal_1.AutoNumber) INNER JOIN tblUserInterface AS tblUserInterface_2 ON tblZIDZoneInspection.Entered_Deficiency = tblUserInterface_2.Username) INNER JOIN " & _
    "tblPersonal AS tblPersonal_2 ON tblUserInterface_2.[Personnel Number] = tblPersonal_2.AutoNumber ORDER BY tblZIDZoneInspection.Location;")
WorkspaceInit.ZID = True
End Function
'------------------------------------------------------------------Cleanup ZID Workspace-------------------------------------------------------------
Public Function CleanupZIDWorkspace()
On Error Resume Next
Set dbZID = Nothing
WSZID.Close
Set WSZID = Nothing
Set RStblZIDCategory = Nothing
Set RStblZIDDivision = Nothing
Set RStblZIDLevel = Nothing
Set RStblZIDLocation = Nothing
Set RStblZIDZoneInspection = Nothing
Set RSZIDQuery = Nothing
WorkspaceInit.ZID = False
End Function

 

[thechazm]

Finally was able to work on this some more but I just found a disturbing fact with Access 2007 and above. It seems the DAO Workspace's don't work the same way as back in 2003.

In 2003 I could capture the tables into RAM and run the queires and requests on those. Then only commiting the changes to the actual tables. This saved tons of time and bandwidth issue's.

In 2007 Even though you can create a workspace it does not put it in RAM it more or less just adds the references to the tables then when you query the information it still hits the tables on the backend instead of in memory... This really sucks.

Anyone know if they have anything equivilant to this where the tables can be captured/snapshot into memory that would work like the old workspaces? If not I think I am done with access and will just develop in VS because the functionality is even better there.

Thanks,

TheChazm