|
|
From a performance standpoint, does it make any sense to "delete"
infrequently used attached tables and only reattach them using code if
they are needed for a specific form or function?
My commercial application is quite large and has over 45 attached
tables and it takes sometime to initially open on some of my client's
systems running over a LAN. The application is split properly between
a FE and BE, we maintain a persistent recordset connection on open,
and there are no recordsets loaded with the opening screen. One of our
developers thought we might be able to increase open speeds by only
attaching tables we needed so we detach the seldom used ones on
application close. We would like to know if this is worth doing.
Should we be detaching and reattaching on close and open respectively
or is this a bad idea or a waste of time?
Any and all help is appreciated.
TC
|
|
What has your testing shown so far? The specifics of your situation may
result in performance differences when compared to other folks' setups...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"John Moore" <mass97[ at ]attglobal.net.invalid> wrote in message
news:er5tj4tjen8fl1df2ntuj9qb4if4at2nbe[ at ]4ax.com...
[Quoted Text] > From a performance standpoint, does it make any sense to "delete"
> infrequently used attached tables and only reattach them using code if
> they are needed for a specific form or function?
>
> My commercial application is quite large and has over 45 attached
> tables and it takes sometime to initially open on some of my client's
> systems running over a LAN. The application is split properly between
> a FE and BE, we maintain a persistent recordset connection on open,
> and there are no recordsets loaded with the opening screen. One of our
> developers thought we might be able to increase open speeds by only
> attaching tables we needed so we detach the seldom used ones on
> application close. We would like to know if this is worth doing.
>
> Should we be detaching and reattaching on close and open respectively
> or is this a bad idea or a waste of time?
>
> Any and all help is appreciated.
>
> TC
>
|
|
I would say that attaching and detaching tables is generally a waste of effort.
You might make sure that the tables in the backend do not have subdatasheets.
You can clear the subdatasheets by running this code in backend.
'Source: MS Knowledge Base #275085
'http://support.microsoft.com/Default.aspx?id=275085
Sub TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer
On Error GoTo tagError
Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0
For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intCount = intCount + 1
End If
End If
tagFromErrorHandling:
Next i
MyDB.Close
If intCount > 0 Then
MsgBox "The " & propName & " value for " & intCount & _
" non-system tables has been updated to " & propVal & "."
End If
Exit Sub
tagError:
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
intCount = intCount + 1
Resume tagFromErrorHandling
Else
MsgBox Err.Description & vbCrLf & vbCrLf & _
" in TurnOffSubDataSheets routine."
End If
End Sub
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John Moore wrote:
[Quoted Text] > From a performance standpoint, does it make any sense to "delete"
> infrequently used attached tables and only reattach them using code if
> they are needed for a specific form or function?
>
> My commercial application is quite large and has over 45 attached
> tables and it takes sometime to initially open on some of my client's
> systems running over a LAN. The application is split properly between
> a FE and BE, we maintain a persistent recordset connection on open,
> and there are no recordsets loaded with the opening screen. One of our
> developers thought we might be able to increase open speeds by only
> attaching tables we needed so we detach the seldom used ones on
> application close. We would like to know if this is worth doing.
>
> Should we be detaching and reattaching on close and open respectively
> or is this a bad idea or a waste of time?
>
> Any and all help is appreciated.
>
> TC
>
|
|
John Moore <mass97[ at ]attglobal.net.invalid> wrote:
[Quoted Text] >From a performance standpoint, does it make any sense to "delete"
>infrequently used attached tables and only reattach them using code if
>they are needed for a specific form or function?
Not at all. On startup Access does not scan through the linked
tables. I know this because I've had tables linked to no longer
existent BEs and Access doesn't tell you until code or a form tries to
open a table.
>My commercial application is quite large and has over 45 attached
>tables
That's medium sized.
>and it takes sometime to initially open on some of my client's
>systems running over a LAN.
All LANs or just a few? Are these in fact WANs where the server is in
another building?
Have you done a decompile recently?
You've visited Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm ?
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
It is not necessary to reconnect them if they are already linked into the
front end .mdb. Typically its not the number of tables that would be causing
it to take a while, its the multi-user environment. For example, if you were
reconnecting the tables and nobody else was using the app, the tables will
relink relatively quickly. If another user has already started the app and
then you attempt to connect backend .mdb tables, it will take a while to do
this. Technically I do not know why this is, I just know it to be so. So
just skip it, tables are already attached anyway and all is good.
RJ
Database Whiz Consulting
www.databasewhiz.com
"Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> wrote in message
news:krltj4dg4kost4abc04o34f4ud35tk0i5l[ at ]4ax.com...
[Quoted Text] > John Moore <mass97[ at ]attglobal.net.invalid> wrote: > >>From a performance standpoint, does it make any sense to "delete" >>infrequently used attached tables and only reattach them using code if >>they are needed for a specific form or function? > > Not at all. On startup Access does not scan through the linked > tables. I know this because I've had tables linked to no longer > existent BEs and Access doesn't tell you until code or a form tries to > open a table. > >>My commercial application is quite large and has over 45 attached >>tables > > That's medium sized. > >>and it takes sometime to initially open on some of my client's >>systems running over a LAN. > > All LANs or just a few? Are these in fact WANs where the server is in > another building? > > Have you done a decompile recently? > > You've visited Access Performance FAQ page at > http://www.granite.ab.ca/access/performancefaq.htm ? > > Tony > -- > Tony Toews, Microsoft Access MVP > Please respond only in the newsgroups so that others can > read the entire thread of messages. > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
"Ray Jefferson" <ray[ at ]databasewhiz.com> wrote:
[Quoted Text] >If another user has already started the app and
>then you attempt to connect backend .mdb tables, it will take a while to do
>this. Technically I do not know why this is, I just know it to be so. So
>just skip it, tables are already attached anyway and all is good.
The solution to the performance problem when relinking tables and
another use is already in the backend is to
1) open a database variable against the backend MDB/ACCDB
2) once you've relinked the first table open a recordset against that
table.
Ensure you close the variable when finished.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
"Ray Jefferson" <ray[ at ]databasewhiz.com> wrote in message
news:%2308WWr7YJHA.1964[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > If another user has already started the app and then you attempt to
> connect backend .mdb tables, it will take a while to do this.
Eh? Are you assuming that users share a FE file?
Keith.
|
|
|
[Quoted Text] > this. Technically I do not know why this is, I just know it to be so.
It is because Windows caches the files and does all your network
operations locally.
That can't work when you have more than one user, so Windows
reverts to leaving the files on the server sending all network
operations across the network.
The second user is particularly slow, because Windows has
to recover the files from the first user, and put them back
on the server again.
Using a persistent connection is faster, and also prevents Windows from
switching between single-user mode and shared
mode.
(david)
"Ray Jefferson" <ray[ at ]databasewhiz.com> wrote in message
news:%2308WWr7YJHA.1964[ at ]TK2MSFTNGP02.phx.gbl...
> It is not necessary to reconnect them if they are already linked into the
> front end .mdb. Typically its not the number of tables that would be
> causing it to take a while, its the multi-user environment. For example,
> if you were reconnecting the tables and nobody else was using the app, the
> tables will relink relatively quickly. If another user has already started
> the app and then you attempt to connect backend .mdb tables, it will take
> a while to do this. Technically I do not know why this is, I just know it
> to be so. So just skip it, tables are already attached anyway and all is
> good.
>
> RJ
> Database Whiz Consulting
> www.databasewhiz.com
>
>
> "Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> wrote in message
> news:krltj4dg4kost4abc04o34f4ud35tk0i5l[ at ]4ax.com...
>> John Moore <mass97[ at ]attglobal.net.invalid> wrote:
>>
>>>From a performance standpoint, does it make any sense to "delete"
>>>infrequently used attached tables and only reattach them using code if
>>>they are needed for a specific form or function?
>>
>> Not at all. On startup Access does not scan through the linked
>> tables. I know this because I've had tables linked to no longer
>> existent BEs and Access doesn't tell you until code or a form tries to
>> open a table.
>>
>>>My commercial application is quite large and has over 45 attached
>>>tables
>>
>> That's medium sized.
>>
>>>and it takes sometime to initially open on some of my client's
>>>systems running over a LAN.
>>
>> All LANs or just a few? Are these in fact WANs where the server is in
>> another building?
>>
>> Have you done a decompile recently?
>>
>> You've visited Access Performance FAQ page at
>> http://www.granite.ab.ca/access/performancefaq.htm ?
>>
>> Tony
>> --
>> Tony Toews, Microsoft Access MVP
>> Please respond only in the newsgroups so that others can
>> read the entire thread of messages.
>> Microsoft Access Links, Hints, Tips & Accounting Systems at
>> http://www.granite.ab.ca/accsmstr.htm
>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
>
>
|
|
|