March 19, 2015 at 8:53 am
I am trying to create a table that would represent a workload for each shop. In order to do that I need to have WorkLoad table and ShopWorkLoad table which is actually just aggregation of WorkLoad.
WorkLoad contains a list of following items:
current orders that are in the process (one select statement)
scheduled orders (another select statement)
expected orders (third select statement) that come through a third-party system
All of this needs to be live. So, for example, as soon as order is added to Order table it should be included in WorkLoad if certain conditions are met. Same goes for scheduled orders (which come from another table). Expected orders will be loaded on a daily bases (based on historical data).
ShopWorkLoad table is aggregation of WorkLoad table.
Currently I did it this way:
Added after insert/update trigger on Order table: when order is created/updated, if it meets certain conditions, it should be inserted in WorkLoad, otherwise remove it from workload if it's in there and doesn't meet conditions
Added after insert/update trigger on Schedule table: when order is scheduled, if it meets certain conditions, it should be inserted in WorkLoad, otherwise remove it from workload if it's in there and doesn't meet conditions
Running daily job that populates WorkLoad table with expected orders based on historical values
Final step is to create an indexed view vShopWorkLoad
My biggest concern is usage of triggers which call pretty complex logic to determine whether item should be added to workload or not.
One other option was to create vWorkLoad view and somehow make it an indexed view but currently I don't see a way of doing that because the query consists of 4 union select statements, below is pseudo example. But even if doing it that way, how to build aggregated indexed view on top of vWorkLoad indexed view?
Third option is to use sql agent job which would run every x seconds (maybe 20) and it would execute all of these queries to populate WorkLoad table with delay of 10-20 seconds, but I am still not sure if this is acceptable to the client.
Fouth option is to create 3 or 4 indexed view where sum of them makes a workload. Then, ShopWorkLoad view would be built on top of these 3 or 4 indexed views, but in this case I don't know how this would affect performance since ShopWorkLoad query would be often queried.
Are there any other suggestions or I should stick with existing solution (triggers)?
Example of workload pseudo query:
select
WorkLoadType = 'Order in process',
OrderId,
ShopId,
...
from
Order
-- ...
-- JOIN 10-12 other tables
-- ...
WHERE
-- a bunch of where conditions
UNION
select
WorkLoadType = 'Scheduled order',
OrderId,
ShopId,
...
from
Order
-- ...
-- JOIN 10-12 other tables
-- ...
WHERE
-- a bunch of where conditions
select
WorkLoadType = 'Expected order',
OrderId,
ShopId,
...
from
Order
-- ...
-- JOIN 10-12 other tables
-- ...
WHERE
-- a bunch of where conditions
Working on SQL Server 2012
March 24, 2015 at 9:21 am
One way to do it would be to put a "WorkloadType" column on your Orders table, and keep that field updated using the triggers you use now, rather than copying the rows to another table. Then you can create a Workload view with the needed fields where the value of WorkloadType is in the list you want. With an index on Shop and WorkloadType, I expect it would be fast enough.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply