report running slow

[jasn_78]

hey guys i have the following code for a crosstab report which all runs ok just extremley slow (like 2 mins) to open where as the query the reports is based on takes only about 10 seconds listed below is my code for that report any suggestions would be appreciated.

Option Compare Database
Option Explicit

Private Sub Report_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From crstabSTOCK")
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "ISDP_DESC" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl0.Caption = rst.Fields(i).Name
Case 1
Me.lbl1.Caption = rst.Fields(i).Name
Case 2
Me.lbl2.Caption = rst.Fields(i).Name
Case 3
Me.lbl3.Caption = rst.Fields(i).Name
Case 4
Me.lbl4.Caption = rst.Fields(i).Name
Case 5
Me.lbl5.Caption = rst.Fields(i).Name
Case 6
Me.lbl6.Caption = rst.Fields(i).Name

End Select
skip_it:
Next
rst.Close
Set rst = Nothing

End Sub

Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From crstabSTOCK")
rst.MoveFirst

j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "ISDP_DESC" Then GoTo skip_it

j = j + 1
Select Case j

Case 0
Me.txt0.ControlSource = rst.Fields(i).Name
Case 1
Me.txt1.ControlSource = rst.Fields(i).Name
Case 2
Me.txt2.ControlSource = rst.Fields(i).Name
Case 3
Me.txt3.ControlSource = rst.Fields(i).Name
Case 4
Me.txt4.ControlSource = rst.Fields(i).Name
Case 5
Me.txt5.ControlSource = rst.Fields(i).Name
Case 6
Me.txt6.ControlSource = rst.Fields(i).Name

End Select
skip_it:
Next i
rst.Close
Set rst = Nothing

End Sub

 

[gemma-the-husky]

1. is the code exactly the same in load and open events - you certainly dont need to do it twice

2. is the report based on a query?

3. if so, what exactly are you doing with the record set?

 

[jasn_78]

gemma

the part on report load is for the headers and the part on report open is for the data. yes the report is based on a query but the query itself only takes 15 seconds to run where as the report takes 2 mins miniumum

any ideas would be great

 

[gemma-the-husky]

i can see a difference in the code now

you can time the load/open events easily enough

there is a functrion called TIMEr, which counts elapsed seconds

have 2 variables starttime and endtime (longs)

all you need do is

starttime = timer
endtime = timer at appropriate points

By comparing start/end times you can see where the bottleneck is, and how long those bits of code take.

it may be that formatting the query into the report takes time, or it may be the startup code.

if crtstabstock is a big file, that may slow it down. Its hard to understand exactly what you are doing with that file that needs you to load ther whole thing.

suppress the code in there and see what difference it makes

 

[jasn_78]

gemma i have run both sections by themselves in the report and both ways are taking the same amount of time to open the report so i dont think it is an issue so much with running pretty much the same code twice and as the query only takes 15 seconds to load through the query viewer be it a few thousand records or 2 im really confused on y this is so slow to run.

 

[gemma-the-husky]

2 minutes is definitely a long while for a report to run. This sort of time indicates either a v. large dataset is being processed, or there is a code problem,

so what i was saying was

is it the open and load procedures themselves that are using lots of processor time. These execute first, before the report is populated so you could time these events, and see how long they take. i think you are only examining the first row of the dataset, but i am not completely sure.

you can just put
exit sub
on the first line of each to temporarily disable them

if it is the detail section, then perhaps it depends what you are doing within the report. Are you making lots of calculations/sub totals etc, in changing the query into the formatted report.

How many lines/pages are there in the report. If you modify the query to restrict the number of rows, what effect does that have on the report time.

etc

 

[jasn_78]

ok gemma just put exit sub in after the first case statement and still taking as long.

the query it is looking at does have calculations in it and can vary in size from 2 records to over 5000 but no matter what the size the query still only takes 15 seconds max to run

and even putting the exit sub there still hasnt made the report preform any faster

 

[sonirap]

Hello!!
i'm having the same problem as you, could you solve it? how??
thank you very much

 

[ravencrt]

In my company we just bought a new computer (quad core, 4 GB memory). It runs windws 7 but in french and also the office 2007 is in french. What I've noticed is that any report I run takes about 30 seconds to open and on other computers it takes 2/3 seconds. I've updated office 2007 to sp2 but the problem still persists. Has anyone solved this problem?
I forgot to mention that only when I open a report it takes a long time, everything else works just fine (as it should).