I have this scenario. Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, that same table contains the same info from all stores which makes it huge. The data is transferred through files as there isn't any direct connections between the HO and the stores.
I'm trying to find an efficient way to make sure that the data from each store has made it safe a sound to the head office table. To do this I have tried to do a
checksum_AGG of a checksum for the fields that should match between the store and the HO for a specific Day for a specific store. But the performance was poor. I've also tried the sum of specific numeric columns and do the same at host for a specific Date and store to compare but the performance seems to be poor also.
I've tried to use a persisted computed column to calculate a row's hashbytes and maybe use that but unfortunately, I couldn't, since the table was created with
ansi_nulls off, which is a restriction of the persisted computed columns. And it's unthinkable to fix that issue on a very large scale of stores and HOs. I also thought of creating a trigger to fill any type of checksum per row, but the reality is that I will need to do this on multiple tables (maybe 50 tables) and to add triggers on every table will be very heavy, especially in OLTP system at the stores.
I have verified the fragmentation of the indexes and all is good.
Ideally it would be great if I transfer all the data from the store line by line and compare it against the HO but it's unthinkable since it's way too big.
I'm looking for ideas that I can explore to see if performance is acceptable. The idea is to get some type of checksum of a table per date (store/date) from a store and compare it with the same operation at the HO. This means that I would only need to transfer a checksum value per store and date to the HO. The solution must be compatible from SQL Server 2008 up until the latest SQL Server version.
More info as requested The sample table that I've been working with is one from a HO for my tests... Size: 160Gb for 105 stores and 633 days (200 columns... I know... not ideal but it's what it is and practically impossible to change for all stores and HOs)
Example of queries tried to create a daily checksum per store:
select [trsDate],[store],CHECKSUM_AGG(checksum(amt,qty,total)) from BigTable where store='111' AND trsDate BETWEEN '2016-01-01' and '2016-01-30' group by [trsDate],[store] order by [trsDate],[store] SELECT trsDate, store,'BigTable', amt=SUM(ISNULL(amt,0)), qty=SUM(ISNULL(qty,0)), total=SUM(ISNULL(total,0)) FROM BigTable WHERE trsDate BETWEEN '2016-01-01' and '2016-01-30' AND store='111' GROUP BY trsDate,store select [trsDate],[store],CHECKSUM_AGG(checksum([trsDate],[store],terminal,trsNumb,linenumb)) from BigTable where store='111' AND trsDate BETWEEN '2016-01-01' and '2016-01-30' group by [trsDate],[store] order by [trsDate],[store] SELECT [trsDate],[store], HASHBYTES('SHA2_512', concat(COUNT(*),CHAR(26), SUM(amt+qty+total)) from BigTable where store='111' AND trsDate BETWEEN '2016-01-01' and '2016-01-30' group by [trsDate],[store] order by [trsDate],[store]
Execution plan shows an index Seek 100%... Adding an index to Store\Date and include amt,qty and total has helped drastically but I have to investigate the impact of this index for the thousands of transaction processed and transfer.
Ideally I would love to have a checksum on all columns but the more I dig the more I realise that it will practically be impossible (otherwise I would need to double the size of each table by putting all columns in the indexe's include)