2

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;
Community
  • 1
JohnG
  • 1,081
  • 1
  • 11
  • 26

2 Answers2

4

If speeding up this query is critically important, I'd consider creating a covering index:

CREATE INDEX IX_MyHugeTable_Date_Store_Qty_Amt
    ON MyHugeTable (Date, Store)
       INCLUDE (Qty, Amt)
;

Adding a new index will impact the time to add, update, and delete rows from the table. You should test to determine the impact of adding this index to the nightly update process. It won't help if this speeds up the query generating the summary data for each day, but the slowdown in inserting and updating daily changes is slowed down more than the summary query is sped up. This would hold true with some of the other suggestions from the comments; test to make sure that the changes you make do not harm normal operations.

FYI: the idea behind a covering index is simple - if an index has all the columns a query refers to, then the engine can retrieve that information from the index without actually touching the table itself. This index should take up much less space per row than the table (with around 200 columns), so the query should perform much better.

As noted by David Browne, your index would include not just the four columns listed, but the other 3 keys that make up the primary key. That's because all non-clustered indexes on a table with a clustered index use the cluster key to identify the row location in the main table. See this link for full details. Still, the index will be much narrower than your ~200 column table.

RDFozz
  • 11,631
  • 4
  • 24
  • 38
3

This is a supplemental answer and I'm making some assumptions here, but it looks like you're trying to reduce the time it takes to complete this process overall. In that case you shouldn't limit yourself only to figuring out how to reduce the summary query. I'm not saying you shouldn't prioritize it, but there may be other steps of your process where you can save additional time minimizing how much performance you need to squeeze out of the summary query.

I would suggest you upgrade your environment to SQL 2016 SP1 or later if you've not already done so. This opens up a lot of functionality you can use (even with Express edition) that will likely help with optimizations, such as Table Partitioning, Table Compression, and/or Columnstore Indexing. These features can be used individually or in conjunction with one another and should provide some performance improvements so long as you're not currently running up against a CPU bottleneck in your environment.

You may also be able to improve your ETL import processes. This article, Guidelines for Optimizing Bulk Import, from Microsoft goes over some concepts that may apply to your scenario. There's a lot of information in it regarding the Bulk Logged Recovery Model, and if you think of using it, I will also point you to Considerations for Switching from the Full or Bulk-Logged Recovery Model which goes over the proper way to switch between the Full and Bulk-Logged Recovery models.

There's a lot here, so again, this isn't an answer to your immediate issue so much as an attempt to show you some other areas you can further improve upon down the road.

John Eisbrener
  • 9,427
  • 6
  • 29
  • 63