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

[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[color=blue]
>0, respectively format the textbox.[/color]


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

Thanks for suggestion (if any)

NB

[Mike Storr]

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

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

https://www.veraccess.com

NB wrote:[color=blue]
> 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
>[color=green]
>>0, respectively format the textbox.[/color]
>
>
>
> I'm not sure which approach is more efficient.
> Or any other algorythm to achieve the same goal?
>
> Thanks for suggestion (if any)
>
> NB[/color]

[Edward]

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

nickbose@lycos. com (NB) wrote in message news:<5cbf60ef. 0402251914.9bf7 129@posting.goo gle.com>...[color=blue]
> 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[/color]

This one. Single round trip to server to retrieve (presumably) fairly
small recordset. All subsequent manipulation done client-side.
[color=blue]
> 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[color=green]
> >0, respectively format the textbox.[/color][/color]

450 round trips to server? No thanks.

My 0.02p

Edward
--
The reading group's reading group:

http://www.bookgroup.org.uk

[Lyle Fairfield]

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

nickbose@lycos. com (NB) wrote in news:5cbf60ef.0 402251914.9bf71 29
@posting.google .com:
[color=blue]
> 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[color=green]
>>0, respectively format the textbox.[/color]
>
>
> I'm not sure which approach is more efficient.
> Or any other algorythm to achieve the same goal?
>
> Thanks for suggestion (if any)
>
> NB[/color]

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)

[NB]

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

> Is it not possible to treat groups of these text boxes as records in a[color=blue]
> table?[/color]

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
[color=blue]
> 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.[/color]

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

[NB]

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

> > Option 1:[color=blue][color=green]
> > - 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[/color]
>
> This one. Single round trip to server to retrieve (presumably) fairly
> small recordset. All subsequent manipulation done client-side.[/color]


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

[color=blue][color=green]
> > 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[color=darkred]
> > >0, respectively format the textbox.[/color][/color]
>
> 450 round trips to server? No thanks.[/color]

NB

[Nick Bose]

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

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, ...)

tblInDetailPall et stores data on how incoming goods are arranged in
pallets (inDetailPallet ID, ....)
tblOutDetailPal let stores data on how outgoing goods are arranged in
pallets (outDetailPalle tID, ....)

tblWHLocProduct (WHLocID, inDetailPalletI D, ... outDetailPallet ID) Note
that outDetailPallet ID 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 (tblInDetailPal let)

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

tblWHLocProduct .inDetailpallet ID=NULL or
(tblWHLocProduc t.inDetailpalle tID= NOT NULL and
tblWHLocProduct .outDetailpalle tID= NOT NULL)


For sending out goods, quantity & arrangement details will be keyed in
(tblOutDetailPa llet)
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 .outDetailpalle tID= NULL

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

tblWHLocProduct .inDetailpallet ID=NOT NULL and
tblWHLocProduct .outDetailpalle tID= NULL

NB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David W. Fenton]

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

nickbose@lycos. com (NB) wrote in
news:5cbf60ef.0 402261419.3a4fb 255@posting.goo gle.com:
[color=blue]
> my concern is performance
> when using findfirst against this recordset.[/color]

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

[Trevor Best]

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

NB wrote:
[color=blue][color=green][color=darkred]
>>>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
>>>respective ly format the textbox[/color]
>>
>>This one. Single round trip to server to retrieve (presumably) fairly
>>small recordset. All subsequent manipulation done client-side.[/color]
>
>
>
> As mentioned in another post in the thread: my concern is performance
> when using findfirst against this recordset.[/color]

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

[Tony Toews]

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

starwars <nobody@tatooin e.homelinux.net > wrote:
[color=blue]
>Comments: This message did not originate from the Sender address above.
> It was remailed automatically by anonymizing remailer software.[/color]

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: abuse@teranews. com
or
Organization: Posted via Supernews, http://www.supernews.com
or
Organization: 100ProofNews.co m - 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

[Nomen Nescio]

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

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 <Xns949BDB78AF4 E7dfentonbwayne tinvali@24.168. 128.90>
"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote:[color=blue]
>
> nickbose@lycos. com (NB) wrote in
> news:5cbf60ef.0 402261419.3a4fb 255@posting.goo gle.com:
>[color=green]
> > my concern is performance
> > when using findfirst against this recordset.[/color]
>
> 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[/color]