Row- and field-level modification dates

Hello all,

This is the best suited forum I found that fits my problem. If there's a better one, please direct me to it.

I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.

For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.

I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.

Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.

Any help will be greatly appreciated!

Thanks.

P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.

P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
This mechanism of auditing can be implemented in Acces with help of AfterUpdate Event code and in SQL with triggers.
I should create a table
ID(identity) Column DataofModifying(DataTime default value) Before After

When the user do something, after update you can write in this table.

There is no automatic way (wait some time because I heard Katmai, SQL Server 2008 have data auditing)|||

There is no specific, built in, way to determine if a row or field has been altered. In situations where alteration auditing is requried, it is often necessary to use 'a really ugly solution' and add ChangedDate columns to every table. (And other columns necessary for auditing are often added as well.)

However, SQL Server does provide 'Replication' where data changes are constantly 'set aside' in a special set of tables to be sent to the replicant. I don't think that would be a solution for your situation.

|||Thanks for replying so quickly.

I think that is amazing.
Are you saying that this feature exists on all Palm devices (even the old black-and-white ones), but is missing from all data server solutions?

I find that extremely hard to believe.

Just for argument's sake, here is a quote out of Palm OS Conduit Development Kit 6.01, describing the eRecAttrDirty flag:

eRecAttrDirty = 0x40 Indicates that the record has been marked as modified. This replaces the older constant DIRTY_BIT.

|||

You should recognize that Palm is an 'application', not a data server.

.NET applications also have the capability to track data changes to a datset.

But SQL Server does not offer the functionality you had hoped to find.

|||Hi again.

Can you elaborate on the functionality in .NET applications?

That might be the solution I'm looking for, if there's any way to make it interact with Access.
|||

Yes, .NET applications can use data stored in Access databases.

But you would be better positioned to post your .NET/Access questions in a .NET (VB/C#) or Access related forum. Not as many folks here that are deeply very familiar with those environments.

|||Alright then, I'll direct this question to a more suitable forum.

Thanks for the help!
|||

SQL Server has a data type called "timestamp", which is really a "row version" and has nothing to do time. You can use this to determine whether a row has been modified when (manually/in your app) synchronizing against another database.

Row- and field-level modification dates

Hello all,

This is the best suited forum I found that fits my problem. If there's a better one, please direct me to it.

I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.

For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.

I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.

Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.

Any help will be greatly appreciated!

Thanks.

P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.

P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
This mechanism of auditing can be implemented in Acces with help of AfterUpdate Event code and in SQL with triggers.
I should create a table
ID(identity) Column DataofModifying(DataTime default value) Before After

When the user do something, after update you can write in this table.

There is no automatic way (wait some time because I heard Katmai, SQL Server 2008 have data auditing)|||

There is no specific, built in, way to determine if a row or field has been altered. In situations where alteration auditing is requried, it is often necessary to use 'a really ugly solution' and add ChangedDate columns to every table. (And other columns necessary for auditing are often added as well.)

However, SQL Server does provide 'Replication' where data changes are constantly 'set aside' in a special set of tables to be sent to the replicant. I don't think that would be a solution for your situation.

|||Thanks for replying so quickly.

I think that is amazing.
Are you saying that this feature exists on all Palm devices (even the old black-and-white ones), but is missing from all data server solutions?

I find that extremely hard to believe.

Just for argument's sake, here is a quote out of Palm OS Conduit Development Kit 6.01, describing the eRecAttrDirty flag:

eRecAttrDirty = 0x40 Indicates that the record has been marked as modified. This replaces the older constant DIRTY_BIT.

|||

You should recognize that Palm is an 'application', not a data server.

.NET applications also have the capability to track data changes to a datset.

But SQL Server does not offer the functionality you had hoped to find.

|||Hi again.

Can you elaborate on the functionality in .NET applications?

That might be the solution I'm looking for, if there's any way to make it interact with Access.
|||

Yes, .NET applications can use data stored in Access databases.

But you would be better positioned to post your .NET/Access questions in a .NET (VB/C#) or Access related forum. Not as many folks here that are deeply very familiar with those environments.

|||Alright then, I'll direct this question to a more suitable forum.

Thanks for the help!
|||

SQL Server has a data type called "timestamp", which is really a "row version" and has nothing to do time. You can use this to determine whether a row has been modified when (manually/in your app) synchronizing against another database.

Rounding datetime values down

Hi,
I have a task that needs to filter large datasets by date, ignoring the
time. However, we do need to store times alongside the dates for other
uses.
The way that had been implemented was to convert to a varchar and then back
again e.g.
convert(datetime, convert(varchar(10), col_name, 103), 103)
However, this results in an unacceptable performance hit.
Is there any better way of achieving this, or am I stuck with having an
extra column which can be populated at the same time (e.g. by a
insert/update trigger)?
John McLuskyJust query the DATETIME column as a range. For example, for today's date:
SELECT ...
FROM YourTable
WHERE col_name >= '20050608'
AND col_name < '20050609' ;
David Portas
SQL Server MVP
--|||David Portas wrote:
> Just query the DATETIME column as a range. For example, for today's
> date:
> SELECT ...
> FROM YourTable
> WHERE col_name >= '20050608'
> AND col_name < '20050609' ;
Hi David,
Thanks for the suggestion. It may be possible to implement inside the
stored procedures that we're using at the moment and I'll look into that
tomorrow when I'm back in the office.
However, if there's a possibility of converting the dates directly (so they
can be used in a view, for example) that would be the best solution.
At the moment, the new column looks like the easiest option!
John.|||Try,
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050131'
select c1, ..., cn
from dbo.t1
where c2 >= @.sd and c2 < dateadd(day, 1, @.ed)
AMB
"JM" wrote:

> Hi,
> I have a task that needs to filter large datasets by date, ignoring the
> time. However, we do need to store times alongside the dates for other
> uses.
> The way that had been implemented was to convert to a varchar and then bac
k
> again e.g.
> convert(datetime, convert(varchar(10), col_name, 103), 103)
> However, this results in an unacceptable performance hit.
> Is there any better way of achieving this, or am I stuck with having an
> extra column which can be populated at the same time (e.g. by a
> insert/update trigger)?
> John McLusky
>
>|||> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
You already have that solution. One possible improvement is to cast to INT
and then back to DATETIME. However, the range query method is better because
it can make full use of an index on the column and avoids unnecessary type
conversions. This can make a BIG difference to performance. Of course you
could consider an indexed view or indexed computed column but that seems
redundant to me if your data can be accessed via an SP.
David Portas
SQL Server MVP
--|||I don't understand. You first post asked for code suggestions how to do this
in a efficient way.
Then you imply that you will have difficulties to implement that. Can you ch
ange your code or not?
If you can, do it the right way.
I can imagine adding a computed column to the table and index that column. W
ould I do that? No. You
would still have to adapt your code for the dirty solution, so better to do
it right up front. :-)
Some elaborations on the subject (showing some options, explaining why IMO D
avid's suggestion is the
ay to go):
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JM" <john@.demon.invalid> wrote in message news:3gontsFdkcgoU1@.individual.net...red">
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the sto
red procedures that
> we're using at the moment and I'll look into that tomorrow when I'm back i
n the office.
> However, if there's a possibility of converting the dates directly (so the
y can be used in a view,
> for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||The easiest way is rarely the best way.
In this case though, the easiest is the best.
Try David's solution.
Maybe you didn't give enough information on what you are trying to do for
the contributers here to find the best solution.
"JM" <john@.demon.invalid> wrote in message
news:3gontsFdkcgoU1@.individual.net...
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the
> stored procedures that we're using at the moment and I'll look into that
> tomorrow when I'm back in the office.
> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||Hi all,
I agree, the (truly) best solution would appear to be David's, and I will
see what I can do with our SPs tomorrow.
When I said 'best' before, I really meant easiest from an implementation
point of view - messy, but easy.
Thanks for all the suggestions. I guess I'm surprised that there isn't a
simple 'round to midnight' function that can be used!
John.
Raymond D'Anjou wrote:
> The easiest way is rarely the best way.
> In this case though, the easiest is the best.
> Try David's solution.
> Maybe you didn't give enough information on what you are trying to do
> for the contributers here to find the best solution.
> "JM" <john@.demon.invalid> wrote in message
> news:3gontsFdkcgoU1@.individual.net...