Slow queries

[Michael S. Montoya]

I have a database that has many save queries to give me inventory on hand
balances. A rail car comes in with multiple inventory items. I have a
CarHeader and CarDetail, with the Detail listing the various items and qty
received of each item. I do not have a place for OnHand or TotalSold as I
am using queries to get these values. I opted to go with the query method
to insure data integrity (what if something happend while the sale was being
made and the CarDetail's onhand didn't get updated, or what happens if two
users access the same inventory/CarDetail record at the same time)

The only problem is the database is getting slower and slower as rail cars
are entered and sales are made.

I am going though the many queries and I noticed I had several dlookup() and
dsums() in some queries and have gotten rid of those by adding another query
to the sql.

I also see that I use the nz() function many times in the queries. I
believe I need this for instances where there are no sales on the joined
table, I will get zero instead of a null. as well as a function like
Shipped: Sum(Nz([QtyShip],0)*Abs(nz([IsPosted],0))) (this will only deduct
from inventory only those order that are posted.

Do the NZ() or ABS() functions slow down queries drasticaly? Is there a
work around. In your guys' opinion, should I just write the QtySold back to
the CarDetail table? If I need to write the sale back to the Detail table,
how do I insure data integrity for issues like multiple users posting
different orders at the same time?

Thanks in advance for help on this one.

 

[John Spencer (MVP)]

Shipped: Sum(Nz([QtyShip],0)*Abs(nz([IsPosted],0)))

This could be written more efficiently as
Abs(Sum(QtyShip*IsPosted))

Only do the ABS once.
If that fails then try
Abs(NZ(Sum(QtyShip*IsPosted)))

Also, if you are using SQL server as the backend or MSDE vice Jet it may be more
efficient to use an IIF statement vice the NZ function.

IIF(something is Null, 0, Something)

 

[david epsom dot com dot au]

It's not normally the sum that slows it down, it's the grouping,
but FWIW, SUM ignores nulls: abs(Sum([QtyShip]*[IsPosted])), and
Jet recalculates referenced fields, so the query gets slower
every time you use that calculated value.

(david)