database optimizing

[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.

 

[Ron Weiner]

Michael

It has been my experience that most often it is better to think about the
way you arrive at the solution for a process than the details of the process
itself. For example think about the method you are using to get the balance
on hand rather than the functions and statements in your code. Once you are
sure that your overall methodology is as efficient as is possible, then you
can start to look at the code and query tweaks to make it even better. I
have found methodology changes often produce orders of magnitude increases,
where code tweaks produce percentage increases. Look where you can get the
biggest bang FIRST.

I have never done an inventory database, but I think that:

Qty on hand = qty on hand at last physical inventory + Sum(qty received
since last physical inventory) - Sum(qty dispensed since last physical
inventory)

is an efficient way to compute the current on hand qty if inventories were
taken on a regular basis, as there would be few rows for the received and
dispensed queries to sum.

In practice I'd probably use three queries to do this. A query to sum qty
received, a query to sum qty dispensed and another one to put them all
together. It final one might look like:

SELECT ItemID, [Qty]+[qryRcvd].[SumRcvd]-[qrySold].[SumSold] AS Ballance
FROM (tblCarDetail INNER JOIN qryRcvd ON tblCarDetail.ItemID =
qryRcvd.ItemID)
INNER JOIN qrySold ON tblCarDetail.ItemID = qrySold.ItemID
WHERE tblCarDetail.LastInvDate>=#[LastInvDate]#;

Now I might be all wet here, but I think this would be accurate, and since
it doesn't use any procedural code (loops, dsum's, or NZ's) it should
execute pretty quickly. In any case if I were you, I'd look at my process
first, before tweaking the code itself.

Ron W

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.