Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    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:

    Code:
    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

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •