StrataFrame Forum

Best Practice for UpdateConcurrencyType?

http://forum.strataframe.net/Topic16178.aspx

By Edhy Rijo - 5/6/2008

Hi,

What would be the best option to use between the RowVersion and TimeStamp for checking the concurrency type and why?

By Trent L. Taylor - 5/6/2008

Both are far faster than using AllFields for concurrency checking, but the fastest will always be the Integer row version versus the time stamp.  Dates and Times are slow to query on, even when pulling back a single record, they are not nearly as fast as testing on a single integer field.  The reason is that anytime a test is performed on a DateTime with a > or < in the query, the execution path must first perform some internal sorting, so it is not as fast as just testing with a > or < on an integer.

In most cases, though, the performance difference between the two will be negligible so it would become a preference at this point.  But I am always of the mind to go with a better performing solution, no matter how small, when I have a choice...so integer row version fields are always our choice.

By Edhy Rijo - 5/6/2008

Thanks a lot Trent. Hehe
By Peter Jones - 5/6/2008

Hi Guys,

Not wishing to be pedantic here but a timestamp data type has nothing to do with date/time - it's just sequential binary number that is incremented by 1 every time a row is inserted or updated in the database - yes the database not the table the row is in. So, every row in the database with a timestamp column will always have a unique number in that column.

Cheers, Peter

By Trent L. Taylor - 5/6/2008

Peter,

You are right, but that was not my point.  The performance issue is not with the incrementing of the value...it is if it will be used within queries for any purpose.  It isn't a drastic difference, but I have actually set this up and dealt with this recently, so I know that the TimeStamp is slower than the integer if you will be performing any query operation on the WHERE side that includes a >, <, or a between operation.

It is not noticeable unless you are dealing with a larger data set.  If you are never going to have a a large database then it really doesn't matter...but I know for a fact that the integer is faster than the time stamp when it relates to queries when dealing with large data sets.  If you never do anything but an equal operation on the column then there will not be a noticeable difference whether you are using an integer or a TimeStamp column.  But why worry about all of this....if you create a integer row version column then you never have to worry with it regardless....but to each his own Smile  I was asked for best practices....and this would fall into that category in my book. Smile

By Trent L. Taylor - 5/6/2008

One other point here....if you actually wanted to include the concurrency field in any type of query, you are dealing with, as you said, binary data.  This becomes more difficult to include in queries if you are trying to determine something in regards to auditing, etc.  So if you were trying to filter out records to see only records that have been modified, then you would have to create a query using values like this:

SELECT COUNT(*) FROM MyTable WHERE MyTimeStampField <= 0x00000000000007D1

(Which obviously you would want to use a parm, but you get my point)

Versus using an integer value

SELECT COUNT(*) FROM MyTable WHERE MyTimeStampField <= 1

So anyway that I look at it...it is far easier to deal with an integer...at least in my simple world Tongue

By Peter Jones - 5/6/2008

Hi Trent,

Sorry if I gave the impression I was taking issue with your recommendation. Some developers do get into trouble thinking the timestamp is a 'timestamp' when its not - I was just making it clear what that misnamed data type actually is.

I actually went down the path of using timestamp for concurrency but changed to rowversion as a result of this thread: http://forum.strataframe.net/Topic7367-6-1.aspx?

Cheers, Peter

By Trent L. Taylor - 5/6/2008

I appreciate your input an making sure of the clarification.  When I first dealt with SQL Server I too first thought that a TimeStamp was DateTime related (though you may have known better)...and when re-reading my earlier post it did sound like I was referring to a DateTime field...when I said Date and Time I really meant DateTime and TimeStamp....so thanks for making sure that point was clear.