 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2005How to make your report run faster
Hi all, 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. First I was try to change the queries for subreport to Pass-Thru queries, but I find out subreport can't run on pass-thru query. So I tried create a new query select everything from the pass-thru query. This doesn't work on me(?) or I did something wrong? can you do query from query? and that can run with subreport in main report? Thanks in advance, AQ > 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 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 > > First I was try to change the queries for subreport to > > Pass-Thru queries. [quoted text clipped - 42 lines] > Larry Linson > Microsoft Access MVP > - 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 [quoted text clipped - 10 lines] > 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 > > - The DB server is Oracle. > [quoted text clipped - 117 lines] > Larry Linson > Microsoft Access MVP > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.