By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Albert Kallal's QuantityOnHand - Speed Optimisation

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a

"David Mitchell" <da**************@talk21.com> wrote in message
news:c3**************************@posting.google.c om...
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.

Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a

"David Mitchell" <da**************@talk21.com> wrote in message
news:c3**************************@posting.google.c om...
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
Nov 13 '05 #5

P: n/a
"David Mitchell" <da**************@talk21.com> wrote in message
news:c3**************************@posting.google.c om...
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

Nov 13 '05 #6

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

David
Nov 13 '05 #7

P: n/a
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?
Nov 13 '05 #8

P: n/a
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.ProductID)

and:

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

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
Nov 13 '05 #9

P: n/a
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
Nov 13 '05 #10

P: n/a
"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:ci**********@coranto.ucs.mun.ca...
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?


Actually, I am not sure what Quantity on hand, and I actually not sure even
what post!!!

I can't even find the reference on a newsgroup google search. However, I do
remember this issue. Just can't remember if I sent the person a piece of
code....or talked about "how" to do this!

At any rate, if my memory serves me, I simply suggested, or posted some
simple code that took the productID, and calculated the Quantity on hand.
This is done simply by adding up all inventory additions and then
subtracting any product in the "invoice details" to get the actual quantity
on hand. This is great approach to inventory, as then you don't have to
write ONE LINE of code to update the inventory stuff...you just calculate it
on the fly. This means you invoice form can add, or delete invoice details,
and no special "update" code is needed to be run to try and maintain the
actually totals of the inventory. (so, you just delete the invoice, or if
you want you can edit, or delete part of a order, and the quantity on hand
magically updates with NO code!!). It also means the totals are always
correct, since no "code" runs to try and update the quantity on hand!

The function I suggested to use is something like:
Public Function MyQuanityOnHand(ProductID as vID) as long

' code goes here to build two sql statements (shoved into reocrdsets) to
calculate
' results

' build query to sum all orders with ProductID
' get result

'build query to sum all inventory additions for ProductID
'get result

Then, those results are something like:
MyQuanityOnHand = ( Sum of Inventory of product - Sum of Product Sold)

end function

Of course, we all know that opening, and closing a reocrdset EACH time is
costly. The original poster was using this "function" in a report to display
the inventory for a list of products. You see the performance numbers here:

The real point here is that calling a function in a query, or in a report
for "each" record is very expensive. As you can see the numbers:

Original post with a 100 records, and a function: 20 seconds.

My example with 2500 products, and a 125,000 detail records: less then 1
second!!

In other words, even a file with 125,000 order details from invoices is
absolute NOTHING for ms-access to process in "real time", and thus we NEVER
actually store the "quantity on hand" in the database..but in fact,
calculate it on the fly.

I did post an example zipped file with the 125,000 "order details" records,
and 2500 inventory records.

As mentioned, to calculate all of the results and "join" those 125,000
records to 2500 products takes less then 1 second on most computers.

Fact is, if you limited the users search to a particular product then were
to display the quantity on hand, you can see that is approach scales quite
well. Since we would be working with just one record, not the 2500 possible
product records, then I see no reason why a product with even 1000 orders
against it would not be instant.

You can find the data, and the sample query here (right at the bottom):

http://www.attcanada.net/~kallal.msn.../msaccess.html

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

P: n/a
Albert, it is little wonder you cannot find the function as I have
wrongly attributed it to you when in fact it came from Allen Brownes'
website.

To both Allen and Albert, my humblest apologies. I regularly utilise
your websites and thank both of you for the help I have found within
them.

For those looking, the url for the function is :-

http://members.iinet.net.au/~allenbr...Inventory.html

David
Nov 13 '05 #12

P: n/a
"David Mitchell" <da**************@talk21.com> wrote in message
news:c3*************************@posting.google.co m...
Albert, it is little wonder you cannot find the function as I have
wrongly attributed it to you when in fact it came from Allen Brownes'
website.

To both Allen and Albert, my humblest apologies. I regularly utilise
your websites and thank both of you for the help I have found within
them.

For those looking, the url for the function is :-

http://members.iinet.net.au/~allenbr...Inventory.html

David


Actually, this is kind of funny! I felt sorry giving your bad advice on
using a function that don't perform too well, so tested a query to see how
fast it would go! (in place of that non existent function, and that non
existent advice I gave you!)

I am actually quite happy this happened, as now I have shown how fast JET
can be for this kind of solution....

I had the query and the tables made, and have suggested the solution in the
past. However, I had never tried the solution with so much data.

I thought you might be referring to me, and if you start reading the
following article of mine...I talking about this approach here:

http://www.attcanada.net/~kallal.msn...Sql/Pick6.html

Anyway...no harm done...and I am glad you asked, since now I know how well
this can work!!!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn


Nov 13 '05 #13

P: n/a
Albert D. Kallal wrote:
"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:ci**********@coranto.ucs.mun.ca...
http://www.attcanada.net/~kallal.msn.../msaccess.html


Thanks, Albert, I'll look into this.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.