Solved

Expression Query and Module slow down performance (in a Form) tremendously

Posted on 2004-09-24
39
634 Views
Last Modified: 2012-05-05
Hi,

I hope someone might be able to provide me an answer to to why the performance in a form (datasheet view is very slow).   Before I provide more details, I'd like to deduct the "issue of hardware" (I tested the same db on 3 different machines -- all PIIIs -- ... the db runs consistentely slow on all machines).

Please bear w/ me during this -- I think -- long thread, but I believe it's the only way to describe the problem accurately.



Okay, here's what I have (I will use sample data for explaining the issue best).

Table:
- Contains 433 records
- 5 fields; Field1 values: "A, B, C, D"; Field2 values: "1, 2, 3, 4, 5"; Field3 values: "High, Medium, Low"; Field4: A, B, C, D"; Field5: "1, 2, 3, 4, 5, .... 97, 98, 100"

Note: There are a few other fields in the table, but the 5 listed fields above (used for prioritization of projects) are the "main driver" as to why the form "is slow".

Query:
- In a query, I pull the 5 fields plus 5 corresponding expressions
- Each expression looks up a value from a module.   For instance:   I query for the field "DoI" plus determine its corresponding expression "GetDoIPoints: DoIPoints([DoI])" (from module -- listed below)

Module:
- For all 5 fields I have a function that allows the query expression to find a corresponding value.
- For instance, for "GetDoIPoints" I use the following function:

********************************************
Public Function DoIPoints(DoI As Variant) As Variant  
        ' Determines the "Degree of Importance" factor
        Select Case DoI
            Case "A": DoIPoints = "0.5333"
            Case "B": DoIPoints = "0.2667"
            Case "C": DoIPoints = "0.1333"
            Case "D": DoIPoints = "0.0667"
        End Select        
End Function
********************************************

So again, in my query, I list the 5 fields plus 5 expressions that pull values (e.g. "0.5333" if DoI was "A").

Then, I utilize another expression that calculates an "overall score" for each record.   This expression looks like this:

PrioritizationValue: CDbl(Nz([GetDoIPoints]*0.3478+[GetValueToMissionPoints]*0.2609+(Format([GetBMPriorityPoints]*0.1304)),'0.00000')+[GetCPLPoints]*0.1739+[GetInfluencePoints]*0.087)

Essentially, I end up with a "score" by which I can rank the importance of each of the 433 records (projects).  The expression uses the 5-decimal "PrioritizationValue" as rank, and resorts all the records if a value has changed in any of the 5 fields.  So, if a record had "B" for DoI and its value was changed to "A" then the record is immediately re-ranked in the query (e.g. rank was "73" with value "B", but it is now value rank "65" with "A".

Finally, I have come across a very useful module that allows me to utilize a "RunSum" in the query.   One of the additional fields is "Budget".   Next to its "Budget" field, I use the "RunSum" expression which adds the budgeted $$$ value of the current record plus to cumulative $$$ value of the previous records.

The benefit of this function (listed below) is that I can calculate the cumulative running sum of the Budget field, independent from a record ID.  

I use the following module for calculating the RunSum (which is always recalculated based on the changes in PrioritizationValue (as a result of changes to the 5 fields):

********************************************
Option Explicit

Public Function qryRunSum(idName As String, idValue, sumField As String)

   Dim db As DAO.Database, rst As DAO.Recordset, subSum
   
   Set db = CurrentDb()
   
   'Make sure to adjust the query name below!!!
   Set rst = db.OpenRecordset("qry01PrioritizationData--Sorted", dbOpenDynaset)
     
   ' Find the current record via proper syntax for Data Type.
   Select Case rst.Fields(idName).Type
      Case dbLong, dbInteger, dbCurrency, _
           dbSingle, dbDouble, dbByte 'Numeric Data Type
         rst.FindFirst "[" & idName & "] = " & idValue
      Case dbText 'Text Data Type
         rst.FindFirst "[" & idName & "] = '" & idValue & "'"
      Case dbDate 'Date Data Type
         rst.FindFirst "[" & idName & "] = #" & idValue & "#"
      Case Else
         rst.MovePrevious 'Move to BOF
   End Select
   
   ' Running Sum (subTotal) for each record occurs here.
   Do Until rst.BOF
      subSum = subSum + Nz(rst(sumField), 0)
      rst.MovePrevious
   Loop
   
   qryRunSum = subSum
   
   Set rst = Nothing
   Set db = Nothing
     
End Function

********************************************

Wow, I understand that this is a long question.   Okay, here now is the actual problem.

1. I use a form that shows the 5 fields, PrioritizationValue, Budget, and RunSum.   Records are being displayed in "datasheet view".
2. All calculations are done properly.   All resorting of records also occur properly.
3. However, when "scrolling down" the records (with mouse on vertical scroller), it appears that almost all records are constantly recalculated and "repainted" to the window.    This "repainting" of the records is really really slow.   Again, no actual "resorting" occurs at this moment.
4. Using the Up/Down arrow keys makes it appears to be a bit faster... but then I only go 1 records at a time.

So, my question is... do I have to much "math" involved in this process.    As of now, I couldn't imagine as to why "weighted averaging" of 5 fields (w/ look-up values in module) would slow down the process so much when reviewing all 433 records in the datasheet form.

Does anyone know what's going on and how I could improve the performance when viewing the records?

Thanks so much in advance,
Tom









 
0
Comment
Question by:TomBock2004
  • 22
  • 16
39 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
How is your query sorted? The runsum function is fine, but it takes the long way round to totalling up the values and could probably be done much more efficiently in SQL. The PrioritizationValue calculation also looks to be using some redundant functions (like Format), but without investigating further, I couldn't say.

Here is a like-for-like replacement for RunSum that might be faster:
Public Function qryRunSum(idName As String, idValue, sumField As String)

   Dim db As DAO.Database, rst As DAO.Recordset, subSum
   
   Set db = CurrentDb()
   
   'Make sure to adjust the query name below!!!
   Set rst = db.OpenRecordset("SELECT SUM([" & sumField & "]) As RunningSum FROM qry01PrioritizationData--Sorted WHERE [" & idName & "]<" & idValue)
   RunSum=rst!RunningSum
   rst.Close
   Set rst=Nothing      
End Function

Unlike the original though, this assumes that the field that you are passing to the query (in idName) is a numeric field - it does not work if the field is any other data type (although could be made to).
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Why are you trying to mechanize a sort in VBA when Jet can do it via a clause (far more quickly) in your query? Can you not invoke a SORT BY clause to achieve your aim?
0
 

Author Comment

by:TomBock2004
Comment Utility
shanesuebsahakarn:

Thanks for the prompt reply...

I replaced my RunSum module with yours... you choose the proper query name so I didn't make any modifications to the function.

Now, however, I get an error message:

Run-Time error '3131':
Syntax error in FROM clause

Do you know how to modify the SQL statement to prevent the error?

Thanks,
Tom



0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Oops, I forgot about the - signs. Try this:

Public Function qryRunSum(idName As String, idValue, sumField As String)

   Dim db As DAO.Database, rst As DAO.Recordset, subSum
   
   Set db = CurrentDb()
   
   'Make sure to adjust the query name below!!!
   Set rst = db.OpenRecordset("SELECT SUM([" & sumField & "]) As RunningSum FROM [qry01PrioritizationData--Sorted] WHERE [" & idName & "]<" & idValue)
   RunSum=rst!RunningSum
   rst.Close
   Set rst=Nothing      
End Function
0
 

Author Comment

by:TomBock2004
Comment Utility
GRayL:

Not sure if I don't understand your suggestion or if my explanation wasn't thorough enough.  

I'm not a developer so I don't know much about JET -- heard about it what wouldn't be able to talk about it intelligently 8)

I certainly don't want to create an unnessary mechanism.

Let me attempt to recap the process:
- Open a form that contains 5 factors (fields)
- Based on value of factor, I pull a corresponding score
- PrioritizationValue calculates all scores into a single overall score
- That overall score is used to sort the records
- Since overall score is always changing due to adjustments of 5 factors, the records are always resorted
- RunSum function allows to properly show the new cumulative budget spent (based on top to bottom priority)

- The math works properly; the resorting works properly as well
- The problem is that the form is very slow to show all recors (I don't see that problem in the actual query though).

Tom



0
 

Author Comment

by:TomBock2004
Comment Utility
shanesuebsahakarn:

Wow, what a prompt reply...

ok, I don't get the error any longer.   However, I now get "#Error" in the RunSum field.

Based on what I have seen in the past, I must make sure that the value is stored/calculated as a DOUBLE datatype.

Any suggestions how to do that?

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
It might be to do with the data types - what data type is the field that you are passing in idName?

Before you check that though, I made another mistake - this line:
RunSum=rst!RunningSum

should actually read:
qryRunSum=rst!RunningSum
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
One thing to bear in mind - I can't guarantee that this will improve performance, particularly in the form. There are other factors to be taken into account in the form as well but hopefully this might go some way to fixing it. Does your query need to be updatable?
0
 

Author Comment

by:TomBock2004
Comment Utility
The RunSum does not add up properly now... can't make sense of the cumulative sums.

I think the performance has increased though...

Additionally, I took out an expression that threw an error message when 2 PrioritizationValues were identical.

In such case (when 2 consecutive values are the same), the RunSum does not add properly.  

Having taking it out made and improvement.

I like your shortened and more brief approach about the RunSum; unfortunately though, the values don't add up anymore.

Would you might giving it another try?

I'll check tomorrow morning again.


Good night for now,
Tom






0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
OK but do me a favour - please post back to this question when you check back, just to trigger a reminder to me so I don't forget to look (as I'm going to bed now myself :-)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Could you also post the full SQL of the query?
0
 

Author Comment

by:TomBock2004
Comment Utility
Here's the SQL:

Query 1 -- "qry01PrioritizationData"
===============================
SELECT tbl01PrioritizationData.TrackingNumber, tbl01PrioritizationData.DoI, DoIPoints([DoI]) AS GetDoIPoints, tbl01PrioritizationData.ValueToMission, ValueToMissionPoints([ValueToMission]) AS GetValueToMissionPoints, tbl01PrioritizationData.BMPriority, BMPriorityPoints([BMPriority]) AS GetBMPriorityPoints, tbl01PrioritizationData.CPL, CPLPoints(Left([CPL],1)) AS GetCPLPoints, tbl01PrioritizationData.Influence, InfluencePoints([Influence]) AS GetInfluencePoints, CDbl(Nz([GetDoIPoints]*0.3478+[GetValueToMissionPoints]*0.2609+(Format([GetBMPriorityPoints]*0.1304)),'0.00000')+[GetCPLPoints]*0.1739+[GetInfluencePoints]*0.087) AS PrioritizationValue, tbl01PrioritizationData.BudgetAmount
FROM tbl01PrioritizationData;

Query 2 -- "qry01PrioritizationData--Sorted" (query 1 did not allow me to ORDER BY qry01PrioritizationData.PrioritizationValue DESC)
===============================
SELECT qry01PrioritizationData.TrackingNumber, qry01PrioritizationData.DoI, qry01PrioritizationData.GetDoIPoints, qry01PrioritizationData.ValueToMission, qry01PrioritizationData.GetValueToMissionPoints, qry01PrioritizationData.BMPriority, qry01PrioritizationData.GetBMPriorityPoints, qry01PrioritizationData.CPL, qry01PrioritizationData.GetCPLPoints, qry01PrioritizationData.Influence, qry01PrioritizationData.GetInfluencePoints, qry01PrioritizationData.PrioritizationValue, qry01PrioritizationData.BudgetAmount
FROM qry01PrioritizationData
ORDER BY qry01PrioritizationData.PrioritizationValue DESC;

Query 3 -- "qry01PrioritizationData--SortedandTotaled" (this is the query that utilizes the RunSum after all records have been sorted in query 2; this query is then linked to the form)
===============================
SELECT [qry01PrioritizationData--Sorted].TrackingNumber, [qry01PrioritizationData--Sorted].DoI, [qry01PrioritizationData--Sorted].GetDoIPoints, [qry01PrioritizationData--Sorted].ValueToMission, [qry01PrioritizationData--Sorted].GetValueToMissionPoints, [qry01PrioritizationData--Sorted].BMPriority, [qry01PrioritizationData--Sorted].GetBMPriorityPoints, [qry01PrioritizationData--Sorted].CPL, [qry01PrioritizationData--Sorted].GetCPLPoints, [qry01PrioritizationData--Sorted].Influence, [qry01PrioritizationData--Sorted].GetInfluencePoints, [qry01PrioritizationData--Sorted].PrioritizationValue, [qry01PrioritizationData--Sorted].BudgetAmount, CDbl(qryRunSum("PrioritizationValue",[PrioritizationValue],"BudgetAmount")) AS RunSum, DCount("*","qry01PrioritizationData--Sorted","[PrioritizationValue]=" & [PrioritizationValue]) AS DupsPrioritizationValue
FROM [qry01PrioritizationData--Sorted]
ORDER BY [qry01PrioritizationData--Sorted].PrioritizationValue DESC;


As you see, I use 3 queries to pull the data
1st -- pull the data and determine the values plus do the math
2nd -- pull the calculated values as previously calculated in put in proper order by PrioritizationValue
3rd -- pull previously sorted and calculated values and now run the cumulative running total (RunSum)

Again, I have taken another field out that listed an error message:

As you had helped me on Wed/Thu, I used the following expression to determine where consecutive records have the same value -- this is really helpful for applying conditional formatting.
DupsPrioritizationValue: DCount("*","qry01PrioritizationData--Sorted","[PrioritizationValue]=" & [PrioritizationValue])

Based on your suggestion of e.g. "1 or >1", I put an error message into the query.   This expression looked like this:
ErrorMessage: IIf([DupsPrioritizationValue]>1,"Duplicate Prio-Value = Error in RunSum","None")

I now realize that the ErrorMessage expression slowed down the processing in the form tremendously.  Since I still have the "visual effect" (cond. formatting), I'm okay w/o the error message.

Again, it's running faster now... still if you could have another look at the RunSum module, I appreciate it.   I liked your approach (short and concise) much better than the one I previously used.   Certainly, I only could use it if it's working properly.


Thanks so much,
Tom







0
 

Author Comment

by:TomBock2004
Comment Utility
Oh, I additional question...

In the "qry01PrioritizationData--SortedandTotaled", I want to list an additional Budget Amount total (for validation purposes).

As of now, let's say the records look like this:

PrioritizationValue         Budget      RunSum
0.9845                         $500         $500
0.9712                         $730        $1230
0.9441                           $65        $1295
0.9441                         $200        $1295
0.9299                         $100        $1395

Becaused the 3rd & 4th have the same PrioritizationValue the RunSum did not take the "$200" of the 4th record into consideration.   However, as of know, the conditional formatting highlights both "0.9441" plus both "$1295".   This will help one to go back and make some changes to either 3rd or 4th record so that either Prioritization value will change.

Okay, here's what I'm thinking (to help identify a "problem").

PrioritizationValue           Budget      RunSum
0.9845                           $500         $500
0.9712                           $730        $1230
0.9441                             $65        $1295
0.9441                           $200        $1295
0.9299                           $100        $1395
BLANK                          $1595

The last row (beginning with BLANK) lists the cumulative total of Budget.  In this case, one can easily compare the 2 values and determine that there must be a problem (at least 2 records have identical values).

P.S.   Since this goes a bit beyond my additional question, I have raised the points from 250 to 500.

Thanks!

Tom



 
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Aha, I think I have it - you're sorting ascending and I assumed you were sorting descending. Try this, change this line:
Set rst = db.OpenRecordset("SELECT SUM([" & sumField & "]) As RunningSum FROM [qry01PrioritizationData--Sorted] WHERE [" & idName & "]<" & idValue)

to read:
Set rst = db.OpenRecordset("SELECT SUM([" & sumField & "]) As RunningSum FROM [qry01PrioritizationData--Sorted] WHERE [" & idName & "]>=" & idValue)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Sorry - "you're sorting ascending and I assumed you were sorting descending"

should be:
"you're sorting descending and I assumed you were sorting ascending"

!!

As to your last question, you'll need an additional top level query but it would probably be rather slow. You could list the total budget in the form footer itself - would that be an acceptable solution? If so, just put a textbox in the footer and set the control source to:
=Sum([Budget])

(change the field name as appropriate)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
If the RunSum query works properly btw, you don't need 3 queries, you can ditch the third one and put the RunSum into the second one instead (and get the function to pull its values from the first query).
0
 

Author Comment

by:TomBock2004
Comment Utility
Responses:

A. SumBudget:
Ok, the idea of using a footer is totally acceptable... I show the form in Datasheet view though.   I added a footer on the form but it doesn't seem to work in datasheet view.  

For testing purposes, I switched the view to FORM view and it worked... but that's not what I need.   Do you know a work-around solution so that I could apply your suggestion for datasheet view?

B. 3 Queries:
Yes, that makes sense... I already tried it though and it didn't seem to work.  Reason for that was that I could not apply DESC order to the 1st query.   If I can sort the PrioritizationValue in DESC order, I then could do the RunSum in the 2nd query.   Any suggestion as to why the DESC doesn't work for the 1st query?   As a matter of fact, ASC doesn't work either.... any specified order causes a Parameter windo to pop up.

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
I'm not sure why ordering doesn't work for the first query but you may need to repeat the calculation in the order by clause.

Is the form a subform or a standalone form? If it's a subform, you could display the total in the main form. Otherwise, you could turn it into a continuous form instead of a datasheet form.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
Comment Utility
About 2 queries - if the RunSum query now works, you can just put the RunSum into the second query, and point its parameters to pull the values from the first query. You can also apply the sorting in the second query as well.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:TomBock2004
Comment Utility
shanesuebsahakarn:

Yes, I think I'll change the datasheet view into a continuous form.  

As far as I can see, the DESC sorting of the records has to happen before I call the RunSum function.   That will make sure that cumulative total is added up properly.

Okay, I think you have answered all of my question and I will close this thread now.

Thanks so much again for your prompt responses and help in this matter.

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
> As far as I can see, the DESC sorting of the records has to happen before I call the RunSum function

My RunSum function doesn't need the data to be sorted, unlike the original :-)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
I should clarify that statement - my RunSum function doesn't need to get its data from a sorted query, unlike the original - you can sort the data within the query that calls the function, if you set the function to retrieve data from the first query.
0
 

Author Comment

by:TomBock2004
Comment Utility
Yes, I now have tested it... so you're right, I can ditch the 2nd query and replace it w/ the 3rd.   That's great.

Thanks again,
Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Out of interest, how does the performance compare?
0
 

Author Comment

by:TomBock2004
Comment Utility
I think the most significant performance improvements has been as a result of the deletion of the ErrorMessage expression.

I'm still trying to figure out which runs better... 2 or 3 queries... at this moment, I believe it runs faster w/ the 3 queries (I would have thought the other way around would be faster).

Now, I'm curious myself... based on the info I have provided in this thread, am I doing to much "math" or "calculations" in the db.   I wouldn't think so... but I'm no DBA so I'm not sure.  

At the beginning of this thread, GRayL's feedback was the following: "Why are you trying to mechanize a sort in VBA when Jet can do it via a clause (far more quickly) in your query? Can you not invoke a SORT BY clause to achieve your aim?"

I really have no clue what he mean by that?   How could I use JET and a clause as he suggested?  

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
One of the largest overheads comes from calling the VBA engine. It is possible to incorporate the RunSum function directly into the query as SQL without having to make any calls to the engine at all but this will result in an uneditable query. For example, your second query would read:

SELECT qry01PrioritizationData.TrackingNumber, qry01PrioritizationData.DoI, qry01PrioritizationData.GetDoIPoints, qry01PrioritizationData.ValueToMission, qry01PrioritizationData.GetValueToMissionPoints, qry01PrioritizationData.BMPriority, qry01PrioritizationData.GetBMPriorityPoints, qry01PrioritizationData.CPL, qry01PrioritizationData.GetCPLPoints, qry01PrioritizationData.Influence, qry01PrioritizationData.GetInfluencePoints, qry01PrioritizationData.PrioritizationValue, qry01PrioritizationData.BudgetAmount, (SELECT Sum(A.BudgetAmount) FROM qry01PrioritizationData As A WHERE A.PrioritizationValue>=qry01PrioritizationData.PrioritizationValue) As RunSum
FROM qry01PrioritizationData
ORDER BY qry01PrioritizationData.PrioritizationValue DESC;

Another slow part of the query is the DCount. This can also be included in the query as an SQL subquery (I've also changed the first part to * to select all the fields from qry 1 for readibility):
SELECT *,
(SELECT Sum(A.BudgetAmount) FROM qry01PrioritizationData As A WHERE A.PrioritizationValue>=qry01PrioritizationData.PrioritizationValue) As RunSum,
(SELECT Count(*) FROM qry01PrioritizationData As B WHERE B.PrioritizationValue=qry01PrioritizationData.PrioritizationValue) As DupsPrioritizationValue
FROM qry01PrioritizationData
ORDER BY qry01PrioritizationData.PrioritizationValue DESC;

At this point, we've eliminated the slowest parts of the query at the cost of making it uneditable, but this might not be a problem if the query is only for display. Give that a go and see how it runs.
0
 

Author Comment

by:TomBock2004
Comment Utility
shanesuebsahakarn:

The work you put into this is enomous -- I really really appreciat it!!!

Okay, I tried both queries... to be honest, they appear to be slower than the other version.  And yes, you were absolutely right, the DCOUNT slows it down tremensdously.   For testing purposes, I took the DCOUT out of the query and I saw a BIG improvement.

Unfortunately, with the already deleted ErrorMessage and the DCOUNT (plus the conditional formatting) missing, there's no easy way to identify "problems".   Again, a problem occurs if the PrioritizationValue is identical for consecutive records; hence, the RunSum won't count properly.

I really like the way to "fixed" that by suggesting the DCOUNT and apply cond. formatting for records where the value is ">1".   Well, ensuring that all errors can easily be detected comes at the cost of performance.

Do you think there is an alternative to the DCOUNT... any method will do as long as I can visually display same PrioritizationValues that are equal.    Maybe there's another way beyond the DCOUNT?!?!  

Please let me know if you have an idea - thanks!

Tom



0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Performance improvements interest me :)

You could try this replacement for DCount - it might be faster:

Function SCount(strField As String, strTable As String, strWhere As String) As Long
Dim rst As DAO.Recordset

Set rst=CurrentDb.OpenRecordset("SELECT Count(" & strField & ") As CountOfField FROM " & strTable & " WHERE " & strWhere)
If rst.EOF Then
   sCount=0
Else
   sCount=rst!CountOfField
End If
rst.Close
Set rst=Nothing
End Function

I'm not sure how fast (if at all) this will be, but it might show some improvement as the D functions are notoriously slow.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Just copy the above function into a module and replace DCount with SCount in your query.
0
 

Author Comment

by:TomBock2004
Comment Utility
Okay, I placed the code (as is) into a module... I didn't think I would need to make any modifications, right?!

Then I replaced
DupsPrioritizationValue: DCount("*","qry01PrioritizationData--Sorted","[PrioritizationValue]=" & [PrioritizationValue])

with

DupsPrioritizationValue: SCount("*","qry01PrioritizationData--Sorted","[PrioritizationValue]=" & [PrioritizationValue])

Now, when executing the query, I get the following error: "Undefined function'SCount' in expression."

Did I do something wrong?

Tom
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Odd, you shouldn't have gotten an error. A couple of things to check:

Make sure the module isn't called SCount - Access doesn't like modules to have the same names as functions.
Make sure it's a normal module not a class module (Insert->Module, rather than Class Module).

Does that resolve it?
0
 

Author Comment

by:TomBock2004
Comment Utility
Indeed, I did call the function SCount... now I don't get the error any longer.

However, I now the another error:

RunTime Error '3131'
Syntax error in FROM clause.

The line below is hightlighted in the VBA window.

    Set rst = CurrentDb.OpenRecordset("SELECT Count(" & strField & ") As CountOfField FROM " & strTable & " WHERE " & strWhere)
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
D'oh, I forgot about the - signs again:
    Set rst = CurrentDb.OpenRecordset("SELECT Count(" & strField & ") As CountOfField FROM [" & strTable & "] WHERE " & strWhere)

That should do it. I'm still not sure about potential performance increases, but I will do some tests myself.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
I've done some testing and on my (simple) dataset, it doesn't show any significant performance increases, unfortunately :(

Sadly, I think this might be as fast as it can go, when coupling in the inherent slowness of conditional formatting as well.
0
 

Author Comment

by:TomBock2004
Comment Utility
Wow, I really believe this makes a difference (using SCount vs. DCount).   You mentioned earlier that DCOUNT is know to be notoriously slower... not I'm curious... why is that?    And why does a separate function makes the process faster?

Since you mentioned that you're interested in Performance Analysis, do you care having a copy of the db.   I might help you see the differences yourself.   At this moment, it's relatively small.   Only 1 data table plus the queries and form.  

Well, maybe the latter isn't appropriate... I just joined as a new member to this Exchange... so, I'm not sure if it's not proper to make contact via email and forward files.

Let me know either way!   Anyhow, thanks so much for the SCOUNT function...

Tom


0
 

Author Comment

by:TomBock2004
Comment Utility
Just saw your posting... hmh, maybe it's me, but I thought it was faster... but then again, this is just a "feeling"... I don't have any tools to really measure it accurately.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
I'm glad it does make a difference - I wasn't sure if it would or not (my own performance tests didn't indicate it but it was on a very simple dataset). I've made use of optimisations within the Jet engine itself, letting Jet do the work for me without dipping too heavily into VBA, so it may well be faster when operating on more complex queries. I've seen reports of similar replacement functions being up to twice as fast.

I'll have to decline your offer of the database I'm afraid. Some experts on the site do look at databases, but I don't as I do this professionally and it isn't fair on my paying clients if I look at other people's databases for free (you wouldn't believe the number of people who ask me to help them do things for 500 worthless EE points - we don't get anything for earning these points, they aren't redeemable for anything - that I'd charge hundreds if not thousands of dollars for :-) I know you aren't asking me to do that but I have to stick with the rule - I hope you understand.
0
 

Author Comment

by:TomBock2004
Comment Utility
I absolutely can appreciate your standpoint...

Again, I wasn't trying to take advantage of your help.  Being a new user of this forum, I am already blown away by the support I have received... especially by you so far.

So, I appreciate your honesty and I feel now more comfortable having a better understanding about the rules and policies in this environment.

Final question... do you have a book recommendation about the utilization of JET vs. VBA?   I just don't want to post a question, get an answer, and then move on w/o understanding some more details.   So, I'd like to get as much out of these discussion and leave w/ having learned more things at the end of the day.

Tom


0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Hmm - I think in almost all circumstances that I can think of, using SQL in a query will almost *always* be faster than performing the same function in VBA, except where the SQL itself contains a lot of embedded VBA functions. For example, using Sum() in a query is much, much faster than using DSum.

The reason for this is that Jet contains some very clever and fast optimisations that can make use of indexes in tables and the ultra-secretive Rushmore optimisation - whereas if you write a function to do the same thing in VBA, you basically have to write your own optimisations. There is also the overhead of jumping into the VBA engine, which can make a difference in very large queries.

There are certain situations where using a combination of VBA and SQL can make things significantly faster though. For example, if you wanted to join two tables on the first 4 letters of a field, you could use the Like operator, but it would be slow. This is because Jet cannot use indexes when you use wildcards to match text. If you use the Left function to extract the first four letters from each table and then join on this new field, it will be *significantly* faster. This is an optimisation I made in one of my batch processing queries in a system that calculated values on millions of records. Runtime was cut by over 75%.

Unfortunately, this sort of thing is the kind of knowledge that you pick up from a lot of different sources. The best source of knowledge I can suggest is the Access <your version> Developer's Handbook. This is the bible of Access development and contains a lot of valuable information about VBA, SQL and many other things.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now