Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report generation takes forever with dmin/dmax fn (A2k-SR1)

    Hi,

    I have a select query that gets data from a few tables. The resulting recordset has about 23000 records and about 16 fields. The query runs in a split second, so nothing complicated in the calculated fields.

    When I use a report to display a summary of data from the same query, the report runs pretty smartly as well considering it is generating subtotals for a few categories of information. As soon as I put the following formulae in text boxes in the report header, the report takes about 5 minutes to generate :

    =Format(DMin("[XDate]","Call Details with Destinations","[XDate] <> Null"),"dd/mm/yy") and
    =Format(DMax("[XDate]","Call Details with Destinations","[XDate] <> Null"),"dd/mm/yy")

    The idea is to show the period over which the data extends as "From" and "To" dates.

    Any ideas why this should slow things down so much and any tips to improve it ?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    In the first place, you can omit the conditions. They are incorrect and unnecessary.

    DMin and DMax ignore empty values, so there is no need to check for that.

    Also, the correct way would be
    Not IsNull([XDate])
    or
    [XDate] Is Not Null

    If that doesn't help, consider creating a totals query that computes the min and max date. Then, create a report bound to this query and display the min and max date in it. Put this as a subreport in the report header of your report.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    And to add to Hans' excellent response, make sure that [XDate] is indexed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    Hi Hans,

    I tried leaving out the conditions, but I get errors in those two text boxes and no speed improvement. The syntax is <font color=red>DMax(expr, domain</font color=red><font color=448800>[, criteria]</font color=448800><font color=red>)</font color=red>, so I would have thought leaving out everything between the [ and ] would have been OK. This is what I tried :

    <font color=red>=Format(DMax("[XDate]","Call Details with Destinations"),"dd/mm/yy")</font color=red>

    What have I done wrong here ? In any case, I can drop the null check here as you said (I didn't know dmax, etc ignored them anyway, thanks for the tip).

    This is one of my really early db's that I had to work on, and I remember learning recently that it is easy, as you described to get an error when checking for nulls. If a logical expression contains a term that evaluates to null, then the entire expression would be null and wouldn't return a boolean as one desired. That is not the case with the first of your suggested alternatives viz. <font color=red>Not IsNull([XDate])</font color=red>, but I would have thought your second one viz. <font color=red>[XDate] Is Not Null</font color=red> would always give an error if <font color=red>[XDate]</font color=red> contained a null. Am I wrong here ?

    Getting back to my problem, I think a sub-report will do the job. Thanks for the idea. I'll try it shortly,

    Regards,

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    Hi Mark,

    In this case, <font color=red>"Call Details with Destinations"</font color=red> refers to my select query and <font color=red>XDate</font color=red> is one of the calculated fields therein. I don't know how to index a field in a query, but I do know how to define an index for a field in a table.

    Am I trying to use the dmax and dmin functions in the wrong context here ?

    A bit of background, my table contains data from a telephone logging system, imported from really messy ASCII files. I then use the query to extract the portions of the date information from the ASCII strings that partially describe the date (the year part is missing) and recreate a valid <font color=red>XDate</font color=red> to use to query for monthly or weekly reports.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    Hello Adrian,

    I don't see why DMax without a condition wouldn't work. But since you get no speed improvement, I'd concentrate on the subreport idea.

    About Is Null:

    If you look at the SQL statements of queries, you'll see that checking for a field being empty is always in the form

    WHERE fieldname Is Null

    or

    WHERE fieldname Is Not Null

    The third argument of the domain functions (DMin, DMax, etc.) is equivalent to the WHERE part of a query (without the word WHERE).

    Regards, Hans

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    You can't index a field in a query, only a field in a table. Your problem is that to evaluate DMin and DMax of XDate, Access has to look at every record in those queries! I'm not exactly sure what XDate is or how it is calculated, but it would seem that it would be good idea to create it in your table as part of your import procedures. Then it could be indexed, and, for example, you could just use: DMax("XDate","sometablename").
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    Hi All,

    Thanks for the suggestions.

    I think the bottom line is that however I want to do it, getting the min and max from 23,000 records is what is taking the time. Even a query that has only a dmin or dmax field is the same.

    I don't want manipulate the data beforehand and create the <font color=red>XDate</font color=red> field there as it adds extra steps for the users to bungle. If I did have it in my imported data table and indexed it, it would probably make it a lot quicker as Mark suggested.

    I will fiddle with the syntax of my statements in the report textboxes with the domain fn's to see why I was getting those errors when I removed the "where" part anyway.

    Regards,

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    Adrian -
    Another approach might be to use a sort. The DMax and DMin functions sort of do that, but in my experience they aren't very efficient. If you open a recordset sorted in ascending order, and look at the first record, it will be the record returned with DMin, and if go to the last record in the recordset, it will be what DMax would return. The advantage of this approach is that you get both values with one operation, which could theoretically cut your run time in half. You could also try it with a TOP1 criteria and use two sorts, but I doubt that would be much quicker than a DMax and DMin. Just another way to try to tweak things.
    Wendell

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report generation takes forever with dmin/dmax fn (A2k-SR1)

    The Dmax was failing because I had faulty data and one record had #error for the XDate field as the month was 0 from the ASCII import ! At least that explains that problem. Thanks for the hints on checking for null.

Posting Permissions

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