How to handle concurrency issue with better performance?

[cherry]

I am now thinking a method to handle the concurrency issue in my
program.
In .Net, the Command Builder Class will generate SQL automatically in
which it compares all the fields' original version with the version in
database as the update criteria and thus avoid overwriting others'
data.

In my case, I have a dataset retrieving data from joining several
tables and thus encounter error when use the DataSet.Update() method
of adapter directly.(cannot generate sql coz more 1 table in the
select command).
Therefore, I create stored procedure for the update command and assign
them to the dataset so that it can do the update. However, using the
concept of commandbuilder to handle concurrency issue, I have to
passing all the fields' original version to the stored procedure as
the where conditiion, the number of parameters might rise to about 30
to 40.
Will the preformance (esp. client) downgrade very much in using such
update method?

I was suggested by colleague to use 2 datasets, one is that I am using
right now and another will act as a image, storing the only table
which will be updated. So that when saving data to the database, I can
use the image DataSet's Update Command directly and needn't pass so
many parameters. But it seems that the synchonization between these 2
dataset is quite troublesome!

How you guys think? Which is better? or there is some better solution
which I may not know?
I am using VS.Net 2003 Enterprise to do the development, language is
VB .Net and the Database is Oracle 10g.
People using the application ~10-20, not a big number but the
performance of application must be as fast as possible due to the
business requirement.

Thanks in advanced.

Regards,
Cherry

 

[RobinS]

I use .Net 2.0 and VS2005, but I'm going to take a stab at answering your
question.

When you do the update on the dataset, it will only update the records that
have been modified since they were pulled from the database. So you don't
need to keep a separate table of those.

Instead of checking all of the fields for changes, what many people do is
store a timestamp in the database for the last update date/time. When doing
your update, you check to see if that value is the same, and if it not,
somebody else has updated the record since you pulled it. In this way, you
can have the WHERE clause on your update use the time stamp and the primary
key fields.

Hope this helps.
Robin S.

 

[Brooke]

Would you put the timestamp check in the stored procedure on the server
side, or in the client side c# code?

 

[William \(Bill\) Vaughn]

This question has been asked (and answered) many, many times here and in my
(and other's) books over the years.
The CommandBuilder (CB) is a mechanism with many limitations. As you have
found, it fails to deal with more sophisticated designs--all too often, even
those as simple as yours. Yes, many developers have migrated to stored
procedures which use TimeStamps to monitor concurrency--this approach is far
faster and easier to code. The newest CB also uses this approach as well
(ADO.NET 2.0 and VS2005).

As I describe in my latest book (if I may be so bold to mention it again),
the real solution is often in the design of the application and the way it
accesses data. Collisions occur because your design permits more than one
application (even the same application) to access the same row at the same
time--where both applications try to change the row. This is like designing
traffic intersections without signals and worrying about where to put the
ambulances and fire trucks to deal with the collisions. We all work with
relational database designs and more importantly DBAs that don't expose the
base table for a litany of reasons. Creating applications that party down on
these base tables is a plan for disaster. One morning you'll wake up with a
terrible security headache that can't be cured with a glass of tomato juice
and a raw egg.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[William \(Bill\) Vaughn]

In the SP that handles the updates.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[RobinS]

Creating applications that party down on
these base tables is a plan for disaster. One morning you'll wake up with
a terrible security headache that can't be cured with a glass of tomato
juice and a raw egg.

LOL. Great turn of phrase.

Robin S.
----------------------------------------

 

[cherry]

Thanks all.
Right now I am thinking whether I can use a existing DateTime column
(ie last_update_date) as the control instead of creating a new
TimeStamp.
In this way, I can save the storage (there are quite many records) and
the time in creating the column (and also quite many tables), right?
The result of using datetime or timestamp should be the same, right?
will there be any potential risk?

Thanks and Regards,
Cherry

 

[William \(Bill\) Vaughn]

Keep in mind that a timestamp column does not store a date/time value. When
the INSERT ads a new row, two rows can be assigned the same datetime value
from GetDate().


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Thanks all.
Right now I am thinking whether I can use a existing DateTime column
(ie last_update_date) as the control instead of creating a new
TimeStamp.
In this way, I can save the storage (there are quite many records) and
the time in creating the column (and also quite many tables), right?
The result of using datetime or timestamp should be the same, right?
will there be any potential risk?

Thanks and Regards,
Cherry

 

[cherry]

Then for timestamp, it will be unique? Is it a automatic updated field
in table?

In my plan, I will use SP and dataset.
when I am updating a record, i will compare the last updated date in
the database with the original last updated date value in the row of
the dataset.
So, it can prevent one from overwriting others' changes...of course,
the violation throwing exception should be handled.

Regards,
Cherry

 

[William \(Bill\) Vaughn]

Timestamps are guaranteed unique. they are simply an auto-incrementing
integer value. They are designed to determine if the row was changed since
the last time the row was read.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Then for timestamp, it will be unique? Is it a automatic updated field
in table?

In my plan, I will use SP and dataset.
when I am updating a record, i will compare the last updated date in
the database with the original last updated date value in the row of
the dataset.
So, it can prevent one from overwriting others' changes...of course,
the violation throwing exception should be handled.

Regards,
Cherry

 

[Jim Rand]

Timestamps are great. Cast them as integers and use them in the where
clause.

------------
StatusLookup
------------

SELECT StatusLookupID, Status, Office, EndUser, Consultant,
ConsultantOffice, LastUpdated, LastUpdatedBy, CAST(TS AS INT) AS TS FROM
AgencyNET.StatusLookup

INSERT INTO [AgencyNET].[StatusLookup] ([Status], [Office], [EndUser],
[Consultant], [ConsultantOffice], [LastUpdatedBy]) VALUES (@Status, @Office,
@EndUser, @Consultant, @ConsultantOffice, @LastUpdatedBy);SELECT
StatusLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.StatusLookup WHERE
(StatusLookupID = SCOPE_IDENTITY())

UPDATE [AgencyNET].[StatusLookup] SET [Status] = @Status, [Office] =
@Office, [EndUser] = @EndUser, [Consultant] = @Consultant,
[ConsultantOffice] = @ConsultantOffice, [LastUpdated] = getutcdate(),
[LastUpdatedBy] = @LastUpdatedBy WHERE (([StatusLookupID] =
@Original_StatusLookupID) AND (CAST(TS AS INT) = @Original_TS));SELECT
CAST(TS AS INT) AS TS FROM AgencyNET.StatusLookup WHERE (StatusLookupID =
@StatusLookupID)

DELETE FROM [AgencyNET].[StatusLookup] WHERE (([StatusLookupID] =
@Original_StatusLookupID) AND (CAST(TS AS INT) = @Original_TS))