Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-24-2008, 07:22 PM   #1
jasn_78
Registered User
 
Join Date: Jul 2001
Location: Brisbane Qld
Posts: 214
Thanks: 0
Thanked 0 Times in 0 Posts
jasn_78
Send a message via MSN to jasn_78 Send a message via Skype™ to jasn_78
report running slow

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

jasn_78 is offline   Reply With Quote
Old 02-25-2008, 02:05 AM   #2
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,814
Thanks: 56
Thanked 1,028 Times in 994 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
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?
gemma-the-husky is offline   Reply With Quote
Old 02-25-2008, 02:18 PM   #3
jasn_78
Registered User
 
Join Date: Jul 2001
Location: Brisbane Qld
Posts: 214
Thanks: 0
Thanked 0 Times in 0 Posts
jasn_78
Send a message via MSN to jasn_78 Send a message via Skype™ to 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

jasn_78 is offline   Reply With Quote
Old 02-25-2008, 04:56 PM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,814
Thanks: 56
Thanked 1,028 Times in 994 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
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
gemma-the-husky is offline   Reply With Quote
Old 02-25-2008, 05:06 PM   #5
jasn_78
Registered User
 
Join Date: Jul 2001
Location: Brisbane Qld
Posts: 214
Thanks: 0
Thanked 0 Times in 0 Posts
jasn_78
Send a message via MSN to jasn_78 Send a message via Skype™ to 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.
jasn_78 is offline   Reply With Quote
Old 02-25-2008, 05:23 PM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,814
Thanks: 56
Thanked 1,028 Times in 994 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
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
gemma-the-husky is offline   Reply With Quote
Old 02-25-2008, 08:08 PM   #7
jasn_78
Registered User
 
Join Date: Jul 2001
Location: Brisbane Qld
Posts: 214
Thanks: 0
Thanked 0 Times in 0 Posts
jasn_78
Send a message via MSN to jasn_78 Send a message via Skype™ to 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

jasn_78 is offline   Reply With Quote
Old 04-27-2011, 05:22 AM   #8
sonirap
Newly Registered User
 
Join Date: Apr 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
sonirap is on a distinguished road
Re: report running slow

Hello!!
i'm having the same problem as you, could you solve it? how??
thank you very much
sonirap is offline   Reply With Quote
Old 05-19-2011, 06:16 AM   #9
ravencrt
Newly Registered User
 
Join Date: Jan 2011
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
ravencrt is on a distinguished road
Re: report running slow

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).

ravencrt is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing Report query parameters in code before running Report sportsguy Reports 5 03-29-2007 09:05 AM
Report Query Not Running canadaboy Reports 3 01-07-2004 05:03 AM
Running Sum Continuing to Next Report Hiten Reports 2 05-20-2003 03:00 PM
Running Sum in a report AndyS48UK Reports 2 06-28-2002 12:07 AM
Running Report for each value jekirksey Reports 1 06-24-2002 02:01 PM




All times are GMT -8. The time now is 06:04 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World