Thanks Sal,

Your time is very much appreciated :D

-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

Here's another potential performance enhancement to add to the list. I can't remeber if it has been mentioned yet. Apparently turning off the subdatasheet option on all your backend non-system tables can have a pretty dramatic effect on performance. I've been following a performance thread on the database advisors form, an this showed up today:

Turning of the subdatasheets and keeping the connection open shows the following figure.

10MB network (both tables contain about 15.000 records)
     Reading a customertable was 10 seconds now 3 seconds
     Reading a order table was 7 seconds now 2 seconds

100MB network (both tables contain about 15.000 records)
     Reading a customertable was 3 seconds now 1 seconds
     Reading a order table was 2 seconds now 1 seconds

Kinda' interesting. Here is a function to turn all your subdatasheets off:

Function TurnOffSubDataSheets()
  Dim MyDB As DAO.Database
  Dim MyProperty As DAO.Property

  Dim propName As String
  Dim propType As Integer
  Dim propVal As String

  Dim strS As String
  Dim i, intChangedTables

  Set MyDB = CurrentDb

  propName = "SubDataSheetName"
  propType = 10
  propVal = "[NONE]"

  On Error Resume Next

  For i = 0 To MyDB.TableDefs.Count - 1

      If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then

          If MyDB.TableDefs(i).Properties(propName).Value <> propVal Then
             MyDB.TableDefs(i).Properties(propName).Value = propVal
             intChangedTables = intChangedTables + 1
          End If

          If Err.Number = 3270 Then
             Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
             MyProperty.Type = propType
             MyProperty.Value = propVal
             MyDB.TableDefs(i).Properties.Append MyProperty
          Else
             If Err.Number <> 0 Then
                 MsgBox "Error: " & Err.Number & " on Table " _
                 & MyDB.TableDefs(i).Name & "."
                 MyDB.Close
                 Exit Function
             End If
          End If

      End If
  Next i

MsgBox "The " & propName & _
" value for all non-system tables has been updated to " & propVal & "."

MyDB.Close

End Function

Also mentioned on this thread is the idea of forcing the link to your backend to remain open by creating a hidden form that is linked to a backend table and remains open for the life of the session (must be a bound form).

Regards,

Bob

Wow Bob,

It seeems that my reply to you always starts with Wow. Not that I am knowledgable to use this tidbit yet... but is this for SQL server,Access, or both?

A second question, as a non Ubermensch it may be pointless but where do you subscribe to this newsgroup?

I'm not sure what the sub datasheets property is but caqn it be set via a regular setting? Or do you have to run the code? Once you have run it must you maintain a live connection, run the code every time you use the DB or only once?

Thanks & Regards,
-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

Hi Roni,

Subdatasheets are a feature that showed up in A2K? A2K2? One or the other. When you establish a 1:M relationship between two tables, and then open the parent table, you'll see a little drill-down checkbox thingy that, when clicked, opens a ("sub")datasheet that displays the related child records.

The use of subdatsheets in your apps, combined with the use of look-up fields in tables, is an excellent strategy for wrecking a perfectly good backend.

I don't know if there is a GUI way to turn subdatasheets off. May be, haven't looked into it. The module is real easy to run though. Just open a new module in your back end database, set a reference to the DAO 3.x library (unles you feel like re-writing the module in ADO, which would be pointless), type TurnOffSubDataSheets in the immediate window, and hit enter. The code will update the SubDataSheetName value for all non-system tables to [None], and your parent tables will (thankfully) loose all of their subdatasheet functionality. The subdatasheet functionality remains turned off permanently. I don't even care to think how you might turn it back on again. Sql Server doesn't provide such annoyances.

The open connection issue, while a valid performance consideration, is another matter. I touched on it a bit in the "Converting DAO to ADO Code" thread in this forum.

Database advisors is at: http://www.databaseadvisors.com

Regards,

Bob

Bob,
I guess that this subdata sheets are quering the entire child table to gather the child Id or other info to get that subdatasheet ready.
That is a big difference in time from 3 to 1 seconds. Imagine if the re where one million records.

I will keep that funtion handy for future reference.

By the way, I have seen the subdata sheet working with an ADP file before. I will try and see if this also turns that one off (it should).



Sal

Holy Crap!

I hope its ok to post that, but I didn't want to start again with wow. I have 3 people using the DB concurrently and it runs almost as fast as it ran on my PC. That is slick Bob. When I grow up I want to be just like you!

Between all the advice given I have learnt a ton, and our DB is really starting to roll! I hope other readers get as much benefit from this thread as I have and I really appreciate all of your continued advice.

Thanks Again
-Roni

Roni Estein
[email protected]
https://www.e-drugsCanada.com

Hi Sal,

Yup. Placing Lookup fields in tables has the same effect. The Lookup Wizard ceates relationships and new indexes on your tables, even if such indexes already exist, wasting space, using up your 32-index limit, and making updating less efficient.

Both these features essentially amount to embedding queries in tables, which not only gets the whole point of relational design bass akwards, but severely degrades performance (thanks for the emphatic feedback Roni). They're two of the most muddle-headed, feature un-enhancements ever to come out of Redmond (kinda' on a par with Clippy).

Regards,

Bob

NEVER NEVER NEVER create lookup fields in a table, that is another one of the ten commandments.
If you ever create one of this inside an MS Access.mdb file, your table will not export to SQL Server. The only way to export it is to export it to Excell or to a tab delimited file.

Whose Idea was Clippy anyway?


Sal

Hang on a minute here ! Is not being able to export data to SQL server the only problem with lookup fields in tables ?

Vladimir

No. What is the point in creating a lookup field. Just create another table and relate it with a foreign key. That will give you a more useful database and a front end that your users can use even after you upgrade to SQL Server , Oracle, My SQL.
What is the purpose of the lookup field in the first place?
Another table is always best, plus you can add security to that table.

Sal