[AccessD] Major Performance Issue! + Code

Andy Lacey andy at minstersystems.co.uk
Thu Jan 22 09:03:15 CST 2004


I did look John. Did I miss it or have you only just done that? Either way I
really hope you don't mind. I did hesitate but as I'd got it from you I was
sure it'd be ok. As it happends Sander hasn't asked for it.

Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> John W. Colby
> Sent: 22 January 2004 14:51
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Major Performance Issue! + Code
> 
> 
> Andy,
> 
> I don't mind at all sharing my demos.  However I have also 
> placed a copy of this timer in a demo, on my site under Misc 
> Demos / C2DbTimerClass.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Andy Lacey
> Sent: Thursday, January 22, 2004 4:03 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Major Performance Issue! + Code
> 
> 
> Sander
> The first thing I suggest you do is put timing code in to 
> isolate where you have performance issue(s). If you don't 
> already have something I can send you a copy of JC's timer 
> class (I'm sure he won't mind). There's no point aiming 
> attention at one routine only to find that it represented 
> 0.01% of the overall time. So, first up, find the lines of 
> code which are doing the damage.
> 
> Andy Lacey
> http://www.minstersystems.co.uk
> 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Sad Der
> > Sent: 22 January 2004 06:26
> > To: Access Developers discussion and problem solving
> > Subject: RE: [AccessD] Major Performance Issue! + Code
> >
> >
> > Here is (part of) the code:
> > PS: I found the GetMaxDate() function, I think on wolfwares.com!
> >
> > '=============================================================
> > ============================
> > ' Function Name         :  FillStamMeetProces
> > ' Parameters            :  No parameters
> > ' Return value          :  (String) <MEANING>
> > ' Purpose               :  <PURPOSE>
> > ' Assumptions           :  ---
> > ' Uses                  :  ---
> > ' Created               :  2003-10-24 10:12, SaDe
> > ' Modifications         :
> > '=============================================================
> > ============================
> > Function FillStamMeetProces(strProces As String) As
> > String
> > '150      StamMeetData
> > '210      StamMeetData
> >    Dim strMeD As String   'Message_date
> >    Dim strSQL As String
> >    Dim strRecordcount As String
> >
> >    On Error GoTo FillStamMeetProces_Error
> >    Select Case strProces
> >    Case "SM"
> >       'Cleanup table
> >       DeleteRecords strProces
> >
> >       '----------------------------------------
> >       ' 150      Te verwerken ontvangen stamgegevens
> >       '----------------------------------------
> >
> > '---------------------------------------------------------
> >       'MaxDate()
> >
> > '---------------------------------------------------------
> >       strSQL = vbNullString
> >
> >       'SQL statement to fetch most recent date
> >       strSQL = "SELECT Max([Bericht 150 (Update Master
> > Data)].Message_Date) AS MaxOfMessage_Date " & _
> >                "FROM [Bericht 150 (Update Master
> > Data)] " & _
> >                "WHERE Connect_EAN =" & "'" &
> > gstrSelectedEan & "' "
> >
> >       strMeD = GetMaxDate(strSQL)
> >       If Len(strMeD) = 0 Then
> >          strMeD = "N/A"
> >       End If
> >
> > '---------------------------------------------------------
> >       'Aantal Records
> >
> > '---------------------------------------------------------
> >       strSQL = vbNullString
> >       'SQL statement to fetch most recent date
> >       strSQL = "SELECT COUNT(*) " & _
> >                "FROM [Bericht 150 (Update Master
> > Data)] " & _
> >                "WHERE Connect_EAN =" & "'" &
> > gstrSelectedEan & "' "
> >
> >       strRecordcount = GetRecordCount(strSQL)
> >
> >       'Check if a date was returned
> >       If strRecordcount = vbNullString Then
> >          strRecordcount = "0"
> >       End If
> >
> >       'Create new record
> >       AddRecords strProces, "150", strMeD,
> > strRecordcount
> >
> >       '----------------------------------------
> >       ' 210      Te verwerken ontvangen meetdata
> >       '----------------------------------------
> >
> > '---------------------------------------------------------
> >       'MaxDate()
> >
> > '---------------------------------------------------------
> >       strMeD = vbNullString
> >       strSQL = vbNullString
> >       'SQL statement to fetch most recent date
> >       strSQL = "SELECT Max(V2_210_MEDTD1.message_date)
> > AS MaxOfmessage_date " & _
> >                "FROM V2_210_MEDTD1 " & _
> >                "WHERE Connect_EAN =" & "'" &
> > gstrSelectedEan & "' "
> >
> >       strMeD = GetMaxDate(strSQL)
> >       If Len(strMeD) = 0 Then
> >          strMeD = "N/A"
> >       End If
> >
> > '---------------------------------------------------------
> >       'Aantal Records
> >
> > '---------------------------------------------------------
> >       strSQL = vbNullString
> >       'SQL statement to fetch most recent date
> >       strSQL = "SELECT COUNT(*) " & _
> >                "FROM V2_210_MEDTD1 " & _
> >                "WHERE Connect_EAN =" & "'" &
> > gstrSelectedEan & "' "
> >
> >       strRecordcount = GetRecordCount(strSQL)
> >
> >       'Check if a date was returned
> >       If strRecordcount = vbNullString Then
> >          strRecordcount = "0"
> >       End If
> >
> >       'Create new record
> >       AddRecords strProces, "210", strMeD,
> > strRecordcount
> >    Case Else
> >       MsgBox "UNKNOW PROCES!"
> >    End Select
> >
> > FillStamMeetProces_Exit:
> >    ' Collect your garbage here
> >    Exit Function
> > FillStamMeetProces_Error:
> >    ' Collect your garbage here
> >    Call
> > g_oGenErr.Throw("Switchdatabase.mMultiInterface",
> > "FillStamMeetProces")
> > End Function
> >
> > Function GetMaxDate(strSQL As String) As String
> > 'Dim strSQL As String
> >    Dim conn As ADODB.Connection
> >    Dim rstBericht As ADODB.Recordset
> >    Dim strMaxDate As String
> >
> >    Set conn = CurrentProject.Connection
> >    Set rstBericht = New ADODB.Recordset
> >
> >
> >    With rstBericht
> >       .ActiveConnection = conn
> >       .CursorType = adOpenKeyset
> >       .LockType = adLockReadOnly
> >       .Source = strSQL
> >       .Open
> >       If .RecordCount > 0 Then
> >          If IsNull(.Fields(0)) Then
> >             strMaxDate = vbNullString
> >          Else
> >             strMaxDate = .Fields(0)
> >          End If
> >       Else
> >          strMaxDate = vbNullString
> >       End If
> >    End With
> >
> >    'Return value
> >    GetMaxDate = strMaxDate
> >
> >    Set rstBericht = Nothing
> > End Function
> >
> >
> > '=============================================================
> > ============================
> > ' Function Name     : AddRecords
> > ' Parameters        : strProces As String
> > '                     strBericht As String
> > '                     strDatum As String
> > '                     strAantal As String
> > ' Return value      : (Boolean) <MEANING>
> > ' Purpose           : Inserts records into
> > tblMultiInterface. This table is used to display
> > detail
> > '                     information regarding the
> > switch, verhuis, stam & meetdata and contract
> > '                     processes
> > ' Assumptions       : ---
> > ' Uses              : ---
> > ' Created           : 2003-10-21 09:00, SaDe
> > ' Modifications     :
> > '=============================================================
> > ============================
> > Function AddRecords(strProces As String, strBericht As String, 
> > strDatum As String, strAantal As String) As Boolean
> >    Dim strSQL As String
> >
> >    On Error GoTo AddRecords_Error
> >    strSQL = "INSERT INTO tblMultiInterface VALUES ("""
> > & strProces & """,""" & strBericht & """,""" &
> > strDatum & """,""" & strAantal & """ )"
> >    DoCmd.RunSQL strSQL
> >
> > AddRecords_Exit:
> >    ' Collect your garbage here
> >    Exit Function
> > AddRecords_Error:
> >    ' Collect your garbage here
> >    Call g_oGenErr.Throw("Switchdatabase.Form_fMultiInterface",
> > "AddRecords")
> >
> > End Function
> >
> > --- Andy Lacey <andy at minstersystems.co.uk> wrote:
> > > Sander
> > > Rather than attaching the code try pasting it into
> > > the email. Shouldn't be a
> > > size issue then, unless, of course, there's miles of
> > > it! I don't know about
> > > anyone else but I certainly would get a clearer
> > > picture of what you're doing
> > > by seeing the code. Can't really get a handle on it
> > > yet.
> > >
> > > Andy Lacey
> > > http://www.minstersystems.co.uk
> > >
> > > > -----Original Message-----
> > > > From: accessd-bounces at databaseadvisors.com
> > > > [mailto:accessd-bounces at databaseadvisors.com] On
> > > Behalf Of Sad Der
> > > > Sent: 21 January 2004 08:40
> > > > To: Acces User Group
> > > > Subject: [AccessD] Major Performance Issue!
> > > >
> > > >
> > > > Hi group,
> > > >
> > > > I've got a MAJOR performance issue. I need to
> > > display
> > > > a lot of data. A performance test showed that it
> > > takes
> > > > between 120 - 160 seconds (2 minutes - 2.40
> > > minutes)
> > > > to display the data! I have no idea how to speed
> > > > things up dramaticly. I really could use some
> > > tips.
> > > >
> > > > Here's a description of the current environment:
> > > > Access 2000 Client (size: 20 Mb)
> > > > Access 2000 Back-end (size: 100 Mb)
> > > > approx 4 users
> > > > Novel network
> > > >
> > > > Functionality:
> > > > I need to display 4 listbox' with data.
> > > >
> > > > Every listbox has the same table as source.
> > > > Every listbox has a query as RowSource
> > > > Every listbox has this layout:
> > > >
> > > > Proces Type | Date | # Records
> > > >
> > > > Listbox 1 displays 4 records
> > > > Listbox 2 displays 2 records
> > > > Listbox 3 displays 6 records
> > > > Listbox 4 displays 3 records
> > > >
> > > > So the table (tblMultiInterface) has 15 records in
> > > > total.
> > > >
> > > > Table tblMultiInterface
> > > > Fields:
> > > >   Proces Type Text
> > > >   Date        Text (!)
> > > >   Records     Text (!) (zero records need to
> > > display
> > > > N/A)
> > > >
> > > > I added the code as an attachment but unfortunatly
> > > > this mail was blocked! If anybody would like to
> > > take a
> > > > look at the code I can send it to you directly.
> > > >
> > > > Comments/tips/etc would be GREATLY appriciated!
> > > >
> > > > TIA
> > > > Sad
> > > >
> > > >
> > > > __________________________________
> > > > Do you Yahoo!?
> > > > Yahoo! Hotjobs: Enter the "Signing Bonus"
> > > Sweepstakes
> > > > http://hotjobs.sweepstakes.yahoo.com/signingbo>
> > > nus
> > > >
> > > > _______________________________________________
> > > > AccessD
> > > > mailing list
> > > > AccessD at databaseadvisors.com 
> > > > http://databaseadvisors.com/mailman/listinfo/a>
> > > ccessd
> > > > Website:
> > > > http://www.databaseadvisors.com
> > > >
> > > >
> > >
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com 
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! SiteBuilder - Free web site building tool. Try it!
> http://webhosting.yahoo.com/ps/sb/
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 
> 
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 
> 



More information about the AccessD mailing list