Hello All,
I hope someone can give me (and other keen access enthusiasts) some
helpful information to explain how to most efficiently filter Queries
& subqueies.
Consider this common simple situation:
1. OrderTable
OrderID - Indexed (PK)
VendorID - Indexed
etc
2. OrderLineTable
OrderLineID - Indexed (PK)
OrderID - Indexed
ProductID - Indexed
Price
Qty
TaxRate
3. VendorTable
VendorID - Indexed (PK)
VendorName etc.
4. ProductTable
ProductID - Indexed (PK)
ProductName etc
OrderLineQuery
OrderLineID
OrderID
ProductID
LineTotal: [Price]*[Qty]*[TaxRate]
OrderTotalQuery
OrderID
OrderTotal: Sum(LineTotal)
Now, If you want to query all Invoices, which include, say
ProductID=1000, and display the [OrderTotal], and perhaps Filter by
Vendor as Optional, and display[VendorName], you need yet another
Query to link the information.
Lets say there is 20,000 invoices, and 60,000 invoice lines, and
ProductID=1000 occurs on 12,000 invoices. Really, nothing too big!
Should I filter on ProductID:
a) OrderLineQuery Field, [OrderLineQuery].[ProductID]?
b) Should I create a Join between [OrderLineQuery].[ProductID] and
[ProductTable].[ProductID], and Filter
[ProductTable].[ProductID]?
Summing up the invoices can take 2 seconds, so it's inefficient to
have the OrderTotalQuery execute more than is required.
I don't want the OrderLineQuery to run too many times, because it
performing calcs on decimal currency, and tax rates - on 60,000 lines,
if unfiltered!
I am so confused, as I seem to be getting lost in the field
manifestation throughout the Subqueries - and when filtering more
fields simultaneously, ie, price, date, vendor, ordertotals etc etc -
The query can take 30 seconds, instead of a fraction of a second.
It's hard to explain.........
Look forward to someone giving me some good advice!
Regards
Elias Farah. 1 1917
Use the JOIN between the 4 tables. Access/JET is very good at choosing the
best query plan, and joins are generally the most efficient approach.
Include both the ProductID and VendorID in your query. You can add the
criteria as needed. In this way, the calculation is needed only for the
selected rows (rather than selecting and calculating all rows and then
culling them). For example, if this is for a form, you could assign the new
query statement (including the joins and criteria) to the RecordSource of
the form. I've generally found that reassigning a SQL statement dynamically
like that is much more efficient and flexible than subqueries or stacked
queries.
Your data structure looks good, though there is no need to index your
foreign keys. If you create relations involving referential integrity,
Access automatically creates indexes to manage the RI.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om... Hello All,
I hope someone can give me (and other keen access enthusiasts) some helpful information to explain how to most efficiently filter Queries & subqueies.
Consider this common simple situation:
1. OrderTable OrderID - Indexed (PK) VendorID - Indexed etc
2. OrderLineTable OrderLineID - Indexed (PK) OrderID - Indexed ProductID - Indexed Price Qty TaxRate
3. VendorTable VendorID - Indexed (PK) VendorName etc.
4. ProductTable ProductID - Indexed (PK) ProductName etc
OrderLineQuery OrderLineID OrderID ProductID LineTotal: [Price]*[Qty]*[TaxRate]
OrderTotalQuery OrderID OrderTotal: Sum(LineTotal)
Now, If you want to query all Invoices, which include, say ProductID=1000, and display the [OrderTotal], and perhaps Filter by Vendor as Optional, and display[VendorName], you need yet another Query to link the information.
Lets say there is 20,000 invoices, and 60,000 invoice lines, and ProductID=1000 occurs on 12,000 invoices. Really, nothing too big!
Should I filter on ProductID:
a) OrderLineQuery Field, [OrderLineQuery].[ProductID]?
b) Should I create a Join between [OrderLineQuery].[ProductID] and [ProductTable].[ProductID], and Filter [ProductTable].[ProductID]?
Summing up the invoices can take 2 seconds, so it's inefficient to have the OrderTotalQuery execute more than is required.
I don't want the OrderLineQuery to run too many times, because it performing calcs on decimal currency, and tax rates - on 60,000 lines, if unfiltered!
I am so confused, as I seem to be getting lost in the field manifestation throughout the Subqueries - and when filtering more fields simultaneously, ie, price, date, vendor, ordertotals etc etc - The query can take 30 seconds, instead of a fraction of a second.
It's hard to explain.........
Look forward to someone giving me some good advice!
Regards Elias Farah. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: N.K. |
last post by:
Hello, I'm trying to find a way to flexibly filter a column in a row
of data read from a file. I will have data type of that column, the
operation to be performed on it (like >, <, <= etc.) and the...
|
by: Alex Ayzin |
last post by:
Hi,
I have a problem that might be easy to solve(possibly, I've just overlooked
an easy solution). Here we go:
I have a dataset with 2 datatables in it. Now, I need to do the following:
if...
|
by: Jason |
last post by:
I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I...
|
by: Richard |
last post by:
Hi,
I have a form that take some time to load due to many comboboxes and at
least 8 subforms.
When I filter or sort the main form I get an error message and then Access
shuts down. They ask if...
|
by: milk-jam |
last post by:
I'm trying to set my datagridview so that the first row will be left blank
and to use it as a filtering filed for the datagridview. Until now I was
using 2 datagridview the upper one with a header...
|
by: Konrad |
last post by:
Hi
Can you point examples in .NET of
filtering (avoiding) displaying web pages with
unwanted content on machine with
ie?
Thanks
Konrad
|
by: |
last post by:
Hello,
Does anyone have an idea on how I can filter the data in the gridview
control that was returned by an sql query?
I have a gridview that works fine when I populate it with data. Now I...
|
by: JUAN ERNESTO FLORES BELTRAN |
last post by:
Hi you all,
I am developping a python application which connects to a database
(postresql) and displays the query results on a treeview. In adittion to
displaying the info i do need to implement...
|
by: Shawn Ramirez |
last post by:
As with most web applications speed is a huge deal to me in my applications.
My customers don't really care if my app is a true 3 tier application or not,
they just want it to be faster then it was...
|
by: Harry Haller |
last post by:
Hello, I want to implement a generic list which will be used to
display 7 columns in a GridView. One should be able to sort, filter
and page each of the 7 columns. Ideally the filter should be...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
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: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
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...
|
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...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |