Anyone ever run into situation where time advanced by 1 second during large WRITE?

[MLH]

Suppose you're using a query to update or append many records in a
large table. And suppose one of those fields is a T/D stamp table and
you're using Now() to write its value. It is conceivable that a large
number of records could take a few milliseconds to write and that the
time could change from 13:00:00 to 13:00:01 between the time the first
record was modified and the time the last was modified.

Now I've never seen that happen. But I'm wondering if any of you have?

Sometimes, it is convenient fort all mods have the same T/D stamp.
If it can happen, I may wish to assign a variant the value of Now() to
FIX the value and use that to furnish the value to each of the record
writes instead of Now() - whose value changes with time.

Comments?

[John Winterbottom]

I can't answer you question but AFAIK, standard SQL mandates that data
affecting operations such as UPDATE occur for all rows simultaneously,
(regardless of any sequential processing in the lower-level
implementation). The fact that this is not the case in Jet can be
illustrated by the following. Given table foo:

create table foo
(
id int not null primary key,
lineNbr int not null unique
)

with some data
insert into foo(id, lineNbr) values(1,1)
insert into foo(id, lineNbr) values(2,2)

The following sql *should* be valid:

update foo set lineNbr = IIf(lineNbr = 1, 2, 1)

But in fact this will fail against a jet database. Other database
engines such as sql server use "deferred constraint checking" and allow
the above to succeed.

I would think however that in your example, the query optimizer would be
smart enough to call now() only once and then use that value for all rows.


MLH wrote:

Suppose you're using a query to update or append many records in a
large table. And suppose one of those fields is a T/D stamp table and
you're using Now() to write its value. It is conceivable that a large
number of records could take a few milliseconds to write and that the
time could change from 13:00:00 to 13:00:01 between the time the first
record was modified and the time the last was modified.

Now I've never seen that happen. But I'm wondering if any of you have?

Sometimes, it is convenient fort all mods have the same T/D stamp.
If it can happen, I may wish to assign a variant the value of Now() to
FIX the value and use that to furnish the value to each of the record
writes instead of Now() - whose value changes with time.

Comments?

[Tom van Stiphout]

On Sat, 07 Jul 2007 12:12:15 -0400, MLH <CR**@NorthState.netwrote:

I think that doesn't happen because of optimization. The query engine
realizes that ALL rows have the same formula, so it calls that formula
once, and uses that value to populate each row.

Same thing you can observe when you have a function of your own:
select CustomerID, MyFunction() from Customers
Access will call MyFunction only once.
If you want to force Access to call the function for each row, you
need to pass in a unique value even if it is not used in the function:
select CustomerID, MyFunction(CustomerID) from Customers

-Tom.

>Suppose you're using a query to update or append many records in a
large table. And suppose one of those fields is a T/D stamp table and
you're using Now() to write its value. It is conceivable that a large
number of records could take a few milliseconds to write and that the
time could change from 13:00:00 to 13:00:01 between the time the first
record was modified and the time the last was modified.

Now I've never seen that happen. But I'm wondering if any of you have?

Sometimes, it is convenient fort all mods have the same T/D stamp.
If it can happen, I may wish to assign a variant the value of Now() to
FIX the value and use that to furnish the value to each of the record
writes instead of Now() - whose value changes with time.

Comments?

[MLH]

Based on your comments, then, VBA won't read Now() more than
once in my example. And you've never seen any evidence to the
contrary. That's comforting.

What about a query executing row-by-row writing Now() to one
of the target fields and MyFunc(MyTableID) to another. Realizing
that they are separate fields, I wonder if forcing Access to call the
function for each row as you pointed out might result in Access
firing new calc's for all fields in each subsequent row - or just the
one field calling the FN with a unique passed value?

If I had a real slow disk - perhaps a floppy - I could test this.
Hmmm??? Dunno if the smallest mdb possible will even fit on
a floppy.

[MLH]

I understand - not the SQL you mentioned -
but your comments in general. Dunno any
more than I did before posting - except that
a couple of you believe that Now() would
propogate to zillions of records in a fixed
value amount.

That is, of course, what I want. I just wish
I could come up with some way to PROVE
it. I suppose I could write out several million
records to a table and do a test.

Maybe I could do something like
Dim StartTime, StopTime

StartTime = Now()
UPDATE ExtremelyLongTable SET ExtremelyLongTable .InDate = Now(),
ExtremelyLongTable .InType = '09'
StopTime = Now()

If the difference between StartTime and StopTime were
a few seconds, I would KNOW that substantial time passed
during the process. If the time written to all the records was
the same - then I'll have an answer - at least for that specific
SQL string.