How to make your report run faster

Hi Larry,

Thank you for your reply. This is my first programming in access. I used
to write sql/plsql but the company is downsize so I have to do developing
job, then here is come to this!
- The DB server is Oracle. and I use ODBC to connect to the server. And
use DAO
Dim qd As DAO.QueryDef
Dim db As DAO.Database
- I used form with listbox -multiselect option - to display 6 fields -id,
name, creator, date... etc (7-8K records). It's a long list so I have 3 text
box 1 check box and 1 combobox to do the filter. User have the option to
filter to the site they know or want. or just click the site(s) in listbox.
if they click the listbox then "Preview report" button is enabled. then if
they click "Preview report" button, the report will display. The reasons I
have to do 6 subreports are we need to have all the multi records. and each
record have to display to 1 page or 2 max, each report have to be in a new
page... (The whole application I have to link at least 40 tables together & 2
views).
My main query is more then 10pages and it use for main report. each
subreport have 1 query. and relative by siteid.

the code under "preview report" button is
Set db = CurrentDb
Set qd = db.QueryDefs("SelectionALL")
qd.SQL = GetSQL()
DoCmd.OpenReport "SelectionALL", acViewPreview

and there is a function GetSQL, which will go thru the listbox and pick the
site id and put in an variant name sList (can be 1 siteid or 2,3.. siteid) to
make siteid IN (" & sList & ") " like that.
Ok, those are what I did, and work great. but slow...
Well, since my first time with VBA and access: I'm not understand how it
works, so
-I was try to make the old queries for subreport, define them as pass-thru
queries, then, create new queries that select from those pass-thru. But it
seem not working when I call the main report.
-How do I create view from the application?
-Now I'm thinking of passing the sList to all the queries of each subreport
(?) Haven't reduce the speed yet... at least 30-45seconds for 1 site!

Again, thanks for your help!

AQ

"Larry Linson" wrote:

"A.Q" wrote

First I was try to change the queries for subreport to
Pass-Thru queries.

As you can't do Pass-Thru Queries against a Jet database, I infer that you
are using an Access Client with a Server DB as a backend. It would be nice,
really nice, if you described your environment -- because all you are going
to be able to get is a "generic answer". If we knew _which_ Server DB and
how you are connecting (MDB or MDE, with ODBC or ADP/ADE with OLEDB),
someone might be able to give you some more specific information.

I wrote this program: user can filter then chose
the site(s) they want to have a report on those sites.
If they click "Preview Report", than a main report and
6 subreports with display, all off them related by site ID.
It work pretty well, but ..little slow. So i need to do
something with it to make it work faster.

With a main Report and six additional Reports in Subreport Controls, you are
doing a lot of retrieval work.

If you really mean "filter" in Access terminology, or are using the Filter
or WhereCondition arguments of DoCmd.OpenReport, then you may be able to
speed up retrieval if you, instead, let the user enter information on a
form, then you use VBA to construct a complete SQL Statement for the
Report's Record Source and, in the Open event of the main Report, replace
its RecordSource.

Make sure any Fields for which you supply Criteria (Fields in the Where
clause) are indexed, as are any Fields you use in Joins.

It is possible that, with some thought, you may be able to accomplish the
purpose of the Report with either fewer, or no, Subreports. That will
require a different design, if it is possible, and a more extensive Query.
But it will reduce the amount of work that Access must do.

It's possible that you may be able to create a View in the Server DB that
will force selection and retrieval to be done server-side... if you have
done something that makes Access retrieve all the data and then select on
the client-side, it can almost certainly be improved upon. Access sees no
difference between a server View and a Table.

I don't know what you mean by "can you do query from query?" You can
certainly define a Query and use that as Data Source for another Query..

Larry Linson
Microsoft Access MVP

"A.Q" wrote

- The DB server is Oracle.

I've done no work with Oracle as the Server DB, so I won't have any
"Oracle-specific" tricks.

and I use ODBC to connect to the server. And use DAO

I've done a good deal of work with Access clients, using DAO and ODBC.

- I used form with listbox -multiselect option - to display 6 fields -id,
name, creator, date... etc (7-8K records).

I can't imagine using one listbox to display multiple fields. I can imagine
using a continuous forms view Form with multiple Controls, one per Field.

It's a long list so I have 3 text
box 1 check box and 1 combobox to do the filter. User have the option
to
filter to the site they know or want. or just click the site(s) in
listbox.
if they click the listbox then "Preview report" button is enabled. then
if
they click "Preview report" button, the report will display. The reasons
I
have to do 6 subreports are we need to have all the multi records. and
each
record have to display to 1 page or 2 max, each report have to be in a
new
page... (The whole application I have to link at least 40 tables together
& 2
views).
My main query is more then 10pages and it use for main report. each
subreport have 1 query. and relative by siteid.

I also have never come close to using a Query whose SQL runs for anywhere
_near_ ten pages. You are doing a lot of work.

In my experience (though never with Oracle), the ODBC drivers we used seemed
to do a reasonable job of not altering the SQL too drastically before
sending it on. The ODBC drivers we used were from InterSolv (which I
understand has now been acquired or changed its name). On the other hand, in
older versions of Access, the Jet database engine might decide that the
query was too complex, and retrieve all or most of the information to do the
selection on the user's machine. That is when we resorted to creating Views
to force that work to be done on the server.

the code under "preview report" button is
Set db = CurrentDb
Set qd = db.QueryDefs("SelectionALL")
qd.SQL = GetSQL()
DoCmd.OpenReport "SelectionALL", acViewPreview

and there is a function GetSQL, which will go thru the listbox and pick
the
site id and put in an variant name sList (can be 1 siteid or 2,3.. siteid)
to
make siteid IN (" & sList & ") " like that.
Ok, those are what I did, and work great. but slow...
Well, since my first time with VBA and access: I'm not understand how it
works, so

The fact that your query is doing a lot of work concerns me. That means that
you are doing much different kind of applications than I have ever done. I
had a colleague whose work required similarly complex SQL and, when we
discussed what he was doing, yes, our appllications were very different. So,
I know there are cases where that is required. I also know there are cases
where the database design itself forces complexity that could be avoided.

-I was try to make the old queries for subreport, define them as pass-thru
queries, then, create new queries that select from those pass-thru. But it
seem not working when I call the main report.

When you use Reports embedded in a subreport control and the
LinkMasterFields and LinkChildFields to select, you are doing "filtering"...
reading more records, but displaying only those that match. This, too, will
be less efficient. Unfortunately, you can't reset the RecordSource for the
Report embedded in the Subreport for each Record. But, as I pointed out, the
Subreport functionality is such that filtering is done locally, on the
user's machine.

I am not aware that you cannot use Pass-Thru Queries as the RecordSource of
a Report embedded in a Subreport Control. I haven't tried it, and haven't
tried using a Pass-Thru Query as the data source of a local Query... if the
local query is where the record selection is being done, then all the data
may have had to be transferred across the network already.

-How do I create view from the application?

I've always created Views using the functionality of the Server (in most
cases where I was doing so, that was Informix). As far as I know, the only
situation where you might be able to create a View from the Access client
application would be in an Access ADP/ADE, which can only be used with
Microsoft SQL Server, not any other server DB.

-Now I'm thinking of passing the sList to all the queries of each
subreport
(?) Haven't reduce the speed yet... at least 30-45seconds for 1 site!

One question that I have not asked is this... "slow" compared to _what_? Do
you have an example of similar amounts of work being done much faster using
a different front-end? Can you execute your SQL directly from an Oracle UI
to get a sense of timing -- that is, whether the delay is Oracle or whether
it is in the interface between Oracle and Access? Do you have recording
software that is logging the information passed between Jet/ODBC and Oracle?
That is... retrieve a record, then retrieve all the records that would be on
the associated six subforms.

The only way to really be successful at speeding up the application is to
know where the delay occurs and what may be the cause, so you can address
them. Otherwise, you may "play" with factors that really have very little
influence on the response time.

But, because the retrieval can be forced to be in the server, it certainly
would be worthwhile to look at doing the report _without_ subreports, using
grouping on what are now your main records, and detail on the related
records.

Best of luck with your project.

Larry Linson
Microsoft Access MVP

Hi Larry,

Thanks for your reply and explanations. They help me understand more.
This application will generate report that will pull all the information of
each site to cross check with data entry as if data entry entered the correct
information. Users can pull 1 site or more...
Well, my client said it's slow!, so he wants me to do "something" to
improve the speed. If it not then I have to develop a same thing but using
PowerBuilder, which I affraid cuz I don't know PB that well...!

Do you think if i pass the siteid list to each of queries, that will help?
if I pass in then they would be like "AND SITE_ID IN ('1234','3456')" or
"WHERE SITE_ID IN ('1234','3456') depend on each query. I call a function
main query which the list of site_id in there. I just wondering how are
those queries processed? after main report or same time? does it matter if
each query have that where condition clause or not?

I noticed that you mention about do grouping. If I do grouping can they
retrieve multi records? and how they will display?

Thanks.
AQ
"Larry Linson" wrote:

"A.Q" wrote

- The DB server is Oracle.

I've done no work with Oracle as the Server DB, so I won't have any
"Oracle-specific" tricks.

and I use ODBC to connect to the server. And use DAO

I've done a good deal of work with Access clients, using DAO and ODBC.

- I used form with listbox -multiselect option - to display 6 fields -id,
name, creator, date... etc (7-8K records).

I can't imagine using one listbox to display multiple fields. I can imagine
using a continuous forms view Form with multiple Controls, one per Field.

It's a long list so I have 3 text
box 1 check box and 1 combobox to do the filter. User have the option
to
filter to the site they know or want. or just click the site(s) in
listbox.
if they click the listbox then "Preview report" button is enabled. then
if
they click "Preview report" button, the report will display. The reasons
I
have to do 6 subreports are we need to have all the multi records. and
each
record have to display to 1 page or 2 max, each report have to be in a
new
page... (The whole application I have to link at least 40 tables together
& 2
views).
My main query is more then 10pages and it use for main report. each
subreport have 1 query. and relative by siteid.

I also have never come close to using a Query whose SQL runs for anywhere
_near_ ten pages. You are doing a lot of work.

In my experience (though never with Oracle), the ODBC drivers we used seemed
to do a reasonable job of not altering the SQL too drastically before
sending it on. The ODBC drivers we used were from InterSolv (which I
understand has now been acquired or changed its name). On the other hand, in
older versions of Access, the Jet database engine might decide that the
query was too complex, and retrieve all or most of the information to do the
selection on the user's machine. That is when we resorted to creating Views
to force that work to be done on the server.

the code under "preview report" button is
Set db = CurrentDb
Set qd = db.QueryDefs("SelectionALL")
qd.SQL = GetSQL()
DoCmd.OpenReport "SelectionALL", acViewPreview

and there is a function GetSQL, which will go thru the listbox and pick
the
site id and put in an variant name sList (can be 1 siteid or 2,3.. siteid)
to
make siteid IN (" & sList & ") " like that.
Ok, those are what I did, and work great. but slow...
Well, since my first time with VBA and access: I'm not understand how it
works, so

The fact that your query is doing a lot of work concerns me. That means that
you are doing much different kind of applications than I have ever done. I
had a colleague whose work required similarly complex SQL and, when we
discussed what he was doing, yes, our appllications were very different. So,
I know there are cases where that is required. I also know there are cases
where the database design itself forces complexity that could be avoided.

-I was try to make the old queries for subreport, define them as pass-thru
queries, then, create new queries that select from those pass-thru. But it
seem not working when I call the main report.

When you use Reports embedded in a subreport control and the
LinkMasterFields and LinkChildFields to select, you are doing "filtering"...
reading more records, but displaying only those that match. This, too, will
be less efficient. Unfortunately, you can't reset the RecordSource for the
Report embedded in the Subreport for each Record. But, as I pointed out, the
Subreport functionality is such that filtering is done locally, on the
user's machine.

I am not aware that you cannot use Pass-Thru Queries as the RecordSource of
a Report embedded in a Subreport Control. I haven't tried it, and haven't
tried using a Pass-Thru Query as the data source of a local Query... if the
local query is where the record selection is being done, then all the data
may have had to be transferred across the network already.

-How do I create view from the application?

I've always created Views using the functionality of the Server (in most
cases where I was doing so, that was Informix). As far as I know, the only
situation where you might be able to create a View from the Access client
application would be in an Access ADP/ADE, which can only be used with
Microsoft SQL Server, not any other server DB.

-Now I'm thinking of passing the sList to all the queries of each
subreport
(?) Haven't reduce the speed yet... at least 30-45seconds for 1 site!

One question that I have not asked is this... "slow" compared to _what_? Do
you have an example of similar amounts of work being done much faster using
a different front-end? Can you execute your SQL directly from an Oracle UI
to get a sense of timing -- that is, whether the delay is Oracle or whether
it is in the interface between Oracle and Access? Do you have recording
software that is logging the information passed between Jet/ODBC and Oracle?
That is... retrieve a record, then retrieve all the records that would be on
the associated six subforms.

The only way to really be successful at speeding up the application is to
know where the delay occurs and what may be the cause, so you can address
them. Otherwise, you may "play" with factors that really have very little
influence on the response time.

But, because the retrieval can be forced to be in the server, it certainly
would be worthwhile to look at doing the report _without_ subreports, using
grouping on what are now your main records, and detail on the related
records.

Best of luck with your project.

Larry Linson
Microsoft Access MVP

"A.Q" wrote

Well, my client said it's slow!, so he wants me to do "something" to
improve the speed. If it not then I have to develop a same thing but
using
PowerBuilder, which I affraid cuz I don't know PB that well...!

As I said, unless you can determine where the slowdown is, then you are just
shooting in the dark trying to fix it.

My question would be: what is it that makes the client, or you, believe that
the same kind of application would be faster if developed in Power Builder?

Because all you are doing with PowerBuilder is creating a client
application, and the speed is almost certainly controlled by the way you are
retrieving the data, not by manipulations, etc., in the client application,
it seems unlikely that you'll do more than waste additional time and effort
to little avail.

You haven't responded this, but I urge you to: Carefully review the design
of the Report and see if you can't create a Query that will let you
accomplish similar results without having multiple Subreports.

Is it not possible for you to use Oracle's own facilities to create Views,
as I did with Informix? Sometimes you just are not allowed, but some
discussion with the DBA might get you permission, even if it is normally not
allowed.

It is not clear to me what part "SITE_ID" plays, but anything you can do to
limit the number of records actually retrieved by Oracle and passed over the
network would be helpful. (It would, I am convinced, be more productive if
you could redesign to eliminate the need for the Subreports and then use a
View, if need be, to force the Query to run on Oracle, to assure that only
the final results are passed over the network.)

Larry Linson
Microsoft Access MVP