Re: How to make your report run faster

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" <AQ@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
>
>
>
.

Loading