One another problem
I have used a formula in the calculated field of a query as -
IIf( [A] <=[b], (IIf([A]<=[C], [A], [C]), (IIf ([b]<=[C], [C], [b]))
the above formula is working properly to give the lowest of [A], [b] and [C] but the problem is that the query is some slow as compared to other query not having such formula. At present, there is only one record in the query to calculate and to show. I fear if what will happen if there will be lots of records.
what to do
can i use - min([A], [b], [C]) - like expression
4 3639 PEB 1,418
Recognized Expert Top Contributor
Strange!
In one record to have slow performance! Expressions like this I use in big tables like 500 000 records and I don't have a big slow down!
Maybe IIF isn't your problem?
:)
Strange!
In one record to have slow performance! Expressions like this I use in big tables like 500 000 records and I don't have a big slow down!
Maybe IIF isn't your problem?
:)
I expressed what i felt. Well your post give me confidence on this type of formula. I want to aske whether your table contians - IIf (IIf (IIf())) - type of formula which is complex.
PEB 1,418
Recognized Expert Top Contributor
See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records! -
SELECT CROSSTAB_NORMS.[0] AS Продукт, arround(IIf([4]="Qty_Piece",Sum([Cadeaux_Commandes01].[Qty])*[CROSSTAB_NORMS].[Qty],Sum([Cadeaux_Commandes01].[Qty])/param_info(show_el_id(16,[1]),0,"COL01N0012")*[CROSSTAB_NORMS].[Qty]),1) AS Result
-
FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1]
-
WHERE (((CROSSTAB_NORMS.[2])="Conditionnement") AND ((CROSSTAB_NORMS.[3])="Atelier") AND ((IIf(([5] Is Null) Or ([5]="All_markets"),True,IIf(Mid([5],1,2)=Mid([n°Cde],1,2),True,False)))=True))
-
GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;
-
See this! This returns result no for more than a few seconds! And in the concerned tables / queries there is 30000 - 40000 records! -
SELECT CROSSTAB_NORMS.[0] AS Продукт, arround(IIf([4]="Qty_Piece",Sum([Cadeaux_Commandes01].[Qty])*[CROSSTAB_NORMS].[Qty],Sum([Cadeaux_Commandes01].[Qty])/param_info(show_el_id(16,[1]),0,"COL01N0012")*[CROSSTAB_NORMS].[Qty]),1) AS Result
-
FROM Cadeaux_Commandes01 INNER JOIN CROSSTAB_NORMS ON Cadeaux_Commandes01.ART = CROSSTAB_NORMS.[1]
-
WHERE (((CROSSTAB_NORMS.[2])="Conditionnement") AND ((CROSSTAB_NORMS.[3])="Atelier") AND ((IIf(([5] Is Null) Or ([5]="All_markets"),True,IIf(Mid([5],1,2)=Mid([n°Cde],1,2),True,False)))=True))
-
GROUP BY CROSSTAB_NORMS.[0], CROSSTAB_NORMS.[1], CROSSTAB_NORMS.[4], CROSSTAB_NORMS.Qty;
-
Oh its amazing. Now I think that time to be taken by MS Access for such type of query is one or two seconds irrespective of the numbers of records. Thanks I will go ahead with this formula. thanks a lot. I will first feed numbers of recods in the table and watch the speed. Thax a lot
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Chumley the Walrus |
last post by:
When i put an explicit value in the sql statement below as
"displaygroup =1 ",
i do see my records (or my response.write test after the recordset
Open line) appear on my dynamic page. But when use code below and
match the 'displaygroup'
field with my dp variable (which was assigned in a prior recordset/sql
block) :
<%dim cm,dp
dp = rs("displaygroup")
|
by: Michelle |
last post by:
Hi all,
I have a query that scans huge table consists of 8 or more millions
records. The funny thing is that if I use the query with local
variable, the query takes more than 1 minutes, whereas if I hard code
the value into the query, it takes about 1 second. Here are the
queries:
WITH VARIABLE:
-------------------
|
by: Ed_No_Spam_Please_Weber |
last post by:
Hello All & Thanks in advance for your help!
Background:
1) tblT_Documents is the primary parent transaction table that has 10
fields and about 250,000 rows
2) There are 9 child tables with each having 3 fields each, their own
PK; the FK back to the parent table; and the unique data for that
table. There is a one to many relation between the parent and each of
the 9 child rows. Each child table has between 100,000 and 300,000
|
by: Ellen Manning |
last post by:
Using A2K. I have a form with a combo box that runs a query and
returns a list of patients. 3 users are sharing this database and the
query runs fine for 2 of the users but not the 3rd. The query takes
2-3 minutes to execute for the 3rd.
Thinking this is more of a pc issue and not a database issue, I
inquired about their pc's and was assured that all 3 pc's are about 1
year old and are identical. They are all using Windows XP...
|
by: innocentchick |
last post by:
I have a database that displays the results of my queries in a subform
within another form. As soon as I modify the query table and save the
changes, the sql code for that query is deleted. For example, if I
change the size of these columns of the query table or even sort my
results using a->z sort, the sql code vanishes when I try to search
again. Anyone have any idea why? - I am assuming this has to do with
my subform. Thanks.
| |
by: john |
last post by:
We have an mdb running on Access 2000 that was originally built in Access
97. Now, after having migrated the mdb to Access 2002 on Citrix (both the
mdb and Access reside on the Citrixserver), the mdb is unacceptably slow in
starting up and moving from form to form. Is this a common problem? Any
ideas to solve it?
Thanks,
Peter
|
by: Bob Darlington |
last post by:
The following query opens slowly the first time it is opened (6-7 seconds),
but then is less than one second for the next random number of openings
before slowing (6-7 seconds) again.
SELECT tTenantDetails.LAN, tTenants.Archive, tTenants.PropNum
FROM tTenants INNER JOIN tTenantDetails ON tTenants.LAN = tTenantDetails.LAN
WHERE (((tTenants.PropNum)=10));
I'm using Access 2002 with XP Pro OS and 2GB RAM.
tTenants and tTenantDetails have...
|
by: Ukar |
last post by:
my query is slow in testing for quick in production.
can any one help
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |