[AccessD] Major Performance Issue! + Code

Sad Der accessd666 at yahoo.com
Thu Jan 22 00:25:38 CST 2004


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/


More information about the AccessD mailing list