Andy Lacey
andy at minstersystems.co.uk
Thu Jan 22 03:02:53 CST 2004
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/accessd
Website: http://www.databaseadvisors.com