7

I'd like some input on an issue I'm having. We have a section of code that we repeat throughout our stored procedures, and every time it takes quite some time to process, and when combined the number of reads runs into the hundreds of millions on a set of hundreds of thousands of Items. Basically we have Items, and Items can have up to 12 machines, each with their own state.

These are the (simplified) table structures:

CREATE TABLE dbo.ItemMachineState
(
    [itemID] [int],
    [machineID] [int],
    [stateID] [int]
)

CREATE TABLE dbo.Transition
(
    [machineID] [int] NOT NULL,
    [eventID] [int] NOT NULL,
    [stateID] [int] NOT NULL,
    [nextStateID] [int] NOT NULL
)

What happens is, during processing we create a #temp table which we work against, and it eventually has an eventID per Item. That temp table is then joined back to ItemState and Transition, like so:

UPDATE  dbo.ItemState
SET     stateID = tr.nextStateID
FROM    #temp t  
JOIN    dbo.ItemMachineState ist ON ist.itemID = t.itemID
JOIN    Transition tr ON tr.stateID = ist.stateID AND
                         tr.machineID = ist.machineID AND
                         tr.eventID = t.eventID

So the eventID, which we calculate, determines what will happen to a given Item's Machines, depending on what State they're each in. The issue is that an event can manipulate zero or more machine states in one movement, if that event is relevant to that particular combo of state and machine.

Here's an example of one of these state shifts:

ItemID 3468489 first looks like this in ItemMachineState...

itemID      machineID   stateID
----------- ----------- -----------
3468489     12          4
3468489     14          113
3468489     15          157
3468489     16          165
3468489     18          169
3468489     19          165
3468489     20          157
3468489     21          165
3468489     23          173
3468489     24          173
3468489     26          9
3468489     36          9

We do some work, and eventually have a #temp table that has an ItemID and an EventID...

itemID      eventID
----------- -----------
3468489     64

Then we join both of these tables to Transition, which looks like this for this particular eventID:

machineID   eventID     stateID     nextStateID
----------- ----------- ----------- -----------
13          64          73          79
13          64          74          79
13          64          75          79
13          64          76          79
13          64          77          79
13          64          78          79
13          64          187         79
13          64          188         79
13          64          189         79
13          64          190         79
13          64          191         79
36          64          9           79
36          64          194         79
36          64          196         79
36          64          208         79
36          64          210         79
36          64          213         79
36          64          218         79
46          64          73          79
47          64          73          79
70          64          73          79
70          64          75          79
70          64          76          79
70          64          77          79
70          64          78          79

Putting it all together:

SELECT  t.itemID, t.eventID, ist.machineID, ist.stateID, tr.nextStateID
FROM    #temp t  
JOIN    dbo.ItemMachineState ist ON ist.itemID = t.itemID
JOIN    Transition tr ON tr.stateID = ist.stateID AND
                         tr.machineID = ist.machineID AND
                         tr.eventID = t.eventID

itemID      eventID     machineID   stateID     nextStateID
----------- ----------- ----------- ----------- -----------
3468489     64          36          9           79

So in this particular example, this Event was only relevant to one Machine for this Item. It's stateID is updated from 9 to 79 on machineID 36, and everything else stays the same for this Item.

I'd like suggestions on how to approach this differently. We can't move away from the table structure, but we can alter how we go about setting stateID to nextStateID during transitions/events. As you can see above this works by elimination; we need the current state and machine to figure out what the next state is, for that machine, for that event. In some cases this won't update anything, other times it will update multiple machines in one go, and we like that ability. I don't think the leanest solution to this problem will be found by simply changing indexes or adding query hints, and that we need a new approach that limits the number of reads and processing time, but gives us the same functionality.


I wanted to avoid bringing indexes and such into this discussion because I'd have to then use real examples, which pollute the essence of what I'm trying to ask here, I changed the name of columns and tables to simplify my question. In any case, here you go:

Query plan http://pastebin.com/xhPa4t8d, create and index scripts http://pastebin.com/sp70QuEJ

Note that in the query plan we force an INNER LOOP JOIN. If left to a simple JOIN, the query takes exponentially longer to process.


Using @wBob UNIQUE CLUSTERED index, before: Before

and after: After

Using OPTION (MERGE JOIN, HASH JOIN) resulted in this execution plan and results:

After Option

Will update with other information shortly

  • 3
    Can you update your question with dbo.ItemState ddl and some data. you can use sqlfiddle.com to set up a repro. Also, are there any indexes, FKs, triggers defined on those tables ? You can post an actual execution plan xml (use pastebin and link it here). That will help you get better and faster answers :-) – Kin Shah Feb 26 '16 at 14:25
  • From the query plan, your Estimated rows (4292220) are way off than Actual Rows (747348). Make sure that your stats are updated. Also, instead of forcing join FROM #Event AS e INNER LOOP JOIN EFT.AssetState AS ast, you can use query hint OPTION (LOOP JOIN, QUERYTRACEON 8649, QUERYTRACEON 4199);. What is the total memory on the server and max memory as well as Max dop setting ? Also, you can check specific condition using if exists (select .. some criteria = true) then update else do nothing along with batch updates. – Kin Shah Feb 26 '16 at 15:30
  • why are you using OPTION (MERGE JOIN, HASH JOIN) at the same time ? Did you try OPTION (LOOP JOIN, QUERYTRACEON 8649, QUERYTRACEON 4199); ? – Kin Shah Feb 29 '16 at 14:38
  • @Kin this basically allows the Optimizer to pick one of the two join types, rearrange the tables in whatever order it sees fit, but not use Nested Loops. This will help rule out if Nested Loops are a problem here. – wBob Feb 29 '16 at 14:50
1

I would consider not updating all the rows at once and instead running through batches of machines so the volume of records decrease per update. You can keep the same code, just batch it.

1

I got around a 50% performance improvement in my test rig by creating a second temp table with a unique clustered index on assetID and eventID and dropping the LOOP hint. This should not semantically change your query results. Try this:

SELECT DISTINCT assetID, eventID
INTO #Event2
FROM #Event

CREATE UNIQUE CLUSTERED INDEX PK_temp_Event2 ON #Event2 ( assetID, eventID )

UPDATE ast
SET ast.stateID = st.nextStateID
FROM #Event2 AS e
    INNER JOIN EFT.AssetState AS ast
        ON ast.assetID = e.assetID
    INNER JOIN dbo.Transition AS st
        ON st.stateID = ast.stateID
            AND st.eventID = e.eventID
            AND st.machineID = ast.machineID

Let me know how you get on. If it works, consider adapting your original #Event table - there's no real need for two temp tables, this was just for the perf. tune exercise.

If it doesn't work, we can look at improving the test rig to more accurately reflect your setup. I did some experiments with less or no non-clustered indexes and got some good results as well although obviously other queries might use them.

Test Rig

-- Secondary DDL provided;
USE tempdb
GO

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'EFT' )
    EXEC ('CREATE SCHEMA EFT')
GO

IF OBJECT_ID('[dbo].[Transition]') IS NOT NULL DROP TABLE [dbo].[Transition]
IF OBJECT_ID('[EFT].[AssetState]') IS NOT NULL DROP TABLE [EFT].[AssetState]
IF OBJECT_ID('[dbo].[Event]') IS NOT NULL DROP TABLE [dbo].[Event]
IF OBJECT_ID('[dbo].[State]') IS NOT NULL DROP TABLE [dbo].[State]
IF OBJECT_ID('[dbo].[Machine]') IS NOT NULL DROP TABLE [dbo].[Machine]
IF OBJECT_ID('#Event') IS NOT NULL DROP TABLE #Event
GO



-- #EFT.AssetState
CREATE TABLE [EFT].[AssetState](
    [assetID] [int] NOT NULL,
    [busDate] [datetime] NOT NULL,
    [machineID] [int] NOT NULL,
    [stateID] [int] NOT NULL,
 CONSTRAINT [PK_AssetState] PRIMARY KEY CLUSTERED 
(
    [assetID] ASC,
    [busDate] ASC,
    [machineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AssetState_assetID] ON [EFT].[AssetState]
(
    [assetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AssetState_assetID_stateID] ON [EFT].[AssetState]
(
    [assetID] ASC,
    [stateID] ASC,
    [machineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AssetState_machineID_stateID_assetID] ON [EFT].[AssetState]
(
    [machineID] ASC,
    [stateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


-- dbo.Transition
CREATE TABLE [dbo].[Transition](
    [transitionID] [int] IDENTITY(1,1) NOT NULL,
    [machineID] [int] NOT NULL,
    [category] [varchar](50) NOT NULL,
    [eventID] [int] NOT NULL,
    [stateID] [int] NOT NULL,
    [nextStateID] [int] NOT NULL,
 CONSTRAINT [PK_Transition] PRIMARY KEY CLUSTERED 
(
    [transitionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_Transition_machineID_stateID_eventID] UNIQUE NONCLUSTERED 
(
    [machineID] ASC,
    [stateID] ASC,
    [eventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [UK_Transition_machineID_nextStateID_eventID] ON [dbo].[Transition]
(
    [machineID] ASC,
    [eventID] ASC,
    [stateID] ASC,
    [nextStateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO



CREATE TABLE [dbo].[State](
    [stateID]   INT PRIMARY KEY
    )
GO

CREATE TABLE [dbo].[Event](
    [eventID]   INT PRIMARY KEY
    )
GO

CREATE TABLE [dbo].[Machine](
    [machineID] INT PRIMARY KEY
    )
GO



ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_NextState] FOREIGN KEY([nextStateID])
REFERENCES [dbo].[State] ([stateID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_NextState]
GO

ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_State] FOREIGN KEY([stateID])
REFERENCES [dbo].[State] ([stateID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_State]
GO

ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_StateEvent] FOREIGN KEY([eventID])
REFERENCES [dbo].[Event] ([eventID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_StateEvent]
GO

ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_StateMachine] FOREIGN KEY([machineID])
REFERENCES [dbo].[Machine] ([machineID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_StateMachine]
GO

-- #Event
CREATE TABLE #Event
(
    assetID INT     ,
    busDate DATETIME,
    eventID INT     
)

CREATE CLUSTERED INDEX IX_Ev_assetID ON #Event ( assetID )
GO



-- Create dummy data
-- populate AssetState with 2,658,200 records
--  2,658,200
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO [EFT].[AssetState]( assetID, busDate, machineID, stateID )
SELECT 
    items.rn AS assetID,
    '1 Jan 2015' AS busDate,
    machines.rn AS machineID,
    items.rn % 7 AS stateID
FROM
    ( SELECT TOP 221520 * FROM cte ) items
    CROSS JOIN
    ( SELECT TOP (12) * FROM cte ) machines
GO


-- Get a random selection for temp table
INSERT INTO #Event ( assetID, busDate, eventID )
SELECT TOP (2128660) assets.assetID, assets.busDate, assets.assetID % 99 AS eventID
FROM ( SELECT DISTINCT assetID, busDate FROM [EFT].[AssetState] ) assets
    CROSS JOIN
    ( SELECT TOP (12) * FROM [EFT].[AssetState] ) machines
ORDER BY NEWID()
GO


-- Get selection for Transition table
INSERT INTO [dbo].[State] ( stateID )
SELECT assetID
FROM ( SELECT DISTINCT TOP 99 assetID FROM [EFT].[AssetState] ) m
GO

INSERT INTO [dbo].[Event] ( eventID )
SELECT assetID
FROM ( SELECT DISTINCT TOP 99 assetID FROM [EFT].[AssetState] ) m
GO

INSERT INTO [dbo].[Machine] ( machineID )
SELECT machineID
FROM ( SELECT DISTINCT machineID FROM [EFT].[AssetState] ) m
GO




INSERT INTO dbo.Transition ( machineID, category, eventID, stateID, nextStateID )
SELECT TOP (1214)
    m.machineID,
    CASE x.rn % 3 WHEN 0 THEN 'X' WHEN 1 THEN 'Y' WHEN 2 THEN 'Z' END category, 
    ( x.rn % 99 ) + 1 eventID,
    ( x.rn % 7 ) + 1 stateID,
    ( x.rn % 7 ) + 2 nextStateID
FROM ( SELECT DISTINCT machineID FROM [EFT].[AssetState] ) m
    CROSS JOIN
    ( SELECT TOP (102) ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) rn, * FROM [EFT].[AssetState] ) x
ORDER BY NEWID()
GO
--:exit



-- Original
DECLARE @startDate DATETIME = GETDATE()
BEGIN TRAN

UPDATE  EFT.AssetState
SET stateID = st.nextStateID
FROM    #Event AS e
    INNER LOOP JOIN
        EFT.AssetState AS ast
    ON ast.assetID = e.assetID
        INNER JOIN
        Transition AS st
    ON st.stateID = ast.stateID
        AND st.eventID = e.eventID
        AND st.machineID = ast.machineID;

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff1

ROLLBACK TRAN
GO



-- Revised
DECLARE @startDate DATETIME = GETDATE()

IF OBJECT_ID('tempdb..#Event2') IS NOT NULL DROP TABLE #Event2

SELECT DISTINCT assetID, eventID
INTO #Event2
FROM #Event

CREATE UNIQUE CLUSTERED INDEX PK_temp_Event2 ON #Event2 ( assetID, eventID )

BEGIN TRAN

UPDATE ast
SET ast.stateID = st.nextStateID
FROM #Event2 AS e
    INNER JOIN EFT.AssetState AS ast
        ON ast.assetID = e.assetID
    INNER JOIN dbo.Transition AS st
        ON st.stateID = ast.stateID
            AND st.eventID = e.eventID
            AND st.machineID = ast.machineID

SELECT @@rowcount r, DATEDIFF( s, @startDate, GETDATE() ) diff2

ROLLBACK TRAN
GO

Update 1: Do you mean 22 million records not reads? You don't have any WHERE clauses so you are going to get scans. You might get seeks for the outer table of a Nested Loops join, but the smaller table would rightly be at the top. I would be tempted to try OPTION ( MERGE JOIN, HASH JOIN ) to basically rule out nested Loops here see how you get on. This method also has the added benefit of not enforcing the join order. I'm suggesting this to gather information not necessarily a fix for production. Any suggestions on improving the test rig to more accurately reflect your setup?

How long to the two queries take? You might try running them through something like Plan Explorer (free version) as I think that will be illuminating. I noticed there was a mistake in my test rig where I include the index creation time in the timing for the second rig, so please rule that out. For my results, I get original query 15 seconds and revised query 7 seconds:

Test rig results

Update 2: Worked with OP to remove non-clustered indexes, remove loop join hint, and add unique index to temp table to get 75+% improvement. Fantastic input and credit to @PaulWhite.

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.