Thread: Live synchronization
03-19-15, 10:43 #1Registered User
- Join Date
- Jul 2011
Unanswered: Live synchronization
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
03-25-15, 10:59 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
You don't need WorkLoad and ShopWorkLoad, for sure. Definitely don't use triggers. *ALL* operations within your database must be initiated by the front-end code calling stored procedures. The mechanism that determines the order qualifications (whether it meets the criteria of being added to the workload, and what shop needs to be assigned to it) must also be a stored procedure that is called by a scheduled task. That task (not the stored procedure itself) needs (my preference) to be created by a stored procedure, that gets called by a stored procedure that creates the order. Got the idea?"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."