Query Speed/Indexes

  • Thread starter LDA via AccessMonster.com
  • Start date
L

LDA via AccessMonster.com

hello,

Below is an example of my SQL.
Currenlty it is taking the query over 5 minutes to Load (if it decides to do
such)
I read some other blogs which indicating optimizing the speed of the query by
assigning relationships/indexes.

I have done all that I know to do. Is this query running slow/timing out
because of the number of fields?

How could I improve the speed.

Thanks,

LDA


SELECT DISTINCT Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, Tests.Cost, Priorities.PriceMultiplier,
Duke_OrderInfo.SampleType, Duke_OrderInfo.StatCode, Results.Method,
TestPrices.Price, Results.Report, duke_accounting_testcharge1.singleprice,
IIf([singleprice]=False,[TestPrices].[Price]*[PriceMultiplier]*Count([Param]),
[TestPrices].[Price]*[PriceMultiplier]) AS charge, Results.orderid, Count
(Results.param) AS CountOfparam, Results.EnteredDate
FROM (Duke_Accounting_TestCharge INNER JOIN ((((((Results INNER JOIN Orders
ON Results.OrderID = Orders.OrderID) INNER JOIN Customers ON Orders.
CustomerID = Customers.CustomerID) INNER JOIN SampleDetails ON (Results.
OrderID = SampleDetails.OrderID) AND (Results.SampleNumber = SampleDetails.
SampleNumber) AND (Results.Test = SampleDetails.Test)) INNER JOIN (Tests
INNER JOIN TestPrices ON (Tests.Method = TestPrices.Method) AND (Tests.Test =
TestPrices.Test)) ON (SampleDetails.Test = Tests.Test) AND (SampleDetails.
Matrix = Tests.Matrix)) INNER JOIN Priorities ON SampleDetails.Priority =
Priorities.Priority) INNER JOIN Duke_OrderInfo ON Results.OrderID =
Duke_OrderInfo_OrderID) ON Duke_Accounting_TestCharge.Test = Tests.Test)
INNER JOIN duke_accounting_testcharge1 ON (Priorities.Priority =
duke_accounting_testcharge1.Priority) AND (Duke_Accounting_TestCharge.Test =
duke_accounting_testcharge1.Test)
GROUP BY Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, Tests.Cost, Priorities.PriceMultiplier,
Duke_OrderInfo.SampleType, Duke_OrderInfo.StatCode, Results.Method,
TestPrices.Price, Results.Report, duke_accounting_testcharge1.singleprice,
Results.orderid, Results.EnteredDate
HAVING (((Results.Method) Not Like "V_*") AND ((Results.Report)=True))
ORDER BY Results.SampleNumber;
 
G

GBA

yeah a select distinct, 7 inner joins, and then an order by.....probably
gonna be slow

to trouble shoot I would segment this into several discrete & separate
queries. Using one query to be the source for the next. I find this process
of trouble shooting helpful in identifying the point that most contributes to
time lag...but also in terms of possibly identifying a more efficient method
to establish the needed results.

I am not able to look at this and say that to modify xyz is the
solution....possibly an MVPer has more precise advice...so I would tend to
take it apart in chunks in order to evaluate.

LDA via AccessMonster.com said:
hello,

Below is an example of my SQL.
Currenlty it is taking the query over 5 minutes to Load (if it decides to do
such)
I read some other blogs which indicating optimizing the speed of the query by
assigning relationships/indexes.

I have done all that I know to do. Is this query running slow/timing out
because of the number of fields?

How could I improve the speed.

Thanks,

LDA


SELECT DISTINCT Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, Tests.Cost, Priorities.PriceMultiplier,
Duke_OrderInfo.SampleType, Duke_OrderInfo.StatCode, Results.Method,
TestPrices.Price, Results.Report, duke_accounting_testcharge1.singleprice,
IIf([singleprice]=False,[TestPrices].[Price]*[PriceMultiplier]*Count([Param]),
[TestPrices].[Price]*[PriceMultiplier]) AS charge, Results.orderid, Count
(Results.param) AS CountOfparam, Results.EnteredDate
FROM (Duke_Accounting_TestCharge INNER JOIN ((((((Results INNER JOIN Orders
ON Results.OrderID = Orders.OrderID) INNER JOIN Customers ON Orders.
CustomerID = Customers.CustomerID) INNER JOIN SampleDetails ON (Results.
OrderID = SampleDetails.OrderID) AND (Results.SampleNumber = SampleDetails.
SampleNumber) AND (Results.Test = SampleDetails.Test)) INNER JOIN (Tests
INNER JOIN TestPrices ON (Tests.Method = TestPrices.Method) AND (Tests.Test =
TestPrices.Test)) ON (SampleDetails.Test = Tests.Test) AND (SampleDetails.
Matrix = Tests.Matrix)) INNER JOIN Priorities ON SampleDetails.Priority =
Priorities.Priority) INNER JOIN Duke_OrderInfo ON Results.OrderID =
Duke_OrderInfo_OrderID) ON Duke_Accounting_TestCharge.Test = Tests.Test)
INNER JOIN duke_accounting_testcharge1 ON (Priorities.Priority =
duke_accounting_testcharge1.Priority) AND (Duke_Accounting_TestCharge.Test =
duke_accounting_testcharge1.Test)
GROUP BY Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, Tests.Cost, Priorities.PriceMultiplier,
Duke_OrderInfo.SampleType, Duke_OrderInfo.StatCode, Results.Method,
TestPrices.Price, Results.Report, duke_accounting_testcharge1.singleprice,
Results.orderid, Results.EnteredDate
HAVING (((Results.Method) Not Like "V_*") AND ((Results.Report)=True))
ORDER BY Results.SampleNumber;
Click to expand...
 
J

John Spencer

First move the criteria from the having clause into a WHERE clause.
Second delete the unneeded DISTINCT

SELECT Results.OrderID
, SampleDetails.Matrix
, Customers.CustomerName
, Results.Test
, Results.Param
, SampleDetails.Priority
, Results.SampleNumber
, SampleDetails.AnalysisEmployee
, Tests.Cost
, Priorities.PriceMultiplier
, Duke_OrderInfo.SampleType
, Duke_OrderInfo.StatCode
, Results.Method
, TestPrices.Price
, Results.Report
, duke_accounting_testcharge1.singleprice
, IIf([singleprice]=False,
[TestPrices].[Price]*[PriceMultiplier]*Count([Param]),
[TestPrices].[Price]*[PriceMultiplier]) AS charge
, Results.orderid
, Count(Results.param) AS CountOfparam
, Results.EnteredDate
FROM (Duke_Accounting_TestCharge
INNER JOIN ((((((Results INNER JOIN Orders
ON Results.OrderID = Orders.OrderID) INNER JOIN Customers ON Orders.
CustomerID = Customers.CustomerID) INNER JOIN SampleDetails ON (Results.
OrderID = SampleDetails.OrderID)
AND (Results.SampleNumber = SampleDetails.SampleNumber)
AND (Results.Test = SampleDetails.Test)) INNER JOIN (Tests
INNER JOIN TestPrices ON (Tests.Method = TestPrices.Method) AND
(Tests.Test = TestPrices.Test))
ON (SampleDetails.Test = Tests.Test)
AND (SampleDetails.Matrix = Tests.Matrix)) INNER JOIN Priorities ON
SampleDetails.Priority = Priorities.Priority) INNER JOIN Duke_OrderInfo
ON Results.OrderID = Duke_OrderInfo_OrderID) ON
Duke_Accounting_TestCharge.Test = Tests.Test)
INNER JOIN duke_accounting_testcharge1
ON (Priorities.Priority = duke_accounting_testcharge1.Priority) AND
(Duke_Accounting_TestCharge.Test = duke_accounting_testcharge1.Test)

WHERE (((Results.Method) Not Like "V_*") AND ((Results.Report)=True))

GROUP BY Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, Tests.Cost, Priorities.PriceMultiplier,
Duke_OrderInfo.SampleType, Duke_OrderInfo.StatCode, Results.Method,
TestPrices.Price, Results.Report, duke_accounting_testcharge1.singleprice,
Results.orderid, Results.EnteredDate
ORDER BY Results.SampleNumber;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
L

LDA via AccessMonster.com

Thanks John, That worked fine....

I have another question however. the count of param is counting all of the
parameters. My intentions were to have it count only the params within each
sample number set. Therefore, if that parameter is marked as being False on
the singleprice than Charge would equate to be Count of params * Price
Mulitipler* Pricefor everything within that sample number set.
on the flip, if the parameter was True in the single price than it would be
Cost Time Multiplier within the sample number set.
As it stands now, I am not able to tell the query to only do these equations
within a sample number set.

Any Suggestions?

LDA
John said:
First move the criteria from the having clause into a WHERE clause.
Second delete the unneeded DISTINCT

SELECT Results.OrderID
, SampleDetails.Matrix
, Customers.CustomerName
, Results.Test
, Results.Param
, SampleDetails.Priority
, Results.SampleNumber
, SampleDetails.AnalysisEmployee
, Tests.Cost
, Priorities.PriceMultiplier
, Duke_OrderInfo.SampleType
, Duke_OrderInfo.StatCode
, Results.Method
, TestPrices.Price
, Results.Report
, duke_accounting_testcharge1.singleprice
, IIf([singleprice]=False,
[TestPrices].[Price]*[PriceMultiplier]*Count([Param]),
[TestPrices].[Price]*[PriceMultiplier]) AS charge
, Results.orderid
, Count(Results.param) AS CountOfparam
, Results.EnteredDate
FROM (Duke_Accounting_TestCharge
INNER JOIN ((((((Results INNER JOIN Orders
ON Results.OrderID = Orders.OrderID) INNER JOIN Customers ON Orders.
CustomerID = Customers.CustomerID) INNER JOIN SampleDetails ON (Results.
OrderID = SampleDetails.OrderID)
AND (Results.SampleNumber = SampleDetails.SampleNumber)
AND (Results.Test = SampleDetails.Test)) INNER JOIN (Tests
INNER JOIN TestPrices ON (Tests.Method = TestPrices.Method) AND
(Tests.Test = TestPrices.Test))
ON (SampleDetails.Test = Tests.Test)
AND (SampleDetails.Matrix = Tests.Matrix)) INNER JOIN Priorities ON
SampleDetails.Priority = Priorities.Priority) INNER JOIN Duke_OrderInfo
ON Results.OrderID = Duke_OrderInfo_OrderID) ON
Duke_Accounting_TestCharge.Test = Tests.Test)
INNER JOIN duke_accounting_testcharge1
ON (Priorities.Priority = duke_accounting_testcharge1.Priority) AND
(Duke_Accounting_TestCharge.Test = duke_accounting_testcharge1.Test)

WHERE (((Results.Method) Not Like "V_*") AND ((Results.Report)=True))

GROUP BY Results.OrderID, SampleDetails.Matrix, Customers.CustomerName,
Results.Test, Results.Param, SampleDetails.Priority, Results.SampleNumber,
SampleDetails.AnalysisEmployee, Tests.Cost, Priorities.PriceMultiplier,
Duke_OrderInfo.SampleType, Duke_OrderInfo.StatCode, Results.Method,
TestPrices.Price, Results.Report, duke_accounting_testcharge1.singleprice,
Results.orderid, Results.EnteredDate
ORDER BY Results.SampleNumber;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
hello,
Click to expand...
[quoted text clipped - 12 lines]
LDA
Click to expand...
Click to expand...
 
J

John Spencer MVP

I'm sorry, but I'm lost on what you are attempting to do. So I can't advise
you on a soltuion.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
L

lda via AccessMonster.com

Thanks for your honesty.
Maybe it wil help If I try to explain my self more.

I have four main fields. order id, sample number, Test, Param., (There are
about 4 other fields, that helps aid in distinction)

I am trying to count the number of parameters for each Test.

For example

OrderID SampleNumber, Test, Param (Result)

j09 2001 abc joy
74ml
j09 2001 abc hope
33ml
j09 2001 abc faith
22
j09 2001 xyz bug
11

therefore, the count of param in sample 2001 should be 3 for test abc and 1
for test xyz.

They way i have it is counting all param for everything within the orderid

Any suggestions

lda
I'm sorry, but I'm lost on what you are attempting to do. So I can't advise
you on a soltuion.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, That worked fine....
Click to expand...
[quoted text clipped - 11 lines]
LDA
Click to expand...
Click to expand...
 
M

misschanda via AccessMonster.com

OrderID SampleNumber, Test, Param (Result)

j09 2001 abc joy 74ml
j09 2001 abc hope .33ml
j09 2001 abc faith 22ml
j09 2001 xyz bug 11ml
Click to expand...
Thanks for your honesty.
Maybe it wil help If I try to explain my self more.

I have four main fields. order id, sample number, Test, Param., (There are
about 4 other fields, that helps aid in distinction)

I am trying to count the number of parameters for each Test.

For example

OrderID SampleNumber, Test, Param (Result)

j09 2001 abc joy 74ml
j09 2001 abc hope .33ml
j09 2001 abc faith 22ml
j09 2001 xyz bug 11ml

therefore, the count of param in sample 2001 should be 3 for test abc and 1
for test xyz.

They way i have it is counting all param for everything within the orderid

Any suggestions

lda
I'm sorry, but I'm lost on what you are attempting to do. So I can't advise
you on a soltuion.
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Format Help 0
query/transform/crosstab?? 0
cross tab query help 3

Top