Debugging dynamic prices

[antgel]

Hi all,

I have a system that calulates the price of an invoice dynamically - when
it's viewed it should add up the price for each line and total it. Easy
enough, you might think... But it's _crawling_ - I've started debugging it
but could really do with some help, as I'm hitting a brick wall.

Here's the relevant parts of a query called invoiceDetail (there is more to
it, but this is the slow bit:

SELECT invoice.freeText,
DSum ("[linePrice]","[invoiceLineDetail]","invoice.id = " & invoice.id) AS
nett, IIf(contact.country="UNITED
KINGDOM",[nett]*0.175+nz([carriage]*0.175),0) AS vat,
[nett]+[vat]+nz([carriage]) AS gross,
invoice.customerId
FROM contact INNER JOIN invoice ON contact.contactid = invoice.customerId;

As you can see, the DSum references a query called invoiceLineDetail. I
shan't reproduce that query at this point, as it is very loooong.

My test case involves a 3-line invoice, number 1154. Suffice it to say that
if I run this query:

SELECT invoiceLineDetail.*
FROM invoiceLineDetail
where invoice.id=1154

I get a 3-line result in under a second. So I don't think the length of the
invoiceLineDetail query is the issue.

I think the problem must be DSum() doing things in an inefficient manner. I
wanted to try getting net from a subquery, rather than DSum(), kind of like:

SELECT invoice.freeText,
nett,
IIf(contact.country="UNITED KINGDOM",[nett]*0.175+nz([carriage]*0.175),0) AS
vat, [nett]+[vat]+nz([carriage]) AS gross,
invoice.customerId
FROM contact INNER JOIN invoice ON contact.contactid = invoice.customerId
WHERE nett IN
(SELECT sum(lineprice) as nett
FROM invoiceLineDetail
where invoice.id=1154);

Two problems when I try this:
1. I'm asked for the value of nett - I want this to come from the subquery.
2. Even when that works, what about for different values of invoice.id?

Is there a better way of doing this?

Antony

 

[John Viescas]

If you don't need the query to be updatable, you can do it like this:

SELECT invoice.freeText,
DSum ("[linePrice]","[invoiceLineDetail]","invoice.id = " & invoice.id) AS
nett, IIf(contact.country="UNITED
KINGDOM",[nett]*0.175+nz([carriage]*0.175),0) AS vat,
[nett]+[vat]+nz([carriage]) AS gross,
invoice.customerId
FROM (contact
INNER JOIN invoice
ON contact.contactid = invoice.customerId)
INNER JOIN
(SELECT invoice.id, Sum(LinePrice) As nett
FROM invoiceLineDetail
GROUP BY invoice.id) As NetSum
ON Invoice.id = NetSum.id;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)