To simplify the situation, I will only consider one large table...
On a nightly basis a store will send all the new and changed data for one day for a large table to the head office. (this portion is good) Also, the store sends a summary of that table for the last 30 days to the head office for that large table.
At the head office, The new and changed data is updated in the large table (no problem here). The summary of the last 30 that is received and is uploaded into a table. It is then compared to a query that summarizes the data at the head office of this very large table (that contains all stores) for that same store.<-- this is where the problem is. This is done to be able to make sure that the store's data matches the head office data for that store (we get a warning if it doesn't match for which they need to take action)
The problem is the the summary query takes too much time... I'm looking to change the way we compare the store table with the host table in a more efficient way.
I tried the indexed view and the results were great but the fact that they have too many limitations, it makes it practically impossible to implement it on a large scale (to all software owners, cash registers, Stores and Head offices) due to different structures and different versions of our software.
I've been trying to think of different ways I can insure that the data of a table (for at least the last 30 days) for a store matches the head office but I feel like I'm turning in circles... So I'm looking for ideas to help me look at this differently.
Limitations: We use SQL Express at the stores, and usually standard at head offices. There's no direct connection between both databases (the data is transferred through files).
Any help is appreciated. thank you
Added more info: Structure of the table (I know is not ideal, it's what I inherited): Date, Store, terminal, transNum, lineNum, Qty, Amount + 194 MORE COLUMNS. The PK and clustered index is: Date, Store, terminal, transNum, lineNum
The query to summarize is simple:
Select Date, Store, sum(Qty) as Qty, sum(Amount) as Amt from MyHugeTable where date between '2017-07-22' and '2017-08-22' and store = '1234' group by Date, Store;