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

In a form's event: Execute SQL 450 times OR excute 1 SQL?

P: n/a
NB
Hi

Some of my recent posts have been kind of monologues.
I know that they are challenging and nobody may have any idea about
them.

However, I keep posting, take this NG as a record keeper for me, and
by chance maybe someone can suggest something.

This is what I'm doing:

In a form that simulates the floor plan of a zone in a warehouse, I
have 450 textboxes representing 450 pallet spaces in that zone.

Upon opening the form, I need to identify which spaces are occupied
and which are not (to format the respective textboxes background)

The goal: users need to have a bird's eyes view of warehouse plan
occupancy.

To accomplish that goal:

Option 1:
- open a recordset of all occupied space in that zone
- Loop through and compare each of the 450 textboxes with locations in
the recordset to identify whether the space is occupied, then
respectively format the textbox

Option 2:
- Loop through each of the 450 textboxes
- Within the loop open a recordset with an exact location (pulling
only 1 - or no - record) then depending on whether recordcount = 0 or
0, respectively format the textbox.

I'm not sure which approach is more efficient.
Or any other algorythm to achieve the same goal?

Thanks for suggestion (if any)

NB
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Is it not possible to treat groups of these text boxes as records in a
table? Without knowing the setup of the form, is it possible to treat
this like a grid? Imagine each record as a row or aisle in the zone, and
each field as a pile/shelf/whatever in the row. This form could then be
a datasheet or continuous form and remove the need for loops (although
it may make formating the background tougher).

If this is not possible, then I would be inclined to run one query and
loop through the controls. A few reasons why...

- You're going to do a loop of 450 in either case
- Looping through fields in a recordset or controls on a form take
roughly the same amount of time.
- You still need to include the textbox in the loop in order to format it.
- If you are on a network, or even a local front-end/back-end setup,
then running 450 queries may become resource intensive.

But that's just my opinion.

Mike Storr
www.veraccess.com
NB wrote:
Hi

Some of my recent posts have been kind of monologues.
I know that they are challenging and nobody may have any idea about
them.

However, I keep posting, take this NG as a record keeper for me, and
by chance maybe someone can suggest something.

This is what I'm doing:

In a form that simulates the floor plan of a zone in a warehouse, I
have 450 textboxes representing 450 pallet spaces in that zone.

Upon opening the form, I need to identify which spaces are occupied
and which are not (to format the respective textboxes background)

The goal: users need to have a bird's eyes view of warehouse plan
occupancy.

To accomplish that goal:

Option 1:
- open a recordset of all occupied space in that zone
- Loop through and compare each of the 450 textboxes with locations in
the recordset to identify whether the space is occupied, then
respectively format the textbox

Option 2:
- Loop through each of the 450 textboxes
- Within the loop open a recordset with an exact location (pulling
only 1 - or no - record) then depending on whether recordcount = 0 or
0, respectively format the textbox.


I'm not sure which approach is more efficient.
Or any other algorythm to achieve the same goal?

Thanks for suggestion (if any)

NB

Nov 12 '05 #2

P: n/a
ni******@lycos.com (NB) wrote in message news:<5c*************************@posting.google.c om>...
Hi

Some of my recent posts have been kind of monologues.
I know that they are challenging and nobody may have any idea about
them.

However, I keep posting, take this NG as a record keeper for me, and
by chance maybe someone can suggest something.

This is what I'm doing:

In a form that simulates the floor plan of a zone in a warehouse, I
have 450 textboxes representing 450 pallet spaces in that zone.

Upon opening the form, I need to identify which spaces are occupied
and which are not (to format the respective textboxes background)

The goal: users need to have a bird's eyes view of warehouse plan
occupancy.

To accomplish that goal:

Option 1:
- open a recordset of all occupied space in that zone
- Loop through and compare each of the 450 textboxes with locations in
the recordset to identify whether the space is occupied, then
respectively format the textbox
This one. Single round trip to server to retrieve (presumably) fairly
small recordset. All subsequent manipulation done client-side.
Option 2:
- Loop through each of the 450 textboxes
- Within the loop open a recordset with an exact location (pulling
only 1 - or no - record) then depending on whether recordcount = 0 or
0, respectively format the textbox.


450 round trips to server? No thanks.

My 0.02p

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
Nov 12 '05 #3

P: n/a
ni******@lycos.com (NB) wrote in news:5cbf60ef.0402251914.9bf7129
@posting.google.com:
Hi

Some of my recent posts have been kind of monologues.
I know that they are challenging and nobody may have any idea about
them.

However, I keep posting, take this NG as a record keeper for me, and
by chance maybe someone can suggest something.

This is what I'm doing:

In a form that simulates the floor plan of a zone in a warehouse, I
have 450 textboxes representing 450 pallet spaces in that zone.

Upon opening the form, I need to identify which spaces are occupied
and which are not (to format the respective textboxes background)

The goal: users need to have a bird's eyes view of warehouse plan
occupancy.

To accomplish that goal:

Option 1:
- open a recordset of all occupied space in that zone
- Loop through and compare each of the 450 textboxes with locations in
the recordset to identify whether the space is occupied, then
respectively format the textbox

Option 2:
- Loop through each of the 450 textboxes
- Within the loop open a recordset with an exact location (pulling
only 1 - or no - record) then depending on whether recordcount = 0 or
0, respectively format the textbox.

I'm not sure which approach is more efficient.
Or any other algorythm to achieve the same goal?

Thanks for suggestion (if any)

NB


I would probably experiment with two decimal(28,0) fields which would give
me 448 bits (well, maybe 3 such fields). I'd try setting a specific bit up
for each record where occupied is true. Then I'd sum the fields to give me
one record with 3 fields each of 28 bytes, whose bits corresponded to the
occupancy state of the locations. (I'd have to experiment further with this
because I do not know what the limitations of summing decimal fields are).
The form's text box occupancy state could then be set to a simple alogrithm
whcih would examine the bit state of the summed fields.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #4

P: n/a
NB
> Is it not possible to treat groups of these text boxes as records in a
table?
This is not possible as I need to draw a floor plan: the textboxes
position must be the correct physical layout of the real warehouse
If this is not possible, then I would be inclined to run one query and
loop through the controls. A few reasons why...

- You're going to do a loop of 450 in either case
- Looping through fields in a recordset or controls on a form take
roughly the same amount of time.
- You still need to include the textbox in the loop in order to format it.
- If you are on a network, or even a local front-end/back-end setup,
then running 450 queries may become resource intensive.


The question is: if I execute only 1 SQL, I'll have to use findfirst
when checking the occupancies - and that's my concern.

If I run 450 SQL, each of them will draw only 1 record at a time.

The performance of this 450 SQL approach is OK (about 1 second) on my
development PC whic is also the file server for the backend. Maybe
I'll try to run it from another PC.

NB
Nov 12 '05 #5

P: n/a
NB
> > Option 1:
- open a recordset of all occupied space in that zone
- Loop through and compare each of the 450 textboxes with locations in
the recordset to identify whether the space is occupied, then
respectively format the textbox


This one. Single round trip to server to retrieve (presumably) fairly
small recordset. All subsequent manipulation done client-side.

As mentioned in another post in the thread: my concern is performance
when using findfirst against this recordset.

Option 2:
- Loop through each of the 450 textboxes
- Within the loop open a recordset with an exact location (pulling
only 1 - or no - record) then depending on whether recordcount = 0 or
0, respectively format the textbox.


450 round trips to server? No thanks.


NB
Nov 12 '05 #6

P: n/a
Though I did not clearly understand your suggestion, I'll explain my
data structure and algorithm for tracking warehouse spaces.

The goal: you need to keep track of where incoming stocks are kept, and
from where outgoing stocks are taken

The issue is: even after a certain batch has been sent out, you still
need to keep a record of the place you had kept them. And there must be
a way to tell that these stocks are not here anymore, they have been
sent out.
This is the data structure, surprisingly simple:

tblWHLoc stores the indexed spaces (WHLocID, ...)

tblInDetailPallet stores data on how incoming goods are arranged in
pallets (inDetailPalletID, ....)
tblOutDetailPallet stores data on how outgoing goods are arranged in
pallets (outDetailPalletID, ....)

tblWHLocProduct (WHLocID, inDetailPalletID, ... outDetailPalletID) Note
that outDetailPalletID is not a primary key, it's just an attribute.
Now how it works:

Upon receiving some goods, quantity & arrangement details will be keyed
in (tblInDetailPallet)

After that, goods from this batch will be allocated to available spaces
in the warehouse. Those WHLocID obviously are those with

tblWHLocProduct.inDetailpalletID=NULL or
(tblWHLocProduct.inDetailpalletID= NOT NULL and
tblWHLocProduct.outDetailpalletID= NOT NULL)
For sending out goods, quantity & arrangement details will be keyed in
(tblOutDetailPallet)
After that, you will specify where (in the warehouse) goods for this
batch come from. Records in tblWHLocProduct to be picked obviously are
those with

matching productID and
tblWHLocProduct.outDetailpalletID= NULL

So at any point in time, to identify occupied space, you just need to
extract those WHLocID with

tblWHLocProduct.inDetailpalletID=NOT NULL and
tblWHLocProduct.outDetailpalletID= NULL

NB
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #7

P: n/a
ni******@lycos.com (NB) wrote in
news:5c**************************@posting.google.c om:
my concern is performance
when using findfirst against this recordset.


For 450 records?

I have never understood why people badmouth .FindFirst in
recordsets. No, it's not as fast as Seek on table-based recordsets,
but it doesn't need to be in most circumstances. It also allows you
to navigate recordsets that draw data from multiple tables, which
Seek cannot.

It's simple enough, I'd think, to test both methods and benchmark.

I doubt, though that you'll see any significant difference. There
may be a big difference in percentage -- one might be twice as fast
as another -- but what really matters is how significant the time
difference is. If one takes 1 second and one takes 2 seconds, I'd
definitely not choose on the basis of which is faster, but on the
basis of which is going to hit the database least. If it's more like
1 second vs. 30 seconds, then I might choose differently. But if
it's .05 seconds vs. 1 second, I'd again choose on efficiency of
hitting the back end.

I don't see how you could do it more efficiently than with walking a
single recordset. You shouldn't need to do .FindFirst, anyway -- all
you need to do is .MoveFirst and then .MoveNext, since you can
populate the controls in any order you want. If you want the data to
appear in the controls in order, you can simply sort the recordset
appropriately.

So, I just don't see what .FindFirst has to do with it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #8

P: n/a
NB wrote:
Option 1:
- open a recordset of all occupied space in that zone
- Loop through and compare each of the 450 textboxes with locations in
the recordset to identify whether the space is occupied, then
respectively format the textbox


This one. Single round trip to server to retrieve (presumably) fairly
small recordset. All subsequent manipulation done client-side.


As mentioned in another post in the thread: my concern is performance
when using findfirst against this recordset.


You don't need to, each of your inventory records has some sort of ID
yes? (e.g. shelf number), you can name your textboxes after them, e.g.

Shelf number: 100 - Textbox txtShelf100

Loop the recordset, e.g.

Do until .Eof
Me("txtShelf" & !ShelfNumber) = !ShelfNumber
.MoveNext
Loop
Nov 12 '05 #9

P: n/a
starwars <no****@tatooine.homelinux.net> wrote:
Comments: This message did not originate from the Sender address above.
It was remailed automatically by anonymizing remailer software.


Please ignore the above posting and others which are somewhat bizarre.

Note to persons new to this newsgroup. One person, with many identities, is being
rather disruptive. If you see a bizarre posting it's probably the work of this
disruptive person..

Check the headers of the posting. If you see the following the posting likely can be
ignored. Of course, there will likely be other headers to be added to this list.

Comments: This message did not originate from the Sender address above.
It was remailed automatically by anonymizing remailer software.
or
X-Abuse-Report: ab***@teranews.com
or
Organization: Posted via Supernews, http://www.supernews.com
or
Organization: 100ProofNews.com - Unlimited Downloads - $8.95/Month

You can also change your NewsReader program settings to ignore off-topic posts. See
http://www.hyphenologist.co.uk/killfile/ for more information.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #10

P: n/a
Ladies and gentlemen of CDMA, please be advised that Mr. David W. Fenton
wrote publicly about his allowing a boy to suck his gay cock in
a gay bath house somewhere in the filth capital of the world,
New York City, where one can hardly walk down a sidewalk and
avoid stepping in vomit.

In article <Xn**********************************@24.168.128.9 0>
"David W. Fenton" <dX********@bway.net.invalid> wrote:

ni******@lycos.com (NB) wrote in
news:5c**************************@posting.google.c om:
my concern is performance
when using findfirst against this recordset.


For 450 records?

I have never understood why people badmouth .FindFirst in
recordsets. No, it's not as fast as Seek on table-based recordsets,
but it doesn't need to be in most circumstances. It also allows you
to navigate recordsets that draw data from multiple tables, which
Seek cannot.

It's simple enough, I'd think, to test both methods and benchmark.

I doubt, though that you'll see any significant difference. There
may be a big difference in percentage -- one might be twice as fast
as another -- but what really matters is how significant the time
difference is. If one takes 1 second and one takes 2 seconds, I'd
definitely not choose on the basis of which is faster, but on the
basis of which is going to hit the database least. If it's more like
1 second vs. 30 seconds, then I might choose differently. But if
it's .05 seconds vs. 1 second, I'd again choose on efficiency of
hitting the back end.

I don't see how you could do it more efficiently than with walking a
single recordset. You shouldn't need to do .FindFirst, anyway -- all
you need to do is .MoveFirst and then .MoveNext, since you can
populate the controls in any order you want. If you want the data to
appear in the controls in order, you can simply sort the recordset
appropriately.

So, I just don't see what .FindFirst has to do with it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc










Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.