3

On a daily basis, I am adding values from the following table (table1):

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 (Name, Value and 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
  • 2
    What makes you think having a "flat" column in your table will enable you to get fast reads? A properly formatted query with a great execution plan with joins, in my opinion, will be a lot faster than what you are attempting to do. The update statement is putting a lock on the table so nothing else can touch it while you are performing your conversion process. Now with that said. If it were me I would build a dynamic t-sql statement, build your string, and then perform the update against the records you want update That way you aren't doing a select and an update the same time. – H.79 Dec 3 '19 at 10:09
  • @H.79 I was under the impression that selecting a single row by ID (with all the data needed) would be faster than selecting 360 rows (with the same amount of data returned as the flat model, ideally). Is that not so? I could try and do a statement that iterates though every row in table1 in stead, and update table2 row by row - is this what you mean? You could post an example as an answer? – Scarabas Dec 3 '19 at 10:29
  • 1
    >>>faster than selecting 360 rows<<< Your 360 rows is not a "volume", and all of them fit in one database page, and server does not read "row by row" but PAGES as minimum – sepupic Dec 3 '19 at 13:17
  • In that case I'm glad I posted this, as I've learned something! So you're saying that splitting it up into 360 rows with a date for each will be at least as fast a read? Easier to maintain for sure. – Scarabas Dec 3 '19 at 13:37
  • @Scarabas - Welcome to the world of SQL buddy! :) Always something new to learn and improve on. I wish you luck and success on your venture here. Please post your updated code so we can review it, and learn together. – H.79 Dec 3 '19 at 19:11
0

The suggestion to have one row per value is good advice. It should be the starting point for any relational database design. To ensure all rows are close together (on the same or at least adjacent pages) you will have to have the clustered index on (ID, Date) (or on (Name, Date) depending on which you've chosen for the PK). Next you'll need to make sure there's room for newly-arriving rows beside their siblings. You can do this in one of two ways. One - by setting PAD_INDEX and FILLFACTOR to reserve that space when the index is created. Two - define the index with little or no free space reserved and rebuild it regularly.

That said, what you're proposing is not silly. As long as VALUES is treated as a single attribute and no query tries to dissect it the design is first normal form.

Why would you write to table1 then try to re-write into table2? I'd upsert (i.e. MERGE) into table2 directly, as each value comes into the system.

It seems your primary concern is that the target table is being locked, preventing concurrent work from proceeding. This is caused by lock escalation. The solution is to process the writes in smaller batches. The optimal batch size can only be found by experimentation, but usually a few thousand at a time is about right. I've an answer on this here.

The MERGE statement has had some problems. I haven't checked if they've been fixed recently (& I'm not sure what version you're running) so be aware and read up on the work-around. I propose the following alternative using newer functions which may simplify syntax:

declare @Target table (ID int, x varchar(99));
declare @Source table (ID int, x varchar(99));

insert @Target values (1, 'a'), (2, 'b,c'), (3, 'd');
insert @Source values (1, 'w'), (2, 'x'), (4, 'y');

-- UPDATE @Target with these rows:
select
    t.ID,
    CONCAT_WS (',', t.x, s.x)
from @Target as t
inner join @Source as s    -- INNER JOIN ensures there's a row in both tables
    on s.ID = t.ID

-- INSERT @Target with these rows:
select
    s.*
from @Source as s
where not exists     -- ensures only new rows are attempted
(
    select 1
    from @Target as t
    where t.ID = s.ID
);

Wrap this in the batching technique I suggested above. The downside is a second scan of the data. As the INSERT and UPDATE branches are disjoint sets they could be run in separate, concurrent jobs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.