Has anyone toiled with creating/using alternate domain aggregate
functions? I have been messing with that a little. The one
recordsource I have been working indicates I get 20 to 40% savings in
processing time. I have not tried this with different recordsets yet.
I am curious what others might think.
What might be interesting if you could create temporary indexes. I did
try sorting the recordset on find first. It ran slower. I have not
tried to filter the recordset as I did on the dsum and dcount function.
Also, I am unsure if opening the recordset as a dbOpenSnapshot the best
solution either.
Option Compare Database
Option Explicit
Public Function mFirst(Field As String, domain As String, criteria As
String) As String
'Replacement for dfirst
Dim MyRS As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
Let MyRS.Sort = Field
MyRS.FindFirst (criteria)
mFirst = MyRS.Fields(Fie ld)
If MyRS.NoMatch = True Then mFirst = "No Match"
End Function
Public Function mCount(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dcount
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move Last
mCount = rsFiltered.Reco rdCount
End Function
Public Function mSum(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dsum
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move First
While rsFiltered.EOF = False
'DoCmd.Echo True, rsFiltered.Perc entPosition
mSum = mSum + rsFiltered.Fiel ds(Field).Value
rsFiltered.Move Next
Wend
End Function 5 8464
Look at what Trevor Best has made long time ago.
Download from http://easyweb.easynet.co.uk/~trevor/AccFAQ/
==downloads ==domain function replacement
Arno R
"Dean" <no*****@coveya ccounting.comsc hreef in bericht news:11******** *************@i 42g2000cwa.goog legroups.com...
Has anyone toiled with creating/using alternate domain aggregate
functions? I have been messing with that a little. The one
recordsource I have been working indicates I get 20 to 40% savings in
processing time. I have not tried this with different recordsets yet.
I am curious what others might think.
What might be interesting if you could create temporary indexes. I did
try sorting the recordset on find first. It ran slower. I have not
tried to filter the recordset as I did on the dsum and dcount function.
Also, I am unsure if opening the recordset as a dbOpenSnapshot the best
solution either.
Option Compare Database
Option Explicit
Public Function mFirst(Field As String, domain As String, criteria As
String) As String
'Replacement for dfirst
Dim MyRS As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
Let MyRS.Sort = Field
MyRS.FindFirst (criteria)
mFirst = MyRS.Fields(Fie ld)
If MyRS.NoMatch = True Then mFirst = "No Match"
End Function
Public Function mCount(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dcount
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move Last
mCount = rsFiltered.Reco rdCount
End Function
Public Function mSum(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dsum
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenR ecordset(domain , dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecord set
rsFiltered.Move First
While rsFiltered.EOF = False
'DoCmd.Echo True, rsFiltered.Perc entPosition
mSum = mSum + rsFiltered.Fiel ds(Field).Value
rsFiltered.Move Next
Wend
End Function
Arno R wrote:
Look at what Trevor Best has made long time ago.
Download from http://easyweb.easynet.co.uk/~trevor/AccFAQ/
==downloads ==domain function replacement
Arno R
Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel... pi********@hotm ail.com wrote:
Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...
Actually, Trevor took a different approach: He creates a query def
each time. I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a replacement
for d functions on attached tables, not intended as replacement all
together. Based on these comments, I am assuming the built-in
functions run faster on native Access record sets rather than linked
tables.
I have I also found http://allenbrowne.com/ser-42.html which is more
similar to what I did which was utilize the existing record set. I am
going to some testing with this code.
Dean wrote:
pi********@hotm ail.com wrote:
Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...
Actually, Trevor took a different approach: He creates a query def
each time. I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a replacement
for d functions on attached tables, not intended as replacement all
together. Based on these comments, I am assuming the built-in
functions run faster on native Access record sets rather than linked
tables.
I have I also found http://allenbrowne.com/ser-42.html which is more
similar to what I did which was utilize the existing record set. I am
going to some testing with this code.
I disagree with your approach though. If you want an efficient way to "look up"
a record open a Recordset that contains only that record rather than opening the
whole domain and then do a FindFirst on the Recordset. If the criteria will
return more than one row and you want t6he first match then use a TOP 1 clause
with an appropriate ORDER BY.
If you want to do a count of a Recordset then use a SQL statement that uses the
Count() function rather that opening the entire domain and then moving to the
end and then using the RecordSet.Count .
It is in creating the most efficient query that you will reap the most benefits.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
"Dean" <no*****@coveya ccounting.comwr ote in
news:11******** *************@m 73g2000cwd.goog legroups.com:
Actually, Trevor took a different approach: He creates a query
def each time.
Huh? As far as I can tell his t functions do nothing of the sort.
I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a
replacement for d functions on attached tables, not intended as
replacement all together. Based on these comments, I am assuming
the built-in functions run faster on native Access record sets
rather than linked tables.
If I'm not mistaken, the t functions were indeed created to be
faster than the builtin domain aggregate functions on linked tables.
But I believe that problem was fixed in A2K or later so that the
domain aggregate functions were no longer slower on linked tables,
so Trevo'rs functions no longer had an advantage over them.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ariel Jakobovits |
last post by:
I have a table with 2 primary keys, one is a foreign key, the other is
produced by a sequence.
I want to SELECT query for one record that has a list of the
sequence-produced values for all records with a given foreign-key
value, essentially:
Create table table1 {
fk_id number,
seq_id number
|
by: Steven An |
last post by:
Howdy,
I need to write an update query with multiple aggregate functions.
Here is an example:
UPDATE t
SET
t.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),
t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )
FROM dbo.test t
|
by: Job Lot |
last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values:
Date Amount Int Balance
1/1/2004 5000.00 50.00 5050.00
1/1/2004 4000.00 40.00 4040.00
1/2/2004 1000.00 10.00 1010.00
1/2/2204 2000.00 20.00 2020.00
1/3/2004 1500.00 15.00 1515.00
I want my...
|
by: jc |
last post by:
Hi.
Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I write
my own variation of a such a function. If I can appreciate how to do
this,
then I hopefully I can write a MEDIAN(COLUMN_NAME) type function or a
more general function like a 10% percentile function with syntax such
as
PERCENTILE(COLUMN_NAME,25).
Regards JC......
|
by: neb |
last post by:
Dear member of the forum,
Ms access has built-in aggregate function like:
-Sum, Max, First, Avg, ...
Is it possible to build user-defined aggregate?
(if you have any clue, do not hesitate to post)
*I'm running a total query, of the form
| |
by: David Garamond |
last post by:
What do people think of adding some more aggregate functions. These are
the ones that MySQL has and PG doesn't:
- STD/STDDEV
- VARIANCE
- BIT_OR
- BIT_AND
- GROUP_CONCAT (for strings, added in MySQL 4.x)
Particularly, I find GROUP_CONCAT practical to quickly display 1-many
|
by: jefftyzzer |
last post by:
The current issue of "Oracle Magazine" has an article on creating
custom aggregate functions, which naturally got me thinking about how
to do this in DB2.
I found some articles on creating custom aggregate functions in DB2,
written close to three years ago by Knut Stolze:
...
|
by: BillCo |
last post by:
just wasted a long time figuring out this and I figure if I post it
might save someone some pain!
Jet (DAO) will allow you to to use nested aggregate functions like
building blocks, e.g.:
SELECT
A,
sum(B) as Answer1,
Answer1 * 2 as DoubleAnswer
|
by: BillCo |
last post by:
I just wasted a long time figuring out this and I figure if I post it
might save someone some pain!
Jet (DAO) will allow you to to use nested aggregate functions like
building blocks, e.g.:
SELECT
A,
sum(B) as Answer1,
Answer1 * 2 as DoubleAnswer
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |