speeding up query calculation..

[ai_enjoi via AccessMonster.com]

id like to ask if there are any tips on how to speed up query calculations...?
??
what are some of the practices shud i follow??

i actually have this query

SELECT qry.PID, qry.Month, qry.SumOfSPFDue, qry.[Amount Due], qry.
SumOfReceiptAmount, [Payments1]+[BegBal] AS Payments, (SELECT Sum
(SumOfReceiptAmount) FROM Query5 WHERE (Query5.PID = qry.PID) AND (Query5.
Month <=qry.Month) AND (Query5.SpecificAcct = qry.SpecificAcct)) AS Payments1,
qry.BegBal, qry.SpecificAcct
FROM Query5 AS qry;

it actually takes 5 or 6 seconds for the query to run... right now, the query
contains 230 records...and every month, data will be added,,,,im wondering
how long my databse can keep up with this... any help on this please...thank
you...

 

[Stefan Hoffmann]

hi,

SELECT qry.PID, qry.Month, qry.SumOfSPFDue, qry.[Amount Due], qry.
SumOfReceiptAmount, [Payments1]+[BegBal] AS Payments, (SELECT Sum
(SumOfReceiptAmount) FROM Query5 WHERE (Query5.PID = qry.PID) AND (Query5.
Month <=qry.Month) AND (Query5.SpecificAcct = qry.SpecificAcct)) AS Payments1,
qry.BegBal, qry.SpecificAcct
FROM Query5 AS qry;

First of all check, whether you have indices on PID, Month and
SpecificAcct. SpecificAcct should be a number or a lookup ID.
Try using single and combined indices.

You may use the JETSHOWPLAN option.

http://support.microsoft.com/kb/240434
http://builder.com.com/5102-6388-5064388.html


You need to test a join instead of your subquery, e.g.

SELECT A.*
FROM Q5 A
INNER JOIN Q5 B
ON A.PID = B.PID
WHERE A.Month < B.Month

or

SELECT A.*
FROM Q5 A
INNER JOIN Q5 B
ON A.PID = B.PID
AND A.Month < B.Month

and calculate the aggregate on this query:

SELECT *, Sum()
FROM Query

This should be faster in many cases.


mfG
--> stefan <--

 

[ai_enjoi via AccessMonster.com]

hi...
i tried using the JETSHOWPLAN..can I ask your help in interpreting the
results...
here it is...:

--- Query5 ---

- Inputs to Query -
Table 'tblSPFAccrual'
Table 'tblTransaction'
Table 'tblTransDetail'
Using index 'TransNum'
Having Indexes:
TransNum 2197 entries, 3 pages, 127 values
which has 1 column, fixed
tblTransactiontblTransDetail 2197 entries, 3 pages, 127 values
which has 1 column, fixed
PrimaryKey 2197 entries, 5 pages, 2197 values
which has 1 column, fixed, unique, clustered and/or counter, primary-
key, no-nulls
PID 2197 entries, 8 pages, 339 values
which has 1 column, fixed
IndexNum 2197 entries, 5 pages, 2197 values
which has 1 column, fixed, clustered and/or counter
Table 'tblPayee/Payor'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 343 entries, 1 page, 343 values
which has 1 column, fixed, unique, clustered and/or counter, primary-
key, no-nulls
IndexNum 343 entries, 1 page, 343 values
which has 1 column, fixed, clustered and/or counter
ID1 343 entries, 1 page, 233 values
which has 1 column, fixed
ID 343 entries, 3 pages, 6 values
which has 1 column, fixed
ContactNum 343 entries, 1 page, 100 values
which has 1 column, fixed
- End inputs to Query -

01) Inner Join table 'tblTransaction' to table 'tblTransDetail'
using index 'tblTransDetail!TransNum'
join expression "tblTransaction.TransNum=tblTransDetail.TransNum"
02) Group result of '01)'
03) Group table 'tblSPFAccrual'
04) Inner Join result of '02)' to result of '03)'
using temporary index
join expression "qrygngbayad.PID=qryblayran.PriestPID And qrygngbayad.
Month=qryblayran.Month And qrygngbayad.SpecificAcct=qryblayran.Acct"
05) Inner Join result of '04)' to table 'tblPayee/Payor'
using index 'tblPayee/Payor!PrimaryKey'
join expression "qryblayran.PriestPID=[tblPayee/Payor].PID"
06) Group result of '05)'


thank you....
and yes, the Specificacct is a number field, PID is an index number

hi,

SELECT qry.PID, qry.Month, qry.SumOfSPFDue, qry.[Amount Due], qry.
SumOfReceiptAmount, [Payments1]+[BegBal] AS Payments, (SELECT Sum
(SumOfReceiptAmount) FROM Query5 WHERE (Query5.PID = qry.PID) AND (Query5.
Month <=qry.Month) AND (Query5.SpecificAcct = qry.SpecificAcct)) AS Payments1,
qry.BegBal, qry.SpecificAcct
FROM Query5 AS qry;

First of all check, whether you have indices on PID, Month and
SpecificAcct. SpecificAcct should be a number or a lookup ID.
Try using single and combined indices.

You may use the JETSHOWPLAN option.

http://support.microsoft.com/kb/240434
http://builder.com.com/5102-6388-5064388.html

You need to test a join instead of your subquery, e.g.

SELECT A.*
FROM Q5 A
INNER JOIN Q5 B
ON A.PID = B.PID
WHERE A.Month < B.Month

or

SELECT A.*
FROM Q5 A
INNER JOIN Q5 B
ON A.PID = B.PID
AND A.Month < B.Month

and calculate the aggregate on this query:

SELECT *, Sum()
FROM Query

This should be faster in many cases.

mfG
--> stefan <--

 

[ai_enjoi via AccessMonster.com]

the items from 1 to 6 just stated how my query was designed if im not
mistaken...

hi...
i tried using the JETSHOWPLAN..can I ask your help in interpreting the
results...
here it is...:

--- Query5 ---

- Inputs to Query -
Table 'tblSPFAccrual'
Table 'tblTransaction'
Table 'tblTransDetail'
Using index 'TransNum'
Having Indexes:
TransNum 2197 entries, 3 pages, 127 values
which has 1 column, fixed
tblTransactiontblTransDetail 2197 entries, 3 pages, 127 values
which has 1 column, fixed
PrimaryKey 2197 entries, 5 pages, 2197 values
which has 1 column, fixed, unique, clustered and/or counter, primary-
key, no-nulls
PID 2197 entries, 8 pages, 339 values
which has 1 column, fixed
IndexNum 2197 entries, 5 pages, 2197 values
which has 1 column, fixed, clustered and/or counter
Table 'tblPayee/Payor'
Using index 'PrimaryKey'
Having Indexes:
PrimaryKey 343 entries, 1 page, 343 values
which has 1 column, fixed, unique, clustered and/or counter, primary-
key, no-nulls
IndexNum 343 entries, 1 page, 343 values
which has 1 column, fixed, clustered and/or counter
ID1 343 entries, 1 page, 233 values
which has 1 column, fixed
ID 343 entries, 3 pages, 6 values
which has 1 column, fixed
ContactNum 343 entries, 1 page, 100 values
which has 1 column, fixed
- End inputs to Query -

01) Inner Join table 'tblTransaction' to table 'tblTransDetail'
using index 'tblTransDetail!TransNum'
join expression "tblTransaction.TransNum=tblTransDetail.TransNum"
02) Group result of '01)'
03) Group table 'tblSPFAccrual'
04) Inner Join result of '02)' to result of '03)'
using temporary index
join expression "qrygngbayad.PID=qryblayran.PriestPID And qrygngbayad.
Month=qryblayran.Month And qrygngbayad.SpecificAcct=qryblayran.Acct"
05) Inner Join result of '04)' to table 'tblPayee/Payor'
using index 'tblPayee/Payor!PrimaryKey'
join expression "qryblayran.PriestPID=[tblPayee/Payor].PID"
06) Group result of '05)'

thank you....
and yes, the Specificacct is a number field, PID is an index number

hi,

[quoted text clipped - 38 lines]

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

hi...
i tried using the JETSHOWPLAN..can I ask your help in interpreting the
results...

Please post also the SQL statement of your query.


mfG
--> stefan <--

 

[ai_enjoi via AccessMonster.com]

hello...
i did not change anything on the query

SELECT qry.PID, qry.Month, qry.SumOfSPFDue, qry.[Amount Due], qry.
SumOfReceiptAmount, [Payments1]+[BegBal] AS Payments, (SELECT Sum
(SumOfReceiptAmount) FROM Query5 WHERE (Query5.PID = qry.PID) AND (Query5.
Month <=qry.Month) AND (Query5.SpecificAcct = qry.SpecificAcct)) AS Payments1,

qry.BegBal, qry.SpecificAcct
FROM Query5 AS qry;

 

[Stefan Hoffmann]

hi,

i did not change anything on the query

Not that query, take a closer log at the plan generated by Jet. It is
all about your underlying query5.

Try to optimize it, using proper indices.


mfG
--> stefan <--