Live data sync - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Live data sync

Author

Topic

ileile
Starting Member

1 Post

Posted - 2015-03-19 : 10:52:25

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

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 12:05:45

If the rate of Insert / Update of on Order and Schedule tables is low (actually if it is low in terms of causing the change to Workload to change), compared to the number of times that the report is run, then I would lean towards using triggers.

If the logic is complete and the data large than a View is going to potentially have to re-do the work often (unless you can get a persistent index ... but in that case you might as well user Triggers I reckon!!)

If the query / reporting rate might increase signfiicantly in the future I would lean toward triggers. As data / usage grows you may find that having all the logic in the reporting side may not scale well.

Downside:

If the logic is in a View and the logic needs to change then just fix the view.

If the logic is in a Trigger and it changes you will have to update all the data that depends on it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources