Slow report opening

I

Ian Baker

I have a report in an Artwork database that shows each piece of artwork and
all the valuations that have been done over the years. The report has:
Report Header
Page Header
ArtID Header
Detail
ArtID Footer
Page Footer

In the ArtID Footer I have 2 fields [ctlTotalIncrease] and [ctlPercentage]
that have to be calculated and inserted for each ArtID.

I have in the OnFormat property of the Detail the following code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo HandleErr
Dim x As Long
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT tblValuation.ArtID, " & _
"tblValuation.ValuationAmount " & _
"FROM tblValuation " & _
"WHERE (((tblValuation.ArtID) = " & Me!ctlArtID & ")) " & _
"ORDER BY tblValuation.ValuationDate;", dbOpenDynaset)

x = ctlPurchasePrice
If x = 0 Then 'Art was donation so no purchase price - so use 1st
valuation
rst.MoveFirst
x = rst!ValuationAmount
rst.MoveLast
ctlTotalIncrease = rst!ValuationAmount - x
ctlPercent = (rst!ValuationAmount / x) - 1
lblTotalIncrease.Caption = "Total increase from 1st valuation"
Else 'Art was purchased so use purchase price
rst.MoveLast
ctlTotalIncrease = rst!ValuationAmount - x
ctlPercent = (rst!ValuationAmount / x) - 1
lblTotalIncrease.Caption = "Total increase from purchase"
End If

rst.Close
Set rst = Nothing

ExitHere:
Exit Sub
HandleErr:
StandardError Err, Err.Description, "frmValuation - Form_AfterUpdate"
Exit Sub

End Sub

My question is - is there a better way to do this?
For example if the SQL in the above code was hard stored as a query would it
run faster (but then how do I pass the ctlArtID parameter) or should the
code be placed somewhere else or am I better to make a temp table first????

Any comments are greatly appreciated
 
A

Allen Browne

Code in a report event is generally the slowest way to achieve a result.
Code that opens a record like that on every row is certainly the slowest
approach.
Some alternatives:

1. Query.
The best way would be to base the report on a query. Include the
tblValuation in the query. It may be necessary to include an outer join to
get the desired result.

2. Subquery
If that can't work for some reason, you may be able to use a subquery. That
would mean typing something like this into the Field row of your query:

LastValuation: ( SELECT TOP 1 ValuationAmount FROM tblValuation
WHERE tblValuation.ArtID = tblArt.ArtID
ORDER BY tblValuation.ValuationDate, tblValuation.ValuationID DESC )

3. Stacked query
If that's still no good, you could create a query, and save it. Then use
that query as the source query to another query, which finally becomes the
source for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ian Baker said:
I have a report in an Artwork database that shows each piece of artwork and
all the valuations that have been done over the years. The report has:
Report Header
Page Header
ArtID Header
Detail
ArtID Footer
Page Footer

In the ArtID Footer I have 2 fields [ctlTotalIncrease] and [ctlPercentage]
that have to be calculated and inserted for each ArtID.

I have in the OnFormat property of the Detail the following code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo HandleErr
Dim x As Long
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT tblValuation.ArtID, " & _
"tblValuation.ValuationAmount " & _
"FROM tblValuation " & _
"WHERE (((tblValuation.ArtID) = " & Me!ctlArtID & ")) " & _
"ORDER BY tblValuation.ValuationDate;", dbOpenDynaset)

x = ctlPurchasePrice
If x = 0 Then 'Art was donation so no purchase price - so use 1st
valuation
rst.MoveFirst
x = rst!ValuationAmount
rst.MoveLast
ctlTotalIncrease = rst!ValuationAmount - x
ctlPercent = (rst!ValuationAmount / x) - 1
lblTotalIncrease.Caption = "Total increase from 1st valuation"
Else 'Art was purchased so use purchase price
rst.MoveLast
ctlTotalIncrease = rst!ValuationAmount - x
ctlPercent = (rst!ValuationAmount / x) - 1
lblTotalIncrease.Caption = "Total increase from purchase"
End If

rst.Close
Set rst = Nothing

ExitHere:
Exit Sub
HandleErr:
StandardError Err, Err.Description, "frmValuation - Form_AfterUpdate"
Exit Sub

End Sub

My question is - is there a better way to do this?
For example if the SQL in the above code was hard stored as a query would it
run faster (but then how do I pass the ctlArtID parameter) or should the
code be placed somewhere else or am I better to make a temp table first????

Any comments are greatly appreciated
Click to expand...
 
I

Ian Baker

Allen
As usual to you many thanks - option 2 below creating a sub query worked
perfectly although the report wouldn't accept sub queries in grouping so to
overcome that I created a subreport. The end result SQL for the subreport
is:
SELECT tblValuation.ArtID, tblValuation.ValuationID,
tblValuation.ValuationDate, tblValuation.ValuationAmount,
tblArt.PurchasePrice, (SELECT TOP 1 tblValuation.ValuationAmount FROM
tblValuation WHERE (((tblValuation.ArtID)=[tblArt].[ArtID])) ORDER BY
tblValuation.ValuationDate, tblValuation.ValuationID DESC; ) AS
FirstValuation, (SELECT TOP 1 tblValuation.ValuationAmount FROM tblValuation
WHERE (((tblValuation.ArtID)=[tblArt].[ArtID])) ORDER BY
tblValuation.ValuationDate DESC , tblValuation.ValuationID; ) AS
LastValuation,
IIf([PurchasePrice]=0,[LastValuation]-[FirstValuation],[LastValuation]-[Purc
hasePrice]) AS TotalIncrease,
IIf([PurchasePrice]=0,([LastValuation]/[FirstValuation])-1,([LastValuation]/
[PurchasePrice])-1) AS PercIncrease
FROM tblArt INNER JOIN tblValuation ON tblArt.ArtID = tblValuation.ArtID
ORDER BY tblValuation.ArtID, tblValuation.ValuationID
WITH OWNERACCESS OPTION;

--
Regards
Ian Baker
Jackaroo Solutions Melb Aust
Jackaroo IT - an IT Mgmt & Help Desk application at http://jackaroo.net.au
Allen Browne said:
Code in a report event is generally the slowest way to achieve a result.
Code that opens a record like that on every row is certainly the slowest
approach.
Some alternatives:

1. Query.
The best way would be to base the report on a query. Include the
tblValuation in the query. It may be necessary to include an outer join to
get the desired result.

2. Subquery
If that can't work for some reason, you may be able to use a subquery. That
would mean typing something like this into the Field row of your query:

LastValuation: ( SELECT TOP 1 ValuationAmount FROM tblValuation
WHERE tblValuation.ArtID = tblArt.ArtID
ORDER BY tblValuation.ValuationDate, tblValuation.ValuationID DESC )

3. Stacked query
If that's still no good, you could create a query, and save it. Then use
that query as the source query to another query, which finally becomes the
source for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ian Baker said:
I have a report in an Artwork database that shows each piece of artwork and
all the valuations that have been done over the years. The report has:
Report Header
Page Header
ArtID Header
Detail
ArtID Footer
Page Footer

In the ArtID Footer I have 2 fields [ctlTotalIncrease] and [ctlPercentage]
that have to be calculated and inserted for each ArtID.

I have in the OnFormat property of the Detail the following code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo HandleErr
Dim x As Long
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT tblValuation.ArtID, " & _
"tblValuation.ValuationAmount " & _
"FROM tblValuation " & _
"WHERE (((tblValuation.ArtID) = " & Me!ctlArtID & ")) " & _
"ORDER BY tblValuation.ValuationDate;", dbOpenDynaset)

x = ctlPurchasePrice
If x = 0 Then 'Art was donation so no purchase price - so use 1st
valuation
rst.MoveFirst
x = rst!ValuationAmount
rst.MoveLast
ctlTotalIncrease = rst!ValuationAmount - x
ctlPercent = (rst!ValuationAmount / x) - 1
lblTotalIncrease.Caption = "Total increase from 1st valuation"
Else 'Art was purchased so use purchase price
rst.MoveLast
ctlTotalIncrease = rst!ValuationAmount - x
ctlPercent = (rst!ValuationAmount / x) - 1
lblTotalIncrease.Caption = "Total increase from purchase"
End If

rst.Close
Set rst = Nothing

ExitHere:
Exit Sub
HandleErr:
StandardError Err, Err.Description, "frmValuation - Form_AfterUpdate"
Exit Sub

End Sub

My question is - is there a better way to do this?
For example if the SQL in the above code was hard stored as a query
Click to expand...
would
Click to expand...
 
Top