I'm creating a table to store serialized shopping cart data associated with a user account. I want to keep a LastAccessedDate column on it to see the last time it was retrieved by my application. I was considering using an UPDATE/OUTPUT statement but came across this similar StackOverflow question recommending storing the date column in a satellite 1:0..1 table. First, can someone speak to the premise that the entire row is updated when updating one column? Is this a good suggestion? What are the potential problems of it being in the same table? I'm I correct that by default, updating the column will lock that page so reads for other carts on that same page would have to occur one at a time? Lastly, if it is a good suggestion, to implement the 1:0..1, is it better for the second table to make the foreign key to the first it's primary key? Or for it to have its own primary key and a unique fk?
February 29, 2020 at 12:34 pm
There are a lot of assumptions baked into the questions. Why are you storing shopping cart information as serialized data?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
I think you are into over-engineering when you are considering to have a separate table. That would only make sense, if you are considering to track not only last accessed, but all accesses.
It is true, that updating one column will update the entire row, but the practical significance of this is less. Updating a datetime column in a narrow table or updating a datetime column in a wide table will produce the same amount of transaction log. Execution time can be expected to be the same. You talk about page locks, but the default locking level is row not page. You can get page locks, if SQL Server thinks that will be more economical, but give that these should be point updates on a primary key, this is not going to happen here.
And - if you really have locking on page, the solution with a separate table would actually be worse for concurrency than having the column in the main table. Because a LastAccessed table would be a lot more narrow, there would be more rows on a single page, and more shopping-carts would be blocked.
If you would make such table the PK in the LastAccessed table should of course be an FK to the ShoppingCart table. There is no reason at all to have a separate ID.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 2, 2020 at 8:34 pm
Erland, great! Thank you. I was misremembering the default locking; it's been years since I've read about that and Sql Server & DB admin and programming is obviously not my focus.
There are a lot of assumptions baked into the questions. Why are you storing shopping cart information as serialized data?
I think the shopping cart data is irrelevant to the question, which is my fault for including it in the first place, but to answer your question: in the past we have stored a shopping cart in a user's session state of their web session. We're upgrading to allow the cart to persist with a user's account across sessions and devices. We foresee no need or benefits to spending the time normalizing, building the schema, and writing all the IO for it for our use cases.
March 2, 2020 at 9:43 pm
First, can someone speak to the premise that the entire row is updated when updating one column?
No. Only the modified column(s) are updated.
Is this a good suggestion? What are the potential problems of it being in the same table? I'm I correct that by default, updating the column will lock that page so reads for other carts on that same page would have to occur one at a time?
I don't believe an update will need to lock the entire page, or, if it does, certainly not for very long. I don't think this will cause you an issue.
Lastly, if it is a good suggestion, to implement the 1:0..1, is it better for the second table to make the foreign key to the first it's primary key? Or for it to have its own primary key and a unique fk?
Even though I don't believe it's a good suggestion, I'll answer this q anyway. I agree 100% with Erland: just use the PK from the first table as the "co-table"'s key. I do use 1-0/1 tables, but only ever use the original key as the key to the other table(s).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 2, 2020 at 10:00 pm
I should add one more thing. I conducted some tests to back what I said, and they gave me some results that it took some time to digest. But there is in fact a small argument for having that column in a separate table, particular if the other table is really wide. When updating the data file, SQL Server writes the entire page to disk. If you have small narrow table, there can be many updated rows in the same page, so that in total fewer pages have to be updated.
But I still don't think this is an argument for making this design from the start. In my test, I have a narrow table with an id and a datetime column, and a wide table which also had a char(7200). I saw a performance benefit for the narrow table - but only when I had the database on a slow spinning disk. There was no particular difference when the database was on an SSD.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 2, 2020 at 10:25 pm
My inclination would be to move the char(7200) to a different table. Or, depending on the specific circumstances, change it to varchar(max) and force it LOB (out of the main table data).
Many places tend to have overly-wide tables because they never do actual data normalization up front. They kinda pretend to, but they don't really. They just slap an identity column (technically a column with the identity property) on every "table" and start stuffing data in, with no real forethought. This inevitably leads to a royal mess in all tables, since it is literally impossible to normalize based on an identity.
Similarly, since normalization is (supposed to be) a logical design process and not a physical one, identity property does not even exist then (the physical implementation might be autonumber or using a sequence, or indeed an identity, but that is not relevant to the logical design, it is simply a unique number at that stage).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 2, 2020 at 10:30 pm
In my case the char(7200) was just a filler to the sake of the test. Let it represent some umpteen other columns. Probably not totalling 7200 bytes, but maybe a couple of hundred. In this the post has a serialised shopping chart, so I guess the main meat of this table is just one fat XML/JSON column and not much more.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 3, 2020 at 4:48 am
First, can someone speak to the premise that the entire row is updated when updating one column?
No. Only the modified column(s) are updated.
We can certainly prove that with the COLUMNS_UPDATED function but it does make you wonder about the INSERTED and DELETED virtual tables that are formed during (especially) an UPDATE.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2020 at 3:16 pm
ScottPletcher wrote:First, can someone speak to the premise that the entire row is updated when updating one column?
No. Only the modified column(s) are updated.
We can certainly prove that with the COLUMNS_UPDATED function but it does make you wonder about the INSERTED and DELETED virtual tables that are formed during (especially) an UPDATE.
I don't think they're so much formed as available to be read, if needed, from existing buffers or data blocks (or at least that's my understanding). SQL doesn't need to form actual tables for the inserted and deleted views.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 3, 2020 at 3:24 pm
The inserted/deleted tables are created from the version store in tempdb.
So that is indeed one more input to the original question. If you are running with any sort of snapshot - and that includes when you have an AG with a readable secondary - versions of the rows are written to tempdb. I don't know if there is any optimization here, but I don't really see how that would be done. Thus, again, there can be a small benefit of moving this column to a separate table. But again I don't think it is worth the complexity.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 3, 2020 at 3:31 pm
I don't believe the inserted table (view), which is current data, could be created from a version store, which is historical data.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 3, 2020 at 8:49 pm
Find an idle, freshly started instance. Run this:
SELECT * FROM sys.dm_tran_version_store
to verify that it is empty. Then do something like this in a database that does not have any form of snapshot enabled:
SELECT * INTO testie FROM Northwind..[Order Details] WHERE 1 = 0
go
CREATE TRIGGER slask_tri ON testie FOR INSERT AS
SELECT COUNT(*) FROM inserted
WAITFOR DELAY '00:00:30'
SELECT COUNT(*) FROM inserted
go
INSERT testie SELECT * FROM Northwind..[Order Details]
Then run the query against sys.dm_tran_version_store again. The result is no longer empty.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply