Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Unhappy Unanswered: Functions in queries

    Hi All

    I'm having a minor 'mare with my current project. I have a series of queries that compare figures from x number of weeks ago (4, 13, 26, 52, although it's not really important right now) with current figures. The comparison shows percent loss and percent gain as follows:
    Code:
    PerCentLoss: IIf([Current]>=[Original],0,1-([Current]/[Original]))
    PerCentGain: IIf([Original]=0 And [Current]>0,1,
                 IIf([Current]<=[Original],0,([Current]/[Original])-1))
    On a recordset of about 140000 records, this takes about 40 minutes to run. This is not ideal.

    Would I gain anything by writing a couple of VBA functions and calling them directly from the query?

    TIA

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dunno. why not try it out and report back any findings

    40 minutes for a query is not right!, uneless say its an overnight batch job but even then.....

    I'd the reast of your sql, just in case you are using domain functions.

    user written fucntions are not neccesarily a smart call as
    they are not that efficient
    mean your queries have to run in JET rather than any other db mechanism,

    but sometimes they are appropriate...

    what the full SQL you are using look like.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hmm. The SQL is a bit nasty because I use the GUI for Access queries. Here it is:
    Code:
    SELECT qryExportWeek04FirstWeek.FileDate, qryExportWeek04FirstWeek.WkEnding, qryExportWeek04FirstWeek.Canvasser, qryExportWeek04FirstWeek.Depot, qryExportWeek04FirstWeek.Customer, qryExportWeek04FirstWeek.CustName, qryExportWeek04FirstWeek.CustAddress, qryExportWeek04FirstWeek.Order, qryExportWeek04FirstWeek.CDate, qryExportWeek04FirstWeek.SDate, qryExportWeek04FirstWeek.Quantity AS Original, qryExportWeek04ThisWeek.Quantity AS [Current], IIf([Current]>=[Original],0,1-([Current]/[Original])) AS PerCentLoss, IIf([Original]=0 And [Current]>0,1,IIf([Current]<=[Original],0,([Current]/[Original])-1)) AS PerCentGain, qryExportWeek04ThisWeek.Cancellation
    FROM qryExportWeek04FirstWeek INNER JOIN qryExportWeek04ThisWeek ON (qryExportWeek04FirstWeek.Order = qryExportWeek04ThisWeek.Order) AND (qryExportWeek04FirstWeek.Customer = qryExportWeek04ThisWeek.Customer) AND (qryExportWeek04FirstWeek.Canvasser = qryExportWeek04ThisWeek.Canvasser) AND (qryExportWeek04FirstWeek.Depot = qryExportWeek04ThisWeek.Depot) AND (qryExportWeek04FirstWeek.FileDate = qryExportWeek04ThisWeek.FileDate)
    ORDER BY qryExportWeek04FirstWeek.FileDate, qryExportWeek04FirstWeek.Canvasser, qryExportWeek04FirstWeek.Depot, qryExportWeek04FirstWeek.Customer;
    I'll have a go at writing the functions in VBA and will post an update. Not sure when I'll have a chance to do that - late tonight, I suspect.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so you are pulling data from other queries
    do you know where the performance problem is?
    is it the top query, or the intermediate queries?
    is/are the date columns indexed?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Agreed - what is the difference in speed when you remove the Iif stuff?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    healdem - The problem appears to be from the query on which this one is based. The date fields are not indexed, which might be part of the problem.

    pootle flump - When I replaced the IIf functions with custom VBA functions, there was a small reduction in runtime.

    I'm going to index the date fields in the source tables and have a look at any differences that that makes. However, I've discovered another couple of problems, one which might be related to running the process over a network as opposed to locally, and on which might be due to me developing in Access 2000 but the users having Access 2003 (gotta love consistent IT departments...). I'll do some prodding and post new threads as appropriate.

    Thanks for your help!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by weejas
    pootle flump - When I replaced the IIf functions with custom VBA functions, there was a small reduction in runtime.
    No - I meant if you remove (not replace) the Iif function.

    I want to confirm absolutely that the Iif functions are to blame. VBA user defined functions will ALWAYS run slowly, even if they don't do much.

    If the query runs slow even with Iif removed then we need to start delving into the subqueries.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah, okay.

    I'll try that in a bit, but I'm looking into a possible problem with the data at the moment (I'm sure it would work perfectly without users!).

    I'll report back when I can.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as you are making a lot of use of date matching then I'd suggest the date column(s) should be indexed.. try that see what happens
    time each query element and sub query and pin down which query is taking the time. that may mean writing a quick and dirty form which runs each query separately and times it... run the queries say 10..20 times and average out the times.

    you are basing the input of one query on the output of others. so the question has got to be whewre is the time being used/lost.

    if the sub query/ feedstock query is the one thats gobbling the time, altering the top level query is just rearranging the deckchairs on the Titanic. for a query thats taking this long either you have a vast amount of data or there is soemthign int he query which is killing performance.. my suspicion would be you may be using domain functions which have been know to be query killers

    you need to understand where the time is going.

    it may be that you can reduce the query significnatly by making sure you bin the irrelevant data at the lowest level queries, so subsequent queries are doing as little work as possible.

    if a query is taking a long time (read more than say 15..30 seconds) then you need to carefull examine each element, make sure all where clause columns (or at least all columns frequently used in where clauses) are indexed.

    however it may just be that you are asking to much of JET and the data may be better off in a server product such as SQL server, MySQl or Postgres.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Right. I think I've got part of this sorted now.

    The problem is that I have two or three levels of queries, and the date selection is happening on the final level. I'm going to go through and change things so that the date restriction happens on the first level instead. I've also indexed the fields that are used in WHERE clauses.

    When I tried this with the table that first prompted this thread, the process ran much faster. Also, yesterday I was working on totally local files. This time, the files are being exported to a network drive at another site!

    Thanks again!

Posting Permissions

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