Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    The Netherlands, Geleen
    Posts
    8

    Wink Unanswered: Boost up performance?!

    I'm lately working on some reports someone else has developed. But now we've got a performance problem.. when I start up the app(form) - that can start up several reports - it takes min. 5 secs to apear. Who's knows some things I should take a look at to boost up the performance?

    Tnx

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Performance is a tricky thing. You have to look at the CPU of the Computer, Speed of Network (if ran over a network), the way you program. You have to look at how involved you SQL statments are that is creating you Report and what you are doing to create the report in general.


    I have a report the ran several queries and Unions with in these to create the report. It took 5 - 10 mins to create the report (becuase of all the calculations and other comparisons it had to do). I ended up creating a dummy table in Access that I use now. I wrote SQl statements the first delete existing data, several insert statment that perform some caclulations and add it to the dummy table, and then one final query, that the report is based on. It now takes between 10-20 secs to create the report. (signifcant improvement).

    You have to use your imagination the think of doing things differently then before, even if that method isn't the best (according to DB theory).

    I even, can I even say it, store caclulated values from time to time, to speed up reporting. One client had a system requirement that a report willnot take longer then 30 seconds (no matter how involved) to load. In order to meet this requirement I would have to store some caclulated values.

    Good Luck finding what works for you.

    S-

  3. #3
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    We need more information - a lot more!! Which version of Access, OS, hardware platform....what does the form do, specifically.....how big is the data set behind the reports....are the reports based on tables or queries....do the queries contain criteria.....and so on.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  4. #4
    Join Date
    Feb 2004
    Location
    The Netherlands, Geleen
    Posts
    8

    Smile Details of Reports

    The app is written with MS Acces versions 97-XP. The app must be able to function on the latest windows versions(OS: 98-NT-2000-XP). In the app which is nothing more than a form you can call several reports. Each report uses(can use) the following items:
    1) Database tables from a c/s app that can run with Oracle and SQL Server.
    2) Reports: System reports -> Entity reports -> general reports
    3) Tables of the c/s app and tables generated for the Report app.
    4) Macros: in the Report app i've defined macros to start up and end actions when a user takes action(usually a button OnClick event).
    5) In some forms that deal with payments i've added some simple functions in the code of the form. For example a user can specify 5 items (name & price) when he changes the price the total excl. tax changes also (OnExit()). The user can aslo specify the tax rate that comes along with the declared actions. When the Out-of-focus event accurs all fields are updated.

    Maybe you guys need some code, but because of the amount i cannot add all of the code.. it's a lot!!! So when you want to see some minor parts please ask. Hopefully these details make sence to you.. tnx for ya help
    Last edited by kixer; 05-03-04 at 05:08.

  5. #5
    Join Date
    Feb 2004
    Location
    The Netherlands, Geleen
    Posts
    8
    After testing all the reports separately I think most of the problem is caused by the report listed below:

    Code:
    UPDATE FMRgfm7wcpn INNER JOIN TGFM8WCPN ON FMRgfm7wcpn.f7gfmwcpncode = TGFM8WCPN.f8gfmwcpncode SET TGFM8WCPN.f8gfmwcpncontact = [FMRgfm7wcpn]![f7gfmwcpncontact]+" ", TGFM8WCPN.f8gfmwcpncost = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost1]),0,([FMRgfm7wcpn]![f7gfmwcpncost1]+0)*100)+IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost2]),0,([FMRgfm7wcpn]![f7gfmwcpncost2]+0)*100)+IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost3]),0,([FMRgfm7wcpn]![f7gfmwcpncost3]+0)*100)+IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost4]),0,([FMRgfm7wcpn]![f7gfmwcpncost4]+0)*100)+IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost5]),0,([FMRgfm7wcpn]![f7gfmwcpncost5]+0)*100), TGFM8WCPN.f8gfmwcpntext = [FMRgfm7wcpn]![f7gfmwcpntext]+" ", TGFM8WCPN.f8gfmwcpncontactinitials = [FMRgfm7wcpn]![f7gfmwcpncontactinitials]+" ", TGFM8WCPN.f8gfmwcpncontactgender = IIf([FMRgfm7wcpn]![f7gfmwcpncontactgender]="2","F","M"), TGFM8WCPN.f8gfmwcpncontacttitle = [FMRgfm7wcpn]![f7gfmwcpncontacttitle]+" ", TGFM8WCPN.f8gfmwcpntaxpercentage = [FMRgfm7wcpn]![f7gfmwcpntaxpercentage]+0, TGFM8WCPN.f8gfmwcpnreference = [FMRgfm7wcpn]![f7gfmwcpnreference]+" ", TGFM8WCPN.f8gfmwcpndate = [FMRgfm7wcpn]![f7gfmwcpndate], TGFM8WCPN.f8gfmwcpncost1 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost1]),0,([FMRgfm7wcpn]![f7gfmwcpncost1]+0)*100), TGFM8WCPN.f8gfmwcpncost2 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost2]),0,([FMRgfm7wcpn]![f7gfmwcpncost2]+0)*100), TGFM8WCPN.f8gfmwcpncost3 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost3]),0,([FMRgfm7wcpn]![f7gfmwcpncost3]+0)*100), TGFM8WCPN.f8gfmwcpncost4 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost4]),0,([FMRgfm7wcpn]![f7gfmwcpncost4]+0)*100), TGFM8WCPN.f8gfmwcpncost5 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncost5]),0,([FMRgfm7wcpn]![f7gfmwcpncost5]+0)*100), TGFM8WCPN.f8gfmwcpncosttext1 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncosttext1])," ",[FMRgfm7wcpn]![f7gfmwcpncosttext1]), TGFM8WCPN.f8gfmwcpncosttext2 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncosttext2])," ",[FMRgfm7wcpn]![f7gfmwcpncosttext2]), TGFM8WCPN.f8gfmwcpncosttext3 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncosttext3])," ",[FMRgfm7wcpn]![f7gfmwcpncosttext3]), TGFM8WCPN.f8gfmwcpncosttext4 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncosttext4])," ",[FMRgfm7wcpn]![f7gfmwcpncosttext4]), TGFM8WCPN.f8gfmwcpncosttext5 = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpncosttext5])," ",[FMRgfm7wcpn]![f7gfmwcpncosttext5]), TGFM8WCPN.f8gfmwcpndeliverydate = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpndeliverydate]),CVDate("25-01-1870"),[FMRgfm7wcpn]![f7gfmwcpndeliverydate]), TGFM8WCPN.f8gfmwcpndeliverydateto = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpndeliverydateto]),CVDate("25-01-1870"),[FMRgfm7wcpn]![f7gfmwcpndeliverydateto]), TGFM8WCPN.f8gfmwcpndelivery = IIf(IsNull([FMRgfm7wcpn]![f7gfmwcpndelivery])," ",[FMRgfm7wcpn]![f7gfmwcpndelivery])
    WITH OWNERACCESS OPTION;
    Hopefully some of you will have some tips for me. Tnx!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •