I want to know what is the code inside D-Family function.
Does it use ADO or DAO recordset to perform ?
I am going to write a vba function that let's me manually create output that SQL couldn't give me without decreasing too much performance due to overwhelming complex query.
so can anyone tell me which way to pull data from table fastest (except SQL) ?
SQL good on pull and process basic data, but when thing goes complex, I think let's VBA handle better, am I wrong ?
7 2166
What are you using this for?
In forms and reports, you can use the SUM/AVG/MAX etc functions to total records in the header/footer sections.
In VBA, you can do something like this (but I'm not sure what the relative performance would be: - Function AddItUp(strFieldName as string, strTableName as string) as Double()
-
dim rs as DAO.recordset
-
dim strSQL as string
-
strSQL = "SELECT SUM(" & strFieldName & ") AS Total FROM " & strTableName
-
set rs = currentdb.openrecordset(strsql, dbfailonerror)
-
AdditUp = rs("Total")
-
-
rs.close
-
set rs = nothing
-
-
' Add solid error handling, handling for 'no records found', etc
-
-
End Function
Also, the domain aggregate functions are not always a bad choice. It depends on HOW you are using them (which is why I asked in my previous post).
They are generally a poor choice in queries... where you may see better performance using sub queries instead.
Without any specifics, the answer is, it depends.
NeoPa 32,556
Expert Mod 16PB
As the Domain Aggregate query functions are inbuilt it's hard for us to know how they're coded and what they're built on.
My own ** Guess ** would be that they use DAO, but that's just a guess.
can we ask mircosoft speakup team bout this ?
I was planning to replace some very complex query structure by 1 UDF vba to simplize those SQL's code and increase performance.
In fact, I have to use statistic function, built-in funcion not enough, so I'll create UDF or write complex query == I'm collecting information to compare whichone will bring me better performance.
What is it that you're trying to do? It might be perfectly doable in SQL
my SQL code's too complex to show detail here,
I'll split it into 3 simple-version parts/proccesses.
firstly I have 3 tables
table1 = {Distr, Cust, BDate, ItemCode, Qty, Price}
table2 = {ItemCode, PriceMin, PriceMax}
table3 = {ItemCode.Value, P_SDate, P_EDate , PromotionName, PrPrice}
Part/Proccess 1 (Qry1) - Generate Current Price of Item -
SELECT ItemCode,
-
sum(iif(BDate between P_SDate and P_EDate, PriceMin-PrPrice, 0)) as PMin,
-
sum(iif(BDate between P_SDate and P_EDate, PriceMax-PrPrice, 0)) as PMax,
-
FROM Table2 Inner Join Table3 On ItemCode = ItemCode.Value
-
Group By ItemCode
-
Part/Proccess 2 (Qry2) - Compare Current Price from Qry1 to Table1 (on November) -
SELECT
-
Distr, Cust, BDate, ItemCode, Qty, Switch(Price < PMin, "Check Bill, Price's too low", Price > PMax, "Check Bill, Price's too high, True, ".") as Status
-
FROM Table1 Inner Join Qry1 ON Table1.ItemCode = Qry1.Itemcode
-
WHERE Month(BDate) = 11
-
ORDER BY Distr, Cust, BDate
-
Part/Proccess 3 (Qry3) - analysis statistics with result from Qry2 -
SELECT Distr, ItemCode, Q.Price as PriceMode, P.Price as PriceWeighted
-
FROM
-
(
-
SELECT Distr, Cust, ItemCode, Price, Count(Price) as Frequency
-
FROM Qry2
-
WHERE Status <> "."
-
GROUP BY Distr, Cust, ItemCode, Price, Month(BDate)
-
WHERE Month(BDate) = 11
-
ORDER BY Distr, Cust
-
HAVING Max(Frequency)
-
) AS Q INNER JOIN
-
(
-
SELECT Distr, Cust, ItemCode, Price, Qty, (Qty/Total-MTD) AS %Weighted
-
FROM
-
(SELECT Distr, ItemCode, Sum(Qty) as Total-MTD
-
FROM Table1
-
WHERE Month(BDate) = 11
-
GROUP BY Distr, ItemCode ) AS A
-
GROUP BY Distr, Cust, ItemCode, Price
-
HAVING Max(Qty/Total-MTD)
-
) AS P ON Q.ItemCode = P.ItemCode AND Q.Cust = P.Cust AND Q.Distr = P.Distr
-
ORDER BY Distr, ItemCode
-
There are a lot of condition that I don't put it in here.
Proccess 2 take about 1 mins to finish.
Proccess 3 I think It'll take 5mins to finish (and will be longer since my KPI_ItemCoverage_Query run over 5mins)
has to mention, my DataBase reached 1.6 million rows.
I plan to use DAO or ADO instead using the complex SQL as qry3..
I think in this situation, VBA can handle better than SQL ==
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Frances Del Rio |
last post by:
can u put JS code inside a frameset? I have this, and it's not working,
even test alerts are ignored, in both browsers.. thank you..
Frances Del Rio
<frameset rows="90,*" border=0...
|
by: qazmlp |
last post by:
There are some blocks of C/C++ code put under
#if 0
#end if
Is there anyway to make the code inside these blocks to get executed
(may be by using some command line options)?
|
by: Bennett Haselton |
last post by:
If I have this code for repeater, it gives a compilation error saying
"The type or namespace name 'MyValue2' could not be found":
<asp:Repeater id="MyList" runat="server">
<ItemTemplate>...
|
by: Edward W. |
last post by:
Sorry, I know the book topic comes up often, but here's what I'm looking for
ASP.NET; (1.1 DNF hopefully)
C# as the main language of the book
Code behind - not code inside
I have ASP.NET...
|
by: Alan Silver |
last post by:
Hello,
Newbie here, so please forgive what is probably a basic question ...
I see a lot of discussion about "code behind", which if I have
understood correctly, means that the script code goes...
|
by: Daniel Andrade |
last post by:
Hello,
How can I write a C# code inside a javascript code, before body tag, as
we wrote in classic asp?
(I have a code file separated with a lot of C# code)
I would like anything like this:
...
|
by: Allen |
last post by:
Hi Everybody,
I would appreciate it if you can give me a hand here.
I want to use the two codes, below, together to do the following
when the "IN" button is clicked (InBtn_Click):-
1-...
|
by: suratna |
last post by:
i have an xslt file. i have no problem writing html code inside it but when i write a mysql select query inside it, it gives error. Is there any way to write php code inside xslt file?
ratna
|
by: mfaisalwarraich |
last post by:
Hi Everyone,
I have some problem with SEO expert while arguing about PHP Code and Head tag.
I have included some PHP Code inside head tag like
<head>
<?php...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |