2

I have a highly "vertical" database, and I've written a dynamic pivot script to build a new time-indexed table that I can actually query on human time scales. Pivoting the table takes a few minutes per hour of data.

I would like to keep the new table up-to-date in an efficient way, and I was thinking of writing a script that executes every minute to pivot data, append to the new table, and delete the original entries. I want to keep the new data forever.

Are there any SQL server 2014 features/pitfalls I should be aware of, and is there a better path than the one I'm about to head down? I've only been scripting in SQL for about 48 hours now...

Thanks everyone.

Original Data:
10:30:00 A 1.0
10:30:01 A 1.1
10:30:02 A 1.2
10:30:00 B 2.1
10:30:01 B 2.3
10:30:02 B 2.5
10:30:00 C 3.4
10:30:01 C 3.3
10:30:02 C 3.2

New table:
           A    B    C
10:30:00  1.0  2.1  3.4
10:30:01  1.1  2.3  3.3
10:30:02  1.2  2.5  3.2

In this example, my question is how to deal with 10:30:03 data efficiently when it arrives.

3
  • 2
    I think people may want to see the old and new table schema and the query to get a better understanding a provide a more relevant answer. May 13, 2016 at 14:51
  • Thanks for the suggestion, @CodyKonior. I added an example.
    – Bryan
    May 13, 2016 at 15:07
  • 1
    re " Pivoting the table takes a few minutes per hour of data" How many billions of (presumably un-indexed) rows are you adding every hour? That's seems unreasonably slow for modern hardware except in the most extreme instances. May 14, 2016 at 0:04

3 Answers 3

2

This is something that I recently did with a table of mine. I used CROSS APPLY and used this page as my guide. Perhaps this can help you?

2
  • Thank you, but this isn't my question - I've already done the pivot. The question is how to update the new table continually, forever, without re-pivoting all of the old data every single time.
    – Bryan
    May 13, 2016 at 15:22
  • Then maybe put the pivoted data in a new table and then use the EXCEPT function to find new records to be inserted? Edit: to clarify, you would use the EXCEPT function on the original table to find the new records to pivot.
    – Chance
    May 13, 2016 at 15:25
1

I think you have two options that might work for you.

  1. Trim new and re-insert.

    In the target table, find the oldest timestamp, delete those records from the target table, then insert all the new records from the source table to the new table.

  2. Don't insert all the records.

    If the concern is that your pivot process may not grab all the records for a speicific timestamp (because they are still inserting at the same time that you are reading from the table), then just step backwards by whatever duration you feel comfortable with. Grab all records that have come into the source table up to the prior minute, and push those to the destination table. If you run your insert process frequently enough, the users won't notice.

1

To make sure you pivot exactly the data that you want without overlooking new incoming data, there are two patterns:

Mark a batch for processing

A. Add a batch column to your source table. By default it's NULL (when data arrives).

B. When you're ready to pivot you update all NULL to a timestamp or a flag.

C. Transfer only stamped data to your pivot

D. Now in the source, either delete or mark this batch of data

This way you mark a batch of data to be processed. Newly arriving data will not be included in this batch but it will be included in the next batch

Using Output

The other approach is using OUTPUT to capture only the data you just processed

Again in this way you capture only the batch of data that you're working with

DECLARE @RecordBatch TABLE (Field1,Field2...)

INSERT INTO TargetTable (Field1,Field2...)
OUTPUT Inserted.*
INTO @RecordBatch
SELECT Field1,Field2...
FROM SourceTable;

-- Just the inserted records are captured 
-- Go mark or delete these in the source
DELETE SourceTable
WHERE Key IN (SELECT Key FROM @RecordBatch);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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