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;
  • 5
    lot of extraneous info in your question; sounds (to me) like this boils down to a performance issue with a query (the HQ summary report); I'd recommend rewriting this question (or delete and create new) based around the (poorly performing) query in question; perhaps take a look at How to create a Minimal, Complete and Verifiable example for some ideas on how to write up a new question – markp-fuso Aug 22 '17 at 14:47
  • @markp The query can't be more simple than it already is.. It is a simple select of that one table, using the PK/Clustered index to summarize 2 columns. and the where clause contains the 2 first columns of the PK/clustered index. The performance is due to the size of the table, the size of the selection. That's why I'm looking for different ideas to change the way this is done. – JohnG Aug 22 '17 at 15:00
  • 1
    Without a lot more details on the actual schema and data ... instead of constantly rolling up the last 30 days of detail data, how about generating daily rollups (eg, rollup the daily data received from stores), then your '30 day' rollup query would consist of rolling up the last 30 'daily rollups'; for example, over the next 30 days, instead of repeatedly rolling up the detail data for August 22nd, roll it up once (today), then reference this rollup for the next 29 days – markp-fuso Aug 22 '17 at 15:10
  • 1
    You could store the data in a Clustered Columnstore Index, which will give you much better compression, and the ability to perform column-wise queries (especially important for wide tables.) – David Browne - Microsoft Aug 22 '17 at 15:23
  • 1
    so what this comes down to is that HQ needs to know when the store changes old/past daily detail data; while a 30-day summary will point out an issue, at some point you need to drill down to the daily/detail data to find the issue; sounds like daily rollups would be more usable than 30-day rollups; also sounds like you need a new design to make sure local/store updates of past/daily data are properly pushed up to HQ (ie, find/fix the problem when it occurs vs trying to find the problem by constantly rolling up 30 days and then having to drill down to find the problem) – markp-fuso Aug 22 '17 at 15:24
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.

|improve this answer|||||
  • +1 I like this one... It's exactly wha I was in the middle of trying. Except the index that I built only included the 2 first columns of the clustered index and included the columns needed. I didn't know about a nonclustered index being better if the index key is unique. I read the article in the link you added but I still don't understand how it improves or degrades the query. Is it not recommended due to the fact that it would just make the index bigger in size? – JohnG Aug 22 '17 at 20:39
  • If my tests work this will be the answer I'm going with. Thanks – JohnG Aug 22 '17 at 20:49
  • To be honest, I included the link because it was part of David Browne's comment. The point is that, if a non-clustered index on a clustered table is declared as a unique index, then if you don't need the primary key columns as a part of the indexes columns, they'll only show up as leaf node data. If the index isn't declared, then the primary key columns will be treated like the other indexes columns, and will appear in the index more than just once per row. So, yes, it makes the index larger in size. – RDFozz Aug 22 '17 at 21:04
  • this solution brought my query time down from over one hour to 20 seconds... incredible! – JohnG Aug 23 '17 at 12:45
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.

|improve this answer|||||
  • +1 I will definitely take your ideas into consideration down the road, unfortunately we sale a software that needs to be supported by SQL 2008 and up. The table partitioning is definitely an avenue that we are discussing down the road. – JohnG Aug 22 '17 at 20:22
0

If you're attempting to see if data has changed, consider adding a rowversion column to each store table, then checking to see if that value changes compared to the value stored in the head quarters' table.

Below is a minimally complete verifiable example.

We'll run this in tempdb:

USE tempdb;

Here we'll create a dummy "store" table:

IF OBJECT_ID(N'dbo.StoreData', N'U') IS NOT NULL
DROP TABLE dbo.StoreData;
CREATE TABLE dbo.StoreData
(
    ItemDate datetime NOT NULL
    , Store int NOT NULL
    , Terminal int NOT NULL
    , TransNum int NOT NULL
    , LineNum int NOT NULL
    , QTY int NOT NULL
    , Amount int NOT NULL
    , rv rowversion NOT NULL
    , CONSTRAINT PK_StoreData
        PRIMARY KEY CLUSTERED
        (
            ItemDate
            , Store
            , Terminal
            , TransNum
            , LineNum
        )
);

Here we create a dummy "head quarters" table:

IF OBJECT_ID(N'dbo.HQ', N'U') IS NOT NULL
DROP TABLE dbo.HQ;
CREATE TABLE dbo.HQ
(
    ItemDate datetime NOT NULL
    , Store int NOT NULL
    , Terminal int NOT NULL
    , TransNum int NOT NULL
    , LineNum int NOT NULL
    , QTY int NOT NULL
    , Amount int NOT NULL
    , rv binary(8) NOT NULL
    , CONSTRAINT PK_HQ
        PRIMARY KEY CLUSTERED
        (
            ItemDate
            , Store
            , Terminal
            , TransNum
            , LineNum
        )
);

The HQ table above changes the definition of the rv column - instead of using a rowversion data-type, we'll use binary(8) since we don't want the value to change in the HQ table. The rv column in the StoreData table will automatically update any time the row is modified by store.

At the headquarters, We'll create a table where we keep track of the most recently used rowversion value for each store:

IF OBJECT_ID(N'dbo.StoreRowVersion', N'U') IS NOT NULL
DROP TABLE dbo.StoreRowVersion;
CREATE TABLE dbo.StoreRowVersion
(
    Store int NOT NULL
    , LastRowVersion binary(8) NOT NULL
);

Now, we'll add 1,000,000 rows into the StoreData table:

INSERT INTO dbo.StoreData (ItemDate, Store, Terminal, TransNum, LineNum, QTY, Amount)
SELECT TOP(1000000) o.create_date
    , 0
    , c2.column_id
    , c3.column_id
    , CONVERT(int, CONVERT(varbinary(8), CRYPT_GEN_RANDOM(8)))
    , 1
    , 2
FROM sys.columns c1
    CROSS JOIN sys.columns c2
    CROSS JOIN sys.columns c3
    CROSS JOIN sys.objects o;

And insert those same rows into the HQ table, to mimic the loading of data from the store to the headquarters:

DECLARE @MaxRV binary(8);
DECLARE @Store int = 0;
SELECT @MaxRV = MAX(rv)
FROM dbo.StoreData;

INSERT INTO dbo.HQ
SELECT *
FROM dbo.StoreData
WHERE StoreData.rv <= @MaxRV;

UPDATE dbo.StoreRowVersion 
SET LastRowVersion = @MaxRV
WHERE Store = @Store;
IF @@ROWCOUNT = 0 
BEGIN
    INSERT INTO dbo.StoreRowVersion (Store, LastRowVersion)
    VALUES (@Store, @MaxRV);
END

The above code should be implemented in a stored procedure with error handling and best-practices for managing a key table, per the answers on this question

Next, we'll have the "store" modify a row:

UPDATE dbo.StoreData
SET QTY = 4
WHERE LineNum = (SELECT TOP(1) LineNum FROM dbo.StoreData);

And add 100 new rows:

INSERT INTO dbo.StoreData (ItemDate, Store, Terminal, TransNum, LineNum, QTY, Amount)
SELECT TOP(100) o.create_date
    , 0
    , c2.column_id
    , c3.column_id
    , CONVERT(int, CONVERT(varbinary(8), CRYPT_GEN_RANDOM(8)))
    , 1
    , 2
FROM sys.columns c1
    CROSS JOIN sys.columns c2
    CROSS JOIN sys.columns c3
    CROSS JOIN sys.objects o;

This query will identify any rows that have been changed by the store (such as the one above), by comparing the rowversion value shown in the HQ table:

DECLARE @Store int;
DECLARE @RV binary(8);

SET @Store = 0;
SELECT @RV = LastRowVersion
FROM dbo.StoreRowVersion srv
WHERE srv.Store = @Store;

/* rows that have been changed since they were loaded into the HQ */
SELECT *
FROM dbo.StoreData s
    INNER JOIN dbo.HQ h ON s.ItemDate = h.ItemDate
        AND s.Store = h.Store
        AND s.Terminal = h.Terminal
        AND s.TransNum = h.TransNum
        AND s.LineNum = h.LineNum
WHERE s.rv > @RV;
╔═════════════════════════╦═══════╦══════════╦══════════╦════════════╦═════╦════════╗
║ ItemDate                ║ Store ║ Terminal ║ TransNum ║ LineNum    ║ QTY ║ Amount ║
╠═════════════════════════╬═══════╬══════════╬══════════╬════════════╬═════╬════════╣
║ 2012-02-10 20:16:00.707 ║ 0     ║ 12       ║ 12       ║ 2038458824 ║ 4   ║ 2      ║
╚═════════════════════════╩═══════╩══════════╩══════════╩════════════╩═════╩════════╝
/* new rows that have not been added to the HQ */
SELECT s.ItemDate, s.Store, s.Terminal, s.TransNum, s.LineNum, s.QTY, s.Amount
FROM dbo.StoreData s
WHERE s.rv > @RV
EXCEPT
SELECT s.ItemDate, s.Store, s.Terminal, s.TransNum, s.LineNum, s.QTY, s.Amount
FROM dbo.StoreData s
    INNER JOIN dbo.HQ h ON s.ItemDate = h.ItemDate
        AND s.Store = h.Store
        AND s.Terminal = h.Terminal
        AND s.TransNum = h.TransNum
        AND s.LineNum = h.LineNum
╔═════════════════════════╦═══════╦══════════╦══════════╦═════════════╦═════╦════════╗
║ ItemDate                ║ Store ║ Terminal ║ TransNum ║ LineNum     ║ QTY ║ Amount ║
╠═════════════════════════╬═══════╬══════════╬══════════╬═════════════╬═════╬════════╣
║ 2003-04-08 09:13:38.093 ║ 0     ║ 6        ║ 12       ║ -1501553543 ║ 1   ║ 2      ║
║ 2003-04-08 09:13:38.093 ║ 0     ║ 12       ║ 12       ║ 879791677   ║ 1   ║ 2      ║
║ 2009-04-13 12:59:10.950 ║ 0     ║ 12       ║ 12       ║ 460219354   ║ 1   ║ 2      ║
║ 2009-04-13 12:59:10.967 ║ 0     ║ 12       ║ 12       ║ -1956152452 ║ 1   ║ 2      ║
║ 2009-04-13 12:59:10.983 ║ 0     ║ 12       ║ 12       ║ -556053565  ║ 1   ║ 2      ║
║ 2009-04-13 12:59:10.983 ║ 0     ║ 12       ║ 12       ║ -383529527  ║ 1   ║ 2      ║
║ 2009-04-13 12:59:11.077 ║ 0     ║ 6        ║ 12       ║ 1816812575  ║ 1   ║ 2      ║
║ 2009-04-13 12:59:11.077 ║ 0     ║ 12       ║ 12       ║ -1349955842 ║ 1   ║ 2      ║
║ ...                                                                                ║
╚═════════════════════════╩═══════╩══════════╩══════════╩═════════════╩═════╩════════╝
|improve this answer|||||
  • The problem is not to see what changed, that is part of the deploy process which is handled with a last modified date since the last transfer. My issue is that I'm trying to verify, although a transfer was registered from store to HQ, if, what the HQ has, matches with what the store sent. In other words, if the store sent an invomplete transaction or if the HQ didn't receive everything or even if there was an error at HQ... this verification process would just help alert HQ that there's a discrepancy between Store 1234 and HQ. – JohnG Aug 22 '17 at 20:48
  • 2
    I don't get it. What are you comparing? The file that got sent to HQ with the data that is imported into the HQ database from that file? You must be comparing data - this method gives you the ability to very easily confirm if a row has changed since the last time you saw that row. Perhaps I'm just dumb, but rolling up the aggregated data then comparing that to the data itself is pointless unless you think there is a bug in SQL Server itself. – Max Vernon Aug 22 '17 at 21:28
  • Your solution is not dumb far from that. I like the idea. there are 2 issues for the software. 1 first the fact that we need to add a column to every table we already have will be a hefty job and secondly, if I understand correctly,(maybe I'm dumb :-) ), This will tell us what latest version of the data for a store is at HQ. how can we tell if the latest versions that is registered at the HQ is the latest one? what if there was a network issue and the HQ never received anything? Our actual deploy from Store to HQ process is solid and verifies all different possible scenarios – JohnG Aug 23 '17 at 12:36
  • @MaxVernon - If I'm following correctly, they're basically using a roll-up done at the remote location, and a rollup done at HQ, and comparing those to confirm that the underlying data matches; for all practical intents and purposes, it's a human-readable version of a checksum. – RDFozz Aug 23 '17 at 14:20
  • 1
    @JohnG - from the details provided in your question it seems like you are attempting to determine if the store changed something since the last time you received an update from them. My code addresses that issue. If, as RDFozz said in his comment above, you are attempting to determine if there was some problem transporting the data from the store to the HQ, that is a completely different problem, and not one related to SQL Server. I upvoted his answer yesterday because it certainly shows you how to decrease the time taken to generate the roll-up, and if that works - great! – Max Vernon Aug 23 '17 at 14:48

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.