SQL Timestamp field for Access use

"Armen Stein" wrote in message


We finally did some extensive testing based on your statements and
determined that indeed, Access-SQL Server can do partial-row
concurrency checking. As you indicated, without a Timestamp, fields
A, B, C can be updated independently from fields D, E, F and no
conflict will occur. But adding a Timestamp causes the whole row to
be checked.


Very very interesting. However, the question becomes (ignoring trigger
issues). when you say whole row, is that checking occurring server side
or access side? Now that this issue be brought up, I understand that
including a timestamp field can "COST" some concurrency issue and
ability. However, if that timestamp allows access to do a BETTER job
of not having to check which fields (in fact "any" fields) that
may have changed then we gain on the access side. In other words I
have to dig farther as to what access does when it "checks" that fields
have changed. If we can "skip" the field checking (comparison) step that
jet uses with sql server, then it still going to be beneficial to use a
timestamp in those fields.

However, your point about background processes updating other fields
is well taken. Adding a Timestamp increases the chances of a trigger
or other background update encountering an issue.

So if I may summarize some proposed best practices:

--------------------

- Avoid using floating point numeric fields, because certain floating
point values may fail unexpectedly in the future.


Agreed. Floating point fields don't compare well at all. I not even sure
ms-access can compare the values correctly to determine that a record been
changed....


- Do not include a Timestamp/RowVersion field in the table.


I 90% agreeing with this "new" suggestion. However, if ms-access can skip
the field comparison and only use the timestamp field to determine a record
change...I am willing to give up the concurrency gains you mention in place
of less work (less network clatter) by ms-access to determine field changes.
If the timestamp field does NOT help ms-access to reduce checking, then that
last 10% of my doubts are gone and as a future recommend we should not just
shotgun add timestamp fields to the sql tables (and, I am guilty of doing
this).


Interestingly, we also tried similar testing in Access 2003 using a
Jet back-end, and found that Access can perform partial-row
concurrency testing also. This underscores the importance of
including the minimum number of fields in a form's Recordsource
instead of using just the table name or "Select * From ...".


I was actually aware of the above, and it is even outlined in the old jet
"white" performance paper...


Sylvain, thank you for taking the time to explain this! I've learned
something new that will improve our project standards in the future.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com


And a big thank you to you also! I just learned something new that the
access community been not too knowledgeable on here (that issue is simply to
always adding a TS column). However, I am still reserving the 10% doubt due
to my above question/issue.

Note that I am not doubting the behavior outlined, I am simply wondering if
the TS column can eliminate the field by field checking, and if yes then
I'll not feel so bad about having a TS column everywhere since then at least
understand the tradeoff between concurrency and less fields having to be
checked for a dirty record...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert D. Kallal wrote:

Note that I am not doubting the behavior outlined, I am simply wondering if
the TS column can eliminate the field by field checking, and if yes then
I'll not feel so bad about having a TS column everywhere since then at least
understand the tradeoff between concurrency and less fields having to be
checked for a dirty record...

See:

Optimizing Microsoft Office Access Applications Linked to SQL Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

under "Supporting Concurrency Checks" (about halfway down the page).

James A. Fortune


HOMEY - An Englishman. -- Dictionary of Australian Slang, Second
Edition, Sidney J. Baker, 1943 (Price: Three shillings & sixpence)

"James A. Fortune" wrote in message
...
Albert D. Kallal wrote:

Note that I am not doubting the behavior outlined, I am simply wondering
if the TS column can eliminate the field by field checking, and if yes
then I'll not feel so bad about having a TS column everywhere since then
at least understand the tradeoff between concurrency and less fields
having to be checked for a dirty record...

See:

Optimizing Microsoft Office Access Applications Linked to SQL Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

under "Supporting Concurrency Checks" (about halfway down the page).


**Excellent** ...this very close to what I had read in the past. How this
works is even more clear now...thanks for posting that link.

I bookmarked the above Optimizing article. In fact I had not seen that
article before. I note the date is late 2006 (about around the last time
been reading on sql articles). I have to say that link is quite rather full
of good reading.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

On Mon, 6 Apr 2009 02:47:50 -0600, "Albert D. Kallal"
wrote:

"James A. Fortune" wrote in message
...
Albert D. Kallal wrote:

Note that I am not doubting the behavior outlined, I am simply wondering
if the TS column can eliminate the field by field checking, and if yes
then I'll not feel so bad about having a TS column everywhere since then
at least understand the tradeoff between concurrency and less fields
having to be checked for a dirty record...

See:

Optimizing Microsoft Office Access Applications Linked to SQL Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

under "Supporting Concurrency Checks" (about halfway down the page).


**Excellent** ...this very close to what I had read in the past. How this
works is even more clear now...thanks for posting that link.

I bookmarked the above Optimizing article. In fact I had not seen that
article before. I note the date is late 2006 (about around the last time
been reading on sql articles). I have to say that link is quite rather full
of good reading.

It's a great article, and seems to recap some of the ideas Mary
Chipman has talked about from the book she wrote with Andy Baron
(Microsoft Access Developer's Guide to SQL Server).

However, the section Supporting Concurrency Checks does not mention
the downside of adding timestamps, namely the loss of partial-row
concurrency checking that Sylvain pointed out. We have some large
databases where we have denormalized fields that are calculated by
triggers or scheduled jobs. Having partial row concurrency checking
could help in these situations.

Albert, you're right that there's a purported performance improvement
in the field comparison when a timestamp is added to the table,
because the timestamp can be used instead of comparing all the fields.
But I haven't seen a performance test on this, so I don't know how
significant the improvement is. Anyone want to try it? Say on a
table with 20 fields or so, thousands of updates, both with and
without a timestamp field.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

"Albert D. Kallal" wrote in
:

"James A. Fortune" wrote in message
...

See:

Optimizing Microsoft Office Access Applications Linked to SQL
Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

under "Supporting Concurrency Checks" (about halfway down the
page).

**Excellent** ...this very close to what I had read in the past.
How this works is even more clear now...thanks for posting that
link.

I bookmarked the above Optimizing article. In fact I had not seen
that article before. I note the date is late 2006 (about around
the last time been reading on sql articles). I have to say that
link is quite rather full of good reading.

It's filled with all sorts of good things. Perhaps they were all
covered in Mary Chipman's book, but it was so long ago that I read
that, and when I had so little experience with SQL Server that it
probably never sunk in.

A really amazing thing in that article for me is the revelation that
you can run DDL against a table link to change the index. I've
always known there's a lot of metadata stored in a linked table
definition, but this is the first time I've read that you can
manipulate any of it in a useful way. I'll have to do some poking
around to see what I can learn about that.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"James A. Fortune" wrote in
:

Optimizing Microsoft Office Access Applications Linked to SQL
Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

Albert is right in saying what a great article that is. I'm just
halfway through it and have already learned a lot (mostly about why
the way I retrieve data is the correct way to do it, i.e., never
retrieve the whole table, which is, of course, proper advice for a
Jet back end, too). I have a question about this, though:

When you create an ODBC-linked table, Office Access looks first
for a clustered unique index.

Now, I have an app that has people in it. I have the table clustered
on Lastname/Firstname. It has a long integer (i.e., integer in SQL
Server) PK. I retrieve data from that table one of three methods:

1. selecting on Lastname only

2. selecting on Lastname and Firstname

3. selecting on the PK field.

The data is always presented sorted in Lastname/Firstname order, and
that's why I clustered on that, hoping it would save a sort.

Is this a problem? There is no clustered unique index on the table
at all, so based on the quote above, am I slowing things down? It
doesn't seem so -- retrieval from a table with 350K rows is
instantaneous.

The table is almost never retrieved as a whole -- the only time that
happens is when I'm mucking around viewing a table datasheet (and I
have noticed that it's much slower to get to the end of the table
than with a Jet back end, and now I know why). When the users are
using the app, data is always requested with criteria as listed
above (or in certain reports where the criteria are on a joined
table that is a child of the main table described above).

Have I made a mistake with the clustering on Lastname/Firstname?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Armen Stein wrote in
:

However, the section Supporting Concurrency Checks does not
mention the downside of adding timestamps, namely the loss of
partial-row concurrency checking that Sylvain pointed out. We
have some large databases where we have denormalized fields that
are calculated by triggers or scheduled jobs. Having partial row
concurrency checking could help in these situations.

Isn't this problem avoided by leaving the timestamp field out of
your SELECT statement? That is, if Access doesn't retrieve the value
of the timestamp, it's not going to be able to use it, right?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Hi David,

A long time ago I remember asking at a PNWADG (Pacific NW Access Developer's
Group) meeting if we needed to include the timestamp field in queries in
order for Access to make use of it. I was told "no" by a member of the
Access Development Team, who was present at the meeting.


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

__________________________________________

"David W. Fenton" wrote in message
36.89...

Isn't this problem avoided by leaving the timestamp field out of
your SELECT statement? That is, if Access doesn't retrieve the value
of the timestamp, it's not going to be able to use it, right?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote in message
36.89...
Armen Stein wrote in
:

However, the section Supporting Concurrency Checks does not
mention the downside of adding timestamps, namely the loss of
partial-row concurrency checking that Sylvain pointed out. We
have some large databases where we have denormalized fields that
are calculated by triggers or scheduled jobs. Having partial row
concurrency checking could help in these situations.

Isn't this problem avoided by leaving the timestamp field out of
your SELECT statement? That is, if Access doesn't retrieve the value
of the timestamp, it's not going to be able to use it, right?

Nope, as far as I can remember, Access will still pull out the timestamp
value and use for controling the updating even if it's not part of the
Select statement.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

"David W. Fenton" wrote in message
36.89...
"James A. Fortune" wrote in
:

Optimizing Microsoft Office Access Applications Linked to SQL
Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

Albert is right in saying what a great article that is. I'm just
halfway through it and have already learned a lot (mostly about why
the way I retrieve data is the correct way to do it, i.e., never
retrieve the whole table, which is, of course, proper advice for a
Jet back end, too). I have a question about this, though:

When you create an ODBC-linked table, Office Access looks first
for a clustered unique index.

Now, I have an app that has people in it. I have the table clustered
on Lastname/Firstname. It has a long integer (i.e., integer in SQL
Server) PK. I retrieve data from that table one of three methods:

1. selecting on Lastname only

2. selecting on Lastname and Firstname

3. selecting on the PK field.

The data is always presented sorted in Lastname/Firstname order, and
that's why I clustered on that, hoping it would save a sort.

Is this a problem? There is no clustered unique index on the table
at all, so based on the quote above, am I slowing things down? It
doesn't seem so -- retrieval from a table with 350K rows is
instantaneous.

The table is almost never retrieved as a whole -- the only time that
happens is when I'm mucking around viewing a table datasheet (and I
have noticed that it's much slower to get to the end of the table
than with a Jet back end, and now I know why). When the users are
using the app, data is always requested with criteria as listed
above (or in certain reports where the criteria are on a joined
table that is a child of the main table described above).

Have I made a mistake with the clustering on Lastname/Firstname?

You could create a clustered, unique index including the three fields,
Lastname, Firstname, and PK, and see if it makes a difference?

--
Brendan Reynolds