On a daily basis, I am adding values from the following table (
Id Name Value 1 'Name 1' 789 2 'Name 2' 789 3 'Name 3' 789
To the text field (
Values) in the following table (
table2), with a comma:
Id Name Values 1 'Name 1' '123,456,789' 2 'Name 2' '123,456,789' 3 'Name 3' '123,456,789'
(So on day one,
'123' was added. On day two,
'456' was added et.c.)
This is to have a daily history of values, over the last year. The idea of having a "flat" history field (rather than a table with an entry per day) is to have fast reads:
- I'm reading a lot from that table
- I need all 360 days for every read
- I'm writing once a day
- I don't need to query the values
I currently have ~32 million rows in each of the two tables above.
Question #1: is this a good way at all of solving my problem of having to display daily values over a year, with fast reads and slower writes?
Question #2: assuming this is an acceptable way of solving the problem - what is the most efficient way of executing the above concatenation? My current solution is to do a
MERGE that selects all entries from
table1 as a source:
MERGE table2 AS [Target] USING(SELECT Name, Value FROM table1) AS [Source] ON [Target].Name = [Source].Name WHEN MATCHED THEN UPDATE SET [Target].Values = [Target].Values + CONVERT(varchar(12), [Source].Value) + ',' + ',' WHEN NOT MATCHED THEN INSERT(Name, Value) VALUES([Source].Name, CONVERT(varchar(12), [Source].Value) + ',');
The reason I am asking these questions is that this takes several hours to run and I'm hitting
cannot obtain a LOCK resource at this time errors.
UPDATE #1: With the very useful comments I got on this question, I learned that the "flat" model I'm using will probably not have any effect on performance as SQL Server reads per page and not per row. Based on that, I'll re-model everything into a more traditional relational structure: one row per entry.
I had to get things working ASAP though, so as a band aid I wrote a stored procedure that updates the flat model without locking. In stead of using a
MERGE, it iterates over each row in
table1, and for each row, upserts (
UPDATE followed by a
IF (@@ROWCOUNT = 0) with an
INSERT) the corresponding
table2 entry. It takes the same amount of time to run, but uses half as much CPU (interesting) and doesn't lock.
Thanks everyone for your inputs.
The stored procedure (
Values will have to be renamed):
-- row ID for iteration declare @id int select @id = min(id) from table1 -- field variables declare @name varchar(128), @value int -- iterate through all table1 entries while @id is not null begin -- select next table1 entry select @name = Name, @value = Value from table1 where id = @id; -- update corresponding table2 entry update table2 set Updated = getdate(), Values = Values + CONVERT(varchar(12), @value) + ',' where Name = @name -- if no rows were updated, there is entry for this name; insert it instead if (@@rowcount = 0) begin insert into table2 (Created, Updated, Name, Values) values ( getdate(), getdate(), @name, CONVERT(varchar(12), @value) + ',') end -- update @id to select next row select @id = min(id) from table1 where id > @id end