Albert Kallal's QuantityOnHand - Speed Optimisation

[David Mitchell]

I use the above function in queries for a number of forms and reports.
The reports take approx 20 seconds to open. There are only 100
product id's in tblProducts. My concern is that the time will
increase as products are added.

Any suggestions to speed it up?

TIA

David

[XMVP]

"David Mitchell" <da************ **@talk21.com> wrote in message
news:c3******** *************** ***@posting.goo gle.com...

I use the above function in queries for a number of forms and reports.
The reports take approx 20 seconds to open. There are only 100
product id's in tblProducts. My concern is that the time will
increase as products are added.

Any suggestions to speed it up?

Use MVP multi-purpose grease.

[Steve Jorgensen]

I don't see the "above function" you are referring to.

On 15 Sep 2004 15:04:25 -0700, da************* *@talk21.com (David Mitchell)
wrote:

I use the above function in queries for a number of forms and reports.
The reports take approx 20 seconds to open. There are only 100
product id's in tblProducts. My concern is that the time will
increase as products are added.

Any suggestions to speed it up?

TIA

David

[David Mitchell]

erm!?! Perhaps the subject of my posting gives a small clue!

QuantityOnHand! !!!!!!

<g>
Steve Jorgensen <no****@nospam. nospam> wrote in message news:<e5******* *************** **********@4ax. com>...

I don't see the "above function" you are referring to.

On 15 Sep 2004 15:04:25 -0700, da************* *@talk21.com (David Mitchell)
wrote:

I use the above function in queries for a number of forms and reports.
The reports take approx 20 seconds to open. There are only 100
product id's in tblProducts. My concern is that the time will
increase as products are added.

Any suggestions to speed it up?

TIA

David

[Larry Linson]

"David Mitchell" <da************ **@talk21.com> wrote in message
news:c3******** *************** ***@posting.goo gle.com...

erm!?! Perhaps the subject of my posting gives a small clue!

QuantityOnHand! !!!!!!

<g>

It is much better if you make it easier on those who are trying to help you
by including all the necessary information in the body of your post, so they
don't have to make a "connection " between the Subject and the Body.

I haven't used Albert's function, so I can't be of help. It's a rare
newsgroup post that will excite anyone to the point that they will search
out where to obtain some code and then try to puzzle out why it isn't
working for someone else. <WHEW>

Your best best is that Albert will see this and respond. He's a nice,
helpful person.

Larry Linson
Microsoft Access MVP

[Albert D. Kallal]

"David Mitchell" <da************ **@talk21.com> wrote in message
news:c3******** *************** ***@posting.goo gle.com...

I use the above function in queries for a number of forms and reports.
The reports take approx 20 seconds to open. There are only 100
product id's in tblProducts. My concern is that the time will
increase as products are added.

Any suggestions to speed it up?

Ok, lets try some sample data, and see how well ms-access works here.

Lets assume 500 products in the product table. = 500 records

Lets assume that we had 5 stock taking days where we added stock to EACH
product 5 TIMES this year.
(so, each stock item was re-stocked 5 times during the year. Note that the
sample given design allows
for price changes as new stock arrives).

That now means that our Inventory table has 2500 records.

Lets also assume that each Inventory item has 50 orders in the invoices
(order details) table.

That now means our Orders Details table has 50 * 2500 = 125,000 records.

I just wrote some code to fill that database example of mine.

So, we got 125,000 detail records, and 2500 inventory items (or, 500
products
with each 5 stock days...the math here is the same!). So, you could assume
2500 products each with 50 orders (and each product only has had ONE stock
day).

Ok, 3 little loops later...and a bit code to generate the data for me. Ok,
here is the results ON A VERY SLOW 600MHz notebook:

Time to total up all of the in-stock records (that is running sql
statements to total all Inventory additions less all those 125,000 order
details.

That means the sql has to join up all records that belong to each
product..and
sum them.

1.4 seconds! is the TOTAL time it takes on my poor 600mhz computer.

So, if I base a report on the above sql query (and sub-queries), the is a
"slight" delay, but the report loads in less then 2 seconds. Mind you, we
on,ly are dealing with 125,000 detail records being joined here. This is
NOT a large table for ms-access.

The report loads in less then 2 seconds, and the bottom right corner shows
17 pages of output (so, we actually are also including the rendering of the
report here)..

To me, this just shows the BLISTERING speed of the JET engine. I in fact
find it is usually MUCH faster then sql server.

I have posted a copy of the above database with the sample data here:
http://www.attcanada.net/~kallal.msn.../msaccess.html

I am guessing, but likely you are dong some sql joins on fields that ARE NOT
indexed. it is critical that the ProductID fields are indexed. As you can
see, a file that joins 125, 000 detail records to 2500 inventory records
takes less then two seconds on a very old and slow computer. With
only 100 items as you mention, there should be NO delay in generating
that report.

Try running the query that I have in
the above..the 125,000 query should near instant for you....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn

[David Mitchell]

Have just downloaded and will take a look over the weekend. Thank you
for your time and effort.

David

[David Mitchell]

Just had a quick look and cannot see any mention of your function
Quantity on Hand.

You are correct regarding the speed, the query executes in less than
0.8 of a second.

My query utilises your function:- Stock: OnHand([ProductID])

Is it because I am using a function in a query?

[Albert D. Kallal]

Yes, calling a function is expensive in terms of processing.

So, for a form that displays a particular (single) product, the function is
acceptable in performance. However, using a function in a report, or
anything that has to deal with a lot of records, then you cannot afford to
call the function "over and over".
So, just take a look at the query called

qryOnHand.

You can see in the query builder how the function was replaced with some sql
to do the same thing.

I simply put:

TotalAddStock: (select sum(UnitsAdd) from inventory where ProductID =
Products.Produc tID)

and:

TotalSoldStock: (select sum(Quantity) from OrderDetails where ProductID =
Products.Produc tID)

The above two columns thus total stock added, and stock sold.

And, then in the query builder I added:

InStock: (TotalAddSTock-TotalSoldStock)

So, you can still use the query builder here, but I did have to type in some
sql directly. And, really, the above is actaully a LOT simpler then using
code.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn

[Tim Marshall]

I'm assuming David Mitchell is referring to part of Albert's web site?
Could I ask if Albert or David would provide a URL? Or, if it's in
reference to an earlier post on cdma, could one of you tell me the
subject title and I'll check my news server or google it?

Thanks.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto