Best design for edit tracking?

Hey all,
This is a general question. Here's my scenario: We have a legacy
database. The core table within contains almost 4 million records in
SQL Server. Currently we have a web front end which allows uses to
search through the database for the information they need.
What the client wants is a web front end which allows some users to
edit the core table above. No problem. What the client also wants is
for the non-editable web front end (used for research) to display (via
colored text) which records have been edited. Example:
1.) Jimmy edits records x, y, and z in coretable1 using the editing
web app.
2.) Jane comes along and uses the research web app to hunt down some
records. She views records s through z. In her view she notices that
certain cells in rows x, y, and z are colored red. This tells her that
Jimmy has edited those specific fields in those specific rows.
My question is: what is the most efficient way to track these column
specific edits so that my web app can display them? This may seem like
a web dev question, but the reality is that my web apps have to
interact with SQL Server so if anyone has any input, I'd love to hear
it! Thanks.OK, the quickest solution to this goest something like this...
Add two rows to your core table, the first is a DateTime with a Default
constraint of GetDate(). The second column is used to identify who made the
change.
Next when update one of the rows, you need to simply include the identifier
of the person who made the change.
Next when you read the table you need to read and compare two rows. The
most recent row, and the second most recent row. This provides the
information about what changed - as a freebie you'll gain access to the old
version of the row.
Another approach would be to have two tables. The first is the core table
as it is now. The second table contains a flag for each column in your core
table, the DateTime column (as above) and the user identifier column (as
above). This time when you edit the row, you also place a new row into this
table settings the flags for the columns which were altered. Don't forget
to include a foreign key back to your core table. When you select the row
from the core table you can also join on your change map, selecting the
max(datetime) and this will tell what columns were changed in the last edit.
But it won't tell you the values that they were before. There is a big
advantage in this method as your core table doesn't need to be altered.
Regards
Colin Dawson
www.cjdawson.com
"roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
> Hey all,
> This is a general question. Here's my scenario: We have a legacy
> database. The core table within contains almost 4 million records in
> SQL Server. Currently we have a web front end which allows uses to
> search through the database for the information they need.
> What the client wants is a web front end which allows some users to
> edit the core table above. No problem. What the client also wants is
> for the non-editable web front end (used for research) to display (via
> colored text) which records have been edited. Example:
> 1.) Jimmy edits records x, y, and z in coretable1 using the editing
> web app.
> 2.) Jane comes along and uses the research web app to hunt down some
> records. She views records s through z. In her view she notices that
> certain cells in rows x, y, and z are colored red. This tells her that
> Jimmy has edited those specific fields in those specific rows.
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
>|||Colin
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
I think you menat "add to columns", and it is worth mentioning that with
this solutuin you will have to write a trigget on that table in order to
track changes
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
> OK, the quickest solution to this goest something like this...
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
> Next when update one of the rows, you need to simply include the
> identifier of the person who made the change.
> Next when you read the table you need to read and compare two rows. The
> most recent row, and the second most recent row. This provides the
> information about what changed - as a freebie you'll gain access to the
> old version of the row.
> Another approach would be to have two tables. The first is the core table
> as it is now. The second table contains a flag for each column in your
> core table, the DateTime column (as above) and the user identifier column
> (as above). This time when you edit the row, you also place a new row
> into this table settings the flags for the columns which were altered.
> Don't forget to include a foreign key back to your core table. When you
> select the row from the core table you can also join on your change map,
> selecting the max(datetime) and this will tell what columns were changed
> in the last edit. But it won't tell you the values that they were before.
> There is a big advantage in this method as your core table doesn't need to
> be altered.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
> news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
>|||oops, I did mean columns yes.
A trigger won't really be able to help as you'll need to supply extra
information than what is stored in the original table. It would be better
to use a Stored procedure and directly enter the data into the new columns.
Of course, the exception to this is that if the application connects to SQL
using seperate usernames, it is possible to use the @.@.User in a trigger to
accomplish the same result. With the applications that my company creates,
this is not possible as they alway connect with the same user (connection
pooling)
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uSloXSxdGHA.4108@.TK2MSFTNGP03.phx.gbl...
> Colin
> I think you menat "add to columns", and it is worth mentioning that with
> this solutuin you will have to write a trigget on that table in order to
> track changes
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
>|||On 13 May 2006 08:30:37 -0700, roy.@.nderson@.gm@.il.com wrote:

>Hey all,
>This is a general question. Here's my scenario: We have a legacy
>database. The core table within contains almost 4 million records in
>SQL Server. Currently we have a web front end which allows uses to
>search through the database for the information they need.
>What the client wants is a web front end which allows some users to
>edit the core table above. No problem. What the client also wants is
>for the non-editable web front end (used for research) to display (via
>colored text) which records have been edited. Example:
>1.) Jimmy edits records x, y, and z in coretable1 using the editing
>web app.
>2.) Jane comes along and uses the research web app to hunt down some
>records. She views records s through z. In her view she notices that
>certain cells in rows x, y, and z are colored red. This tells her that
>Jimmy has edited those specific fields in those specific rows.
>My question is: what is the most efficient way to track these column
>specific edits so that my web app can display them? This may seem like
>a web dev question, but the reality is that my web apps have to
>interact with SQL Server so if anyone has any input, I'd love to hear
>it! Thanks.
Hi Roy,
This is impossible to answer, because the requirements are incomplete.
For instance, what happens if Jimmy edits rows x, y, and z (as in your
example), then Joan edits rows w and x, then Jimmy edits z another time
and then Jane views rows s through z. What columns in what rows have to
be marked as "changed"?
Another question - what if Jimmy edits rows x, y, and z; then nothin
happens for a long time. After a year, Jane looks at rows s through z.
Should Jimmy's changes still be marked?
Hugo Kornelis, SQL Server MVP|||>
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
There are several ways to accomplish that. Do you want your system to
be optimized for retrieval of the current version but support
occasional drill down into editing history. Or do you want to optimize
retrieving history of edits and are ready to pay the price of slowing
down retrieval of current version?

Best Data Type for a Tracking ID?

Hello,
We're in the table design process and could really use outside thoughts on
our options.
The table in question will grow to several million records. This table will
update and insert depending on what the tracking ID does as it lives out it
day long life span.
Example Stored Proc:
UPDATE tbTracking
SET myCount = myCount +1
WHERE trackingID = @.trackingID AND productID = @.productID
IF (@.@.rowcount = 0)
BEGIN
INSERT INTO tbTracking (...) VALUES (...) ;
END
Everyday at midnight the table will be truncate just afer many other queries
generated reports and store cumulative data into other tables.
Our goal is extreme performance, with that in mind what is the best data
type for trackingID and productID? We have the luxury to make them whatever
is best, as this is a completely new project.
Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like,
but we're in need of advise from those with more experience with these
matters.
Thank you for your time.
MarkMark S. wrote:

> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
This doesn't make much sense to me. As described it isn't clear whether
trackingid is a surrogate key or not. If it isn't then I don't
understand what its purpose is. What is the business meaning of
trackingid? Why isn't the datatype predetermined? What key or keys
exist in this table?
Maybe we are talking surrogate keys here, in which case see:
http://www.aspfaq.com/show.asp?id=2504

> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
A guiding in principle in data warehouse applications is usually to
capture the data at the finest possible level of granularity. I expect
you've considered this, but I just thought it worth restating for the
benefit of all.
David Portas
SQL Server MVP
--|||Who or what determines the value of TrackingID?
If this key is to uniquely identify an event (such as a support call), then
perhaps (8 byte) datetime would be the logical choice. It has inherent
meaning, and unless you have hundreds of support calls coming in per minute,
it is unlikely that this key value would be duplicated, and retry logic on
the insert procedure or statement can handle the unlikely event that does.
There may even be a need to purge the tbTracking table based on < a specific
date/time rather than truncating the entire table at the end of the day.
As for ProductID, this should be an existing attribute in your database.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||In my experiences, int data type is the best performer especially when it
comes to indexing, joins, etc. (This comment relates to 32-bit O/S and SQL
Server 2000.)
I would make a meaningless key:
TrackingRowId int, identity, primary key, clustered
ProductRowId int, identity, primary key, clustered
SQL Server takes care of assigning the next number. The clustered index
will always insert data at the end of the data pages which avoids page split
s.
If you want to reset the tracking id each midnight, simply change the reseed
the tables as part of your processing.
Personally...
I would NOT use a character string for a key because its slower to compare
strings.
I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
butt when you have to write a manual query to pull data.
Finally, I have to reiterate David's point of capturing data at the lowest
level. Are you sure you want to summarize and truncate on a nightly basis.
What if a bug is discovered? You won't have the original data to recreate
the totals. What if a new way of summarizing is added? You won't have the
original data to drill into or summarize.
And, to reiterate JT's point of having a datetime column that can be very
handy. It is debateable whether to use it as the key or not. If you're
truncating the tables each night, it may be a waste.
Just my two cents,
Joe
"Mark S." wrote:

> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
>
>|||Gentleman thank you for your feedback.
Joe, other than the UniqueIndentifier being unfriendly for humans, do you
find it faster than INTs?
As to th other questions and caveats, all that has been considered
previously, and wasn't mentioned in my question in the interests of brevity
and an attempt to focus my question on a single point. Regardless, thank you
for your full consideration.
Cheers,
Mark
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:50A0324B-1446-4044-A850-706B59A04169@.microsoft.com...
> In my experiences, int data type is the best performer especially when it
> comes to indexing, joins, etc. (This comment relates to 32-bit O/S and
> SQL
> Server 2000.)
> I would make a meaningless key:
> TrackingRowId int, identity, primary key, clustered
> ProductRowId int, identity, primary key, clustered
> SQL Server takes care of assigning the next number. The clustered index
> will always insert data at the end of the data pages which avoids page
> splits.
> If you want to reset the tracking id each midnight, simply change the
> reseed
> the tables as part of your processing.
> Personally...
> I would NOT use a character string for a key because its slower to compare
> strings.
> I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
> butt when you have to write a manual query to pull data.
> Finally, I have to reiterate David's point of capturing data at the lowest
> level. Are you sure you want to summarize and truncate on a nightly
> basis.
> What if a bug is discovered? You won't have the original data to recreate
> the totals. What if a new way of summarizing is added? You won't have
> the
> original data to drill into or summarize.
> And, to reiterate JT's point of having a datetime column that can be very
> handy. It is debateable whether to use it as the key or not. If you're
> truncating the tables each night, it may be a waste.
> Just my two cents,
> Joe
> "Mark S." wrote:
>|||The last line of the article David suggested
http://www.aspfaq.com/show.asp?id=2504
Says alot about GUI() not being optimized, if anyone disagrees, feel free to
speak up:
"the wider datatype leads to a drop in index performance (if clustered, each
insert almost guaranteed to 'dirty' a different page), and an increase in
storage requirements; " and five other cons.
Thank you.|||To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
WITH FILLFACTOR = 100
This causes every new row to be added at the end of the table--minimizing
index maintenance and eliminating page splits. Use caution, however: since
SQL Server doesn't automatically reorganize indexes, a high volume of DELETE
activity will cause the index to become sparse, which can reduce SELECT
performance, but it will not affect INSERT performance.
The INT datatype matches the word size of most Intel processors (32-bit), so
comparisons require fewer CPU cycles.
The stored procedure is an example of what not to do. It's a recipie for
primary key constraint violations. There's nothing that prevents two
transactions from trying to INSERT the same information at the same time.
Most of the time what will happen is that one connection will succeed with
the INSERT and the other will UPDATE the newly inserted row, but a collision
will occur if identical UPDATE statements occur simultaneously on two
unbound connections followed by (since the row doesn't yet exist)
simultaneous identical INSERT statements.
The correct method is to use something like this:
BEGIN TRAN
IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
UPDATE
ELSE
INSERT
COMMIT
Some people use the following instead, but I prefer the above method since
it is easier to read and understand
BEGIN TRAN
INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
IF @.@.ROWCOUNT = 0
UPDATE
COMMIT
Note that there isn't any marked reduction in performance or concurrency
between this and your sample, because UPDLOCK doesn't block SELECTs, and any
blocking that does occur is necessary to maintain integrity. Without the
EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
anyway which involves reading the index page into memory. With the EXISTS
clause, the SELECT reads the index page and applies an update lock, and the
INSERT or UPDATE simply transition from an update lock to an exclusive lock
in memory--no additional physical read is necessary.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||Thank you very much.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23oG2PTDDGHA.3980@.TK2MSFTNGP12.phx.gbl...
> To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY
> CLUSTERED WITH FILLFACTOR = 100
> This causes every new row to be added at the end of the table--minimizing
> index maintenance and eliminating page splits. Use caution, however:
> since SQL Server doesn't automatically reorganize indexes, a high volume
> of DELETE activity will cause the index to become sparse, which can reduce
> SELECT performance, but it will not affect INSERT performance.
> The INT datatype matches the word size of most Intel processors (32-bit),
> so comparisons require fewer CPU cycles.
> The stored procedure is an example of what not to do. It's a recipie for
> primary key constraint violations. There's nothing that prevents two
> transactions from trying to INSERT the same information at the same time.
> Most of the time what will happen is that one connection will succeed with
> the INSERT and the other will UPDATE the newly inserted row, but a
> collision will occur if identical UPDATE statements occur simultaneously
> on two unbound connections followed by (since the row doesn't yet exist)
> simultaneous identical INSERT statements.
> The correct method is to use something like this:
> BEGIN TRAN
> IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
> UPDATE
> ELSE
> INSERT
> COMMIT
> Some people use the following instead, but I prefer the above method since
> it is easier to read and understand
> BEGIN TRAN
> INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
> IF @.@.ROWCOUNT = 0
> UPDATE
> COMMIT
> Note that there isn't any marked reduction in performance or concurrency
> between this and your sample, because UPDLOCK doesn't block SELECTs, and
> any blocking that does occur is necessary to maintain integrity. Without
> the EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
> anyway which involves reading the index page into memory. With the EXISTS
> clause, the SELECT reads the index page and applies an update lock, and
> the INSERT or UPDATE simply transition from an update lock to an exclusive
> lock in memory--no additional physical read is necessary.
> "Mark S." <marks@.yahoo.com> wrote in message
> news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
>

best bulk insert command

I have a 5 million row table that gets truncated and new values get imported
.
The new values are obtained by values that have changed in other tables.
Does anyone know the quickest way this can be acheived.
I have tried this took around 34mins
insert in attritable (attrivalue,attri_id,desc)
exec sp_insert
then
Theres a job that bcps the values out to text file in batches of 5000 ..then
inserts them into the table again in batches of 5000 and this takes around 2
8
mins.
Even though BCP is quicker it seems a waste to do it this way and more prone
to errors....Is BCP definately the quickest way to enter data this way does
anyone know'
Thanks for any help or suggestions
Sammy> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
Instead of returning a result set that you insert, consider changing
sp_insert to create the new table with SELECT ... INTO and then create
constraints and indexes.

> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
Bulk Insert methods like command-line BCP, Transact-SQL BULK INSERT, DTS and
bulk copy APIs are the fastest way to get external data into SQL Server.

> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
This calculates to about 3000 rows per second. Not as fast as I would
expect with a narrow table on modern hardware (10,000+) but a lot depends
the size of your data and the kind of indexes you have on the table. You
may find it faster to drop indexes and recreate afterward. See Optimizing
Data Loads at
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx.[/url
]
Hope this helps.
Dan Guzman
SQL Server MVP
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0B51C58B-7E10-4905-98CB-74CAA211622B@.microsoft.com...
>I have a 5 million row table that gets truncated and new values get
>imported.
> The new values are obtained by values that have changed in other tables.
>
> Does anyone know the quickest way this can be acheived.
> I have tried this took around 34mins
> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
> then
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
> Thanks for any help or suggestions
> Sammy
>
>
>
>
>
>
>
>