From: Tom Wickerath AOS168b AT comcast DOT on
In my last reply, I mentioned monitoring network traffic by looking at the
packets received data before and after running a query. Earlier this
afternoon, after I posted that reply, I set up a quick test to check the
packets data. I wanted to use a large table that anyone else could also have
access to, so I downloaded Sean Lahman's Baseball database:

http://www.baseball1.com/

The largest table in this database is the Fielding table, with 158,188
records. Still not huge, but at least it's publically available, so that
others can use it, if interested, to compare results. I created a new BE
database, disabled Name Autocorrect, and imported the Fielding table. Then I
copied this database to a shared folder.

~~~~~~~~~~~~~~~~~~~~~~~~
Special Note:

I originally was thinking most of the shared folders I have access to are
located in Bellevue, WA., but it turns out that this particular server is
located in Kent, WA. I was able to get the building number, and street
address, using an internal search page. I also got the address for my
building from the same page, and then plugged these two addresses into
MapQuest. MapQuest shows a distance of 10.15 miles between my location and
the location of the file server. So, as much as I'd like to use a LAN only,
the fact is we are using a WAN, albeit a very fast and rock solid one.

~~~~~~~~~~~~~~~~~~~~~~~~

I used this aggregate query to produce an intentional table scan:

SELECT PlayerID, YearID, POS, Sum(G) AS GSum
FROM Fielding
GROUP BY PlayerID, YearID, POS;

and then I added these two WHERE clauses:

WHERE 1 = 0
and
WHERE False

I had not read Marsh's suggestion of using a wide table at the time I ran
these tests, otherwise, I would have included all fields from this table. I
ran each query three times, but started with a different query for each set.
This is indicated in the spreadsheet with the test number: Set 1 = {1a, 1b,
1c}, Set 2 = {2a, 2b, 2c} and Set 3 = {3a, 3b, 3c}. I closed the FE
application in-between sets 1 & 2 and sets 2 & 3. So, hopefully, this would
have cleared out any records cached in local memory, but I don't know that
for a fact.

I have uploaded a 30 KB .zip file, which includes the FE.mdb file, a
Results.xls spreadsheet, and a text file with the URL to the baseball site:

http://www.accessmvp.com/TWickerath/downloads/LargeTableTest.zip

The Excel spreadsheet shows that WHERE conditions of 1=0 and WHERE FALSE
resulted in an average of 83 and 82 packets transferred (n=3, with Access
closed after each set of tests), respectively, for queries with these
criteria. The query without a WHERE clause, run to get an idea of how many
packets of data would be transferred, resulted in an average of 9531 packets.
Thus, the two constrained queries resulted in about 0.87% and 0.86% of the
full table scan. I think it is safe to say, based on these results, that the
JET ShowPlan indication of a scan for this criteria is not honest.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Marshall Barton" wrote:

> Tom Wickerath <AOS168b AT comcast DOT net> wrote:
> >I sent a private e-mail to David late last night, inquiring about any past
> >test results. He replied as follows:
> >
> > "I don't recall anything of the sort, and don't see it in Google
> > Groups. Nor do I have archives of my posts from way back when.
> >
> > Sorry I can't help. Maybe Marshall has a better reference?"
>
> Not with the Google archives in such disarray. I definitely
> remember the gist of David's tests because I tried it on a
> client's A97 db that was kind of slow and it made a big
> difference.
>
> >> Performance testing can be very difficult and tricky,
> >> especially with things that involve I/O.
> >
> >I agree. When I am doing such testing at work, I typically reboot several
> >times, between each test, and I make sure to shut down as many applications
> >as I can, including Outlook, to try to make a somewhat stable baseline. On my
> >work PC, I cannot shut off the antivirus, so I just have to live with the
> >possibility that it may be adding some network load.
> >
> >I think a good way to test the 1=0 question might be to start with a really
> >large table, like Banana did, and monitor the amount of data transferred for:
> >
> >1.) An intentional table scan
> >Run some query that forces all records to be brought over a network wire,
> >perhaps an aggregrate query to sum a numeric field.
> >
> >2.) WHERE conditions that include 1=0, WHERE False, etc.
> >
> >One needs to first display the network monitor icon in the System Tray. In
> >Windows XP, one does this via:
> >
> > Control Panel | Network Connections
> >
> >Double-click the connection of interest, and then left-click on Properties.
> >Select the check box for the option that reads "Show icon in notification
> >area when connected". When you double-click the resulting System Tray icon,
> >you will see Packets Sent and Packets Received values. With a baseline that
> >is as repeatable as possible (ie. Outlook and any other networked
> >applications shut down), read the Packets Received value before and after
> >each individual test. The difference (delta) represents how many packets of
> >data was transferred to solve each query. Of course, one must keep in mind
> >that some other application that you may not be able to shut down may have
> >caused some of the traffic for a given test. So, one can run the test several
> >times, in an attempt to make sure there is not a contributing influence from
> >some other application. You need a split application, with the BE on a
> >network share, in order to use this method. Several years ago, John Viescas
> >recommended this method to me as a way of testing how much data actually
> >comes over the network wire.
>
>
> Good to know. I think that means the test needs to use a
> wide table so you can tell the difference between index
> retrieval and data retrieval.
>
> --
> Marsh
> MVP [MS Access]
From: John Spencer on
DEMO database zipped and sent.

By the way after thinking about this, I don't think that the write conflicts
have all that much to do with the number of records you have in the recordset.

Also, note that comboboxes are limited to a maximum of 64 K records (binary
K). So if you have more than that, you would need to come up with some scheme
to limit the number of records being returned in combobox's list.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Paul wrote:
> John,
>
> Here are both my office and home addresses in case one of them filters out
> the attachment.
>
> Work - my first and last names (below) separated by a dot, followed by the
> "at."
> The remainder is dgs ca gov, but with dots instead of spaces between the
> three strings.
>
> Home - my last name only, followed by the "at.
> The remainder is surewest net, again with a period instead of the space.
>
> I'm looking forward to receiving your Email.
>
> Thanks so much.
>
> Paul Ponzelli
> Staff Real Estate Officer
> DGS Central Leasing
>
>
From: Marshall Barton on
Tom Wickerath <AOS168b AT comcast DOT net> wrote:

>In my last reply, I mentioned monitoring network traffic by looking at the
>packets received data before and after running a query. Earlier this
>afternoon, after I posted that reply, I set up a quick test to check the
>packets data. I wanted to use a large table that anyone else could also have
>access to, so I downloaded Sean Lahman's Baseball database:
>
> http://www.baseball1.com/
>
>The largest table in this database is the Fielding table, with 158,188
>records. Still not huge, but at least it's publically available, so that
>others can use it, if interested, to compare results. I created a new BE
>database, disabled Name Autocorrect, and imported the Fielding table. Then I
>copied this database to a shared folder.
>
>~~~~~~~~~~~~~~~~~~~~~~~~
>Special Note:
>
>I originally was thinking most of the shared folders I have access to are
>located in Bellevue, WA., but it turns out that this particular server is
>located in Kent, WA. I was able to get the building number, and street
>address, using an internal search page. I also got the address for my
>building from the same page, and then plugged these two addresses into
>MapQuest. MapQuest shows a distance of 10.15 miles between my location and
>the location of the file server. So, as much as I'd like to use a LAN only,
>the fact is we are using a WAN, albeit a very fast and rock solid one.
>
>~~~~~~~~~~~~~~~~~~~~~~~~
>
>I used this aggregate query to produce an intentional table scan:
>
>SELECT PlayerID, YearID, POS, Sum(G) AS GSum
>FROM Fielding
>GROUP BY PlayerID, YearID, POS;
>
>and then I added these two WHERE clauses:
>
>WHERE 1 = 0
>and
>WHERE False
>
>I had not read Marsh's suggestion of using a wide table at the time I ran
>these tests, otherwise, I would have included all fields from this table. I
>ran each query three times, but started with a different query for each set.
>This is indicated in the spreadsheet with the test number: Set 1 = {1a, 1b,
>1c}, Set 2 = {2a, 2b, 2c} and Set 3 = {3a, 3b, 3c}. I closed the FE
>application in-between sets 1 & 2 and sets 2 & 3. So, hopefully, this would
>have cleared out any records cached in local memory, but I don't know that
>for a fact.
>
>I have uploaded a 30 KB .zip file, which includes the FE.mdb file, a
>Results.xls spreadsheet, and a text file with the URL to the baseball site:
>
> http://www.accessmvp.com/TWickerath/downloads/LargeTableTest.zip
>
>The Excel spreadsheet shows that WHERE conditions of 1=0 and WHERE FALSE
>resulted in an average of 83 and 82 packets transferred (n=3, with Access
>closed after each set of tests), respectively, for queries with these
>criteria. The query without a WHERE clause, run to get an idea of how many
>packets of data would be transferred, resulted in an average of 9531 packets.
>Thus, the two constrained queries resulted in about 0.87% and 0.86% of the
>full table scan. I think it is safe to say, based on these results, that the
>JET ShowPlan indication of a scan for this criteria is not honest.


Very nice job Tom. The packet counts appear to conclusively
demonstrate that the query optimizer has definitely been
improved since David's post and my simple test. This is
very good news and we can now forget about playing the
impossible PK criteria game that I brought up earlier.

I missed where you said which version of Jet and/or Ace you
used in the tests??

--
Marsh
MVP [MS Access]
From: David W. Fenton on
[BTW, I completely missed this interesting thread, because I have my
newsreader configured to kill crossposts to more than 2 newsgroups.
It's really unwise to crosspost excessively as it makes your post
look like spam; in this case, I can't see that anything other than
m.p.acess and m.p.formscoding was really appropriate, and my
philosophy is that if you post in m.p.access, i.e., the general
Access newsgrsoup, you shouldn't crosspost the same article to the
specific newsgroups -- instead, *don't* post it in the general
newsgroup and crosspost to 1 or more groups with specific
non-overlapping topics that are appropriate; but definitely keep the
crossposts to a minimum]

"Paul" <BegoneSpam(a)forever.com> wrote in
news:ONN76tUlKHA.1648(a)TK2MSFTNGP05.phx.gbl:

> I have been told by several developers that one way to minimize
> the occurrence of the Write Conflict is to put the main form's
> controls into a subform and remove the Record Source from the main
> form. You then set Child and Master Field links in the subforms
> to the value returned by the record selection combo box on the
> main form (stored in a text box on the main form).

I would suggest that you've perhaps misunderstood the
recommendation. It is never a good idea to have the same table
loaded in more than one editable table simultaneously. If you do
that, you're definitely setting yourself up for write conflicts, as
opposed to ameliorating write conflict errors.

My first question for you is to wonder if you're using optimistic or
pessimistic locking -- the first choice in all case is OPTIMISTIC,
but it sounds to me like you're using pessimistic.

Secondly, if you're updating a record in a different subform, save
any edits to the first subform before navigating to the other
subform. This is the only way to avoid write conflicts

Third, if you're running SQL updates in code against a table that is
also loaded in a form/forms, save the form/forms *before* running
the SQL update. Failure to do so guarantees write conflicts.

Last of all, further down the thread there's substantial discussion
of record-level locking. I've never used it and I've never had
issues.

Second, even if you use the tricks cited downthread to make sure
you're using record-level locking, if you then use DAO to update,
you're back to page-level locking for the DAO update, as DAO was
never updated by MS to be able to use record-level locking (because
of the stupid anti-DAO/pro-ADO campaign, which caused a whole bunch
of the Jet 4 innovations to be supported in ADO but not in DAO -- we
are still living with the after-effects of that bloody stupid
decision on MS's part).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on
Marshall Barton <marshbarton(a)wowway.com> wrote in
news:pfd6l5ppku6c82oqkpiun44kvqg7k3c54r(a)4ax.com:

> Side note. There was a cdma thread on this issue many years
> ago where David Fenton posted the results of extensive
> performance testing of using anything that boils down to
> WHERE False. In my mind, he conclusively demonstrated that
> Jet query optimization is not smart enough to recognize that
> no records will be retrieved and consequently does a full
> table scan.

As Tom reported, I can't find any record of this.

Based on further discussion downthread, I can't imagine that Jet is
actually really doing a full table scan. Expressions that don't use
data from a field in a table get evaluated by Jet just once. For
instance, Rnd() will be evaluated once, while Rnd([FieldFromTable])
will get evaluated for each row.

Thus, it seems to me that WHERE 0=1 or any other variation on WHERE
FALSE that doesn't use data that changes row-by-row will be
evaluated only once. My guess is that SHOWPLAN is WRONG and there is
no table scan. Banana's test results bear that out, I think.

That said, I think I'd rather use something that I know what it
does, and I know that AutonumberPK = .5 is going to use the index
and is going to be blazingly fast (it doesn't have to do anything
with the index except examine the metadata to find out that it's an
impossible value). It's also going to be portable without causing
performance problems, whereas there's no guarantee that other db
engines will smartly optimize WHERE FALSE.

And last of all, I never use empty recordsources for forms like this
because the forms come up blank. Instead, I use a rowsource with one
record that returns Null for all the fields used in the
ControlSources on the form:

SELECT TOP 1 Null As Field1, Null As Field2, Null As Field3
FROM SmallestTableInMyDatabase

This insures no performance hit and that the form displays with no
data and is completely uneditable. I think that's a much better way
to start with a form where you are setting the recordsource based on
user choice.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13
Prev: não acho meu orkut
Next: maike yordano pirela vera