John W. Colby
jwcolby at colbyconsulting.com
Thu Jan 22 09:14:18 CST 2004
I just put it up 10 minutes ago.
And I really don't mind at all. All of the demos I do are designed to help
our list members to learn neat stuff and do their jobs better.
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 10:03 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Major Performance Issue! + Code
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
>
>
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com