Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

ECS, SQL and visual tools...

Discussion in 'Entity Component System' started by Arowx, Jan 22, 2019.

Thread Status:
Not open for further replies.
  1. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    ECS uses data filters to match data that is then processed in a function/system.

    In a lot of regards this is similar to SQL that uses a query filter against a database of data to extract data.

    An SQL approach to ECS could be powerful because it would allow simple and flexible query syntax to power your game with the addition of making easier visual tools to make games.

    It could also decouple the systems aspect of ECS from the data it works on 'aka' the boilerplate problem.

    Currently ECS tightly couples data and functions via entities and their system, with a more SQL style system the data is decoupled and this allows the filter to be dynamic.

    Also the entities would just be tables of data in our unity game database. SQL is a very powerful tool that would facilitate changing/generating/removing and adding data/entities to our games.

    Could the humble database table be the technology that Unity needs to fix the game complexity problem, it seems to work for other complex data problem domains in the real world.

    Or would you not want to be a database admin game developer?


    Example of how visual tools can help simplify complex data.
     
    Last edited: Jan 23, 2019
    Stardog and Flurgle like this.
  2. starikcetin

    starikcetin

    Joined:
    Dec 7, 2017
    Posts:
    340
    You already have the ability to query in ECS. I can't follow your train of thoughts here.
     
  3. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    I thought ECS was locked into a hard coded type based filter, not a flexible token based syntax like SQL...

    e.g.

    Code (CSharp):
    1.  public class MoveForwardSystem : JobComponentSystem
    2.     {
    3.         [BurstCompile]
    4.         struct MoveForwardRotation : IJobProcessComponentData<Position, Rotation, MoveSpeed>
    5.         {
    6.             public float dt;
    7.    
    8.             public void Execute(ref Position position, [ReadOnly] ref Rotation rotation, [ReadOnly] ref MoveSpeed speed)
    9.             {
    10.                 position = new Position
    11.                 {
    12.                     Value = position.Value + (dt * speed.speed * math.forward(rotation.Value))
    13.                 };
    14.             }
    15.         }
    16.  
    17.         protected override JobHandle OnUpdate(JobHandle inputDeps)
    18.         {
    19.             var moveForwardRotationJob = new MoveForwardRotation
    20.             {
    21.                 dt = Time.deltaTime
    22.             };
    23.             var moveForwardRotationJobHandle = moveForwardRotationJob.Schedule(this, inputDeps);
    24.             return moveForwardRotationJobHandle;
    25.         }
    26.     }
    The filter/query is hard coded into the struct MoveForwardRotation, there is no option to limit the scope of the data for this query/filter dynamically.

    It's like the SELECT query in SQL without the WHERE clause...

    Actually it's without the FROM clause that can limit the pool of applicable data.
     
    Last edited: Jan 22, 2019
  4. RecursiveEclipse

    RecursiveEclipse

    Joined:
    Sep 6, 2018
    Posts:
    298
    Maybe it's possible to use Any/All/None(FROM/WHERE/etc) like components to generate an EntityArchetypeQuery and filter in a job? Just an idea, I have no idea what the cost of building the query or what technical issues would come up would be. But you can more or less use ScriptableObject databases to get data right now, only in the main thread though.
     
  5. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    One area where a Where clause could be very useful could be a calculated query that only needs to work on characters/npcs/units within range of the player or within LOS of the player e.g. animation/movement/player detection/reaction systems.
     
  6. starikcetin

    starikcetin

    Joined:
    Dec 7, 2017
    Posts:
    340
    The where clause is the body of your loop.
     
  7. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    Then you will be processing every matching data entity when you could be only working on a fraction of the data.
     
  8. NoDumbQuestion

    NoDumbQuestion

    Joined:
    Nov 10, 2017
    Posts:
    186
    I think we have chunk iteration "where" it check/compare SharedComponentData and check dirty chunk. Quite limited, SQL query maybe helpful in some case but ECS was specifically used for game and it far different from how database query work internally.
     
  9. If we want to put it this simplistic way, SQL works just like the ECS. If you have adequate index on your table (set of component equivalent) it goes through the index-table (where you have comparison data and reference to the full data row) and pick ones you asked for, it's like chunk iteration (going through all of the entities with a certain set of components, although ECS is physically moving the data in place, SQL usually references it - sometimes not, but it is because of the response times).
    If you don't have the right index on the table it goes through the entire table from the beginning to the end and gives you what you were asking for. (Now this is really a simplistic way to put, because a ton of optimization still applies and such).
    So there is no free lunch, either you or the ECS code base needs to go through all of the component groups or on an index table which points to the component group.
    Since this system prefers the direct data access, it's not really a good idea to build an indexer other than you already have (chunk iteration).

    Just compare the average response time of a good SQL setup and a good ECS system setup. It's not the same ballpark. Having an abstraction on the top of this system is not the best idea IMHO. Otherwise we could use OOP at the first place (that is built precisely for this purpose)
     
  10. Ashkan_gc

    Ashkan_gc

    Joined:
    Aug 12, 2009
    Posts:
    1,117
    The main goal of ECS is to put the data in memory in a layout which is fastest to process and avoid jumps (so you don't mess up with your CPU's prediction algo), cache misses and other things which can affect performance negatively.
    Also the creators of the system are highly against generalized systems and think you should create your entity layouts based on the exact problem you are solving so if you have loops which skip many of your entities, probably some of your components might need to be divided to multiple ones so they are in different systems and processed separately.

    Also see the principles document in ECS docs repo for more info, the movement is toward LESS and not MORE abstractions
     
  11. starikcetin

    starikcetin

    Joined:
    Dec 7, 2017
    Posts:
    340
    Yes, SQL does that as well.
     
  12. Srokaaa

    Srokaaa

    Joined:
    Sep 18, 2018
    Posts:
    169
    ECS reminds me more of a NoSQL databases where instead of doing JOINs you rather denormalize data so it is easily accessible for each entity. I think SQL-style JOINs would kill any performance advantage that ECS gives us and I am quite sure that they would be over-used as they are just convenient to use and have data nicely normalized.
     
  13. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    The thing is it won't do it every frame and nore should ECS once it has the filtered data it only needs to check new data or changed data for addition to the query/filters dataset.

    With an SQL style approach ECS would do all of this under the hood for us, whereas with boilerplate ECS you would have to write a number of systems to move the data about and mark it and search it and sort it.

    For example an SQL style LOS to player query could look like this...

    Code (CSharp):
    1. Select ID, Position
    2. from Enemy
    3. where Alive = TRUE and
    4. Range(Position, PlayerPosition) < 1000 and
    5. LineOfSight(Position, PlayerPosition) = True;
    Then you could update animations, sound, fx for all enemy players in LOS to the player.

    In ECS Boilerplate you would probably want 3 atomic systems to check for LOS, Range and Enemies that would have to work in sequence each frame to obtain this data-set.

    An SQL query style ECS system could build those systems under the hood but also have the complexity to ensure it marks the data so only new data or changed data will be parsed and added.

    Would you prefer to be the boilerplate ECS programmer or SQL style ECS programmer?

    Also an SQL style of ECS would be layered onto ECS so it would not replace boilerplate bespoke ECS just add an easy to use way of working with ECS that is industry standard.

    And think of all those bored database programmers who could make great games with Unity.
     
    Last edited: Jan 24, 2019
  14. eizenhorn

    eizenhorn

    Joined:
    Oct 17, 2016
    Posts:
    2,683
    DidChange, DidAddOrChange, WorldDiff

    EntityArchetypeQuery -> Any

    And will limits us, will tie us hands, I want all control over all data pipeline, and less black boxes, I want have my own "filter" logic related on my requirements, and not on "some under hood stuff".
     
  15. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194

     
  16. eizenhorn

    eizenhorn

    Joined:
    Oct 17, 2016
    Posts:
    2,683
     
  17. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    SQL Structured Query Language is designed to make data filters/queries and half of working with ECS and Data Driven development is making queries and filtering data.

    The command syntax is already built into C# e.g.

    Code (CSharp):
    1. IQueryable<Customer> custQuery =
    2.     from cust in db.Customers
    3.     where cust.City == "London"
    4.     select cust;
    5.  
    6. foreach (Customer cust in custQuery) {     Console.WriteLine("ID={0}, City={1}", cust.CustomerID,         cust.City); }
    7.  
    Once you get past basic ECS examples where everything needs to be processed every frame you need to have multiple ECS systems that filter and sub filter data.

    With an SQL approach you can have multiple where clauses that would limit and further filter the data.

    With a from clause you could limit the scope/worlds the query applies to.

    Single lines of readable text could replace writing entire ECS boilerplate systems that you only need to filter your data set.

    The ECS engineers could then ensure that more complex and connected queries/filters are handled in the optimal fashion, something a novice ECS programmer would only be able to achieve with a lot of additional learning through trial error and forum posts...

    IMHO it would be a much more powerful and simpler to use than boilerplate ECS.
     
    Flurgle likes this.
  18. tertle

    tertle

    Joined:
    Jan 25, 2011
    Posts:
    3,753
    Have you considered the performance implications of what you're suggesting for a 'where' clauses?

    How you filter at the moment is generally after you receive your data 'where' is effectively executed within a burst compiled job in parallel. It's extremely fast.

    If you were to filter when gathering data you'd be moving this 'where' clause to the main thread which could end up taking significant time, especially on large entity sets.
     
  19. 5argon

    5argon

    Joined:
    Jun 10, 2013
    Posts:
    1,555
    "Where" query is not related to ECS and likely won't be added, because it is not based on getting chunks.

    You want all component A containing int, where its int is less than 5. How can a chunk knows which member matches that criteria without iterating through them? The ECS way would be attaching components to classify them because they would move to different chunks. If UT implement "where" by iteration as I said, sure it would be simpler but that has no place in ECS library and soon someone would mistake it that it is performant like everything else in ECS. (The closest in ECS is SetFilter by one SCD value, since SCD's unique value works by separating chunks)

    ps. I just watched how Firebase Cloud Firestore filters data fast by automatic indexing and zigzag range query :
    maybe it could interest you, but also you will know why that kind of way cannot be adopted to fit ECS's chunk memory layout. (And there are a lot of limitations and some manual work in setting the composite index up)
     
  20. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    OK let's take an example:

    You want to update all the characters within 1 km and LOS of the player.

    A: One system could go through all characters then in the function check distance and los then update them.

    B:Three systems could find and filter the characters by range then LOS then update them.
    Approach A should be faster for small data runs but is not Atomic and reusable as B.

    Really you want the Atomic nature of B so any time you want a LOS test or Range test you have simple atomic systems to do that. However you want the speed of A.

    C: Three systems that monitor their data sets and only work on updated or changed data can provide the ideal solution.
    What if one SQL style of programming with ECS could provide solution C under the hood with only a few lines of code and some common in built atomic ECS systems for LOS and Range checking?

    Now imagine you want to do different updates based on the LOD...​
     
  21. snacktime

    snacktime

    Joined:
    Apr 15, 2013
    Posts:
    3,356
    SQL was designed for relational data. In almost any high performance app or apps at scale, that normalized data is denormalized into a more efficient structure for querying. SQL on top of denormalized data simply doesn't work.

    Most of the data we work with in games is ephemeral, temporary in nature. Plus ECS has no idea of the structure of your data. So ECS starts with data that is denormalized and structured for performance. That's really the only starting point that even makes sense.

    So the question really is more how do most systems that denormalize data handle situations like complex groupings and such. And the answer in almost every case is they duplicate data as needed. Create groupings of your data that just contain what is of interest to a specific feature. Sometimes you can afford to iterate over entities you know you don't care about. But that can fairly quickly escalate into iterating over tens of thousands of entities you don't care about. So taking the one time hit of creating separate groups is far more efficient.

    In a perfect world you would have normalized data that is efficient to query. You can actually do that and there is some efforts right now actually to design the next generation of databases to be more cpu cache friendly. Performance is back in style now days. But for the most part denormalize and duplicate is the simplest approach that works well.
     
  22. 5argon

    5argon

    Joined:
    Jun 10, 2013
    Posts:
    1,555
    Your solution C includes includes iteration over chunk queried ECS data , and as I said if they made that available as a part of ECS package it would looks out of place, as the iteration-as-where is not really ECS-like. The where filter part is already not "under the hood" as it works above ECS. If the chunk could maintain a magical index for all data where it can be use as a partial-chunk query, then that's appropriate to be added to the base library.

    You might DIY and call it as "ECS Query" package instead where it wraps over chunk iteration after getting all the chunks.
     
  23. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    You are completely missing the point ECS is a Data Driven Development so you will provide it with a set of data from entities that need an action or process to update their values. It's not about massive amounts of business data that need to be normalised into a database.

    The only reason these entities have data is that data is needed in their virtual world.

    However because SQL was designed to select a set of data and be easy to use it can fit the criteria of a the data filter of ECS and it's where clause can be leveraged as sub filters or queries on that data.

    So from one SQL query based ECS query you can have the equivalent of multiple or a much more complex ECS query running without all of the boilerplate code needed in the current ECS API.
     
  24. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    Iteration is all you do in ECS that OnUpdate/Execute function is just the inner workings of what would be a foreach loop in a more SQL based approach.

    It sounds like your getting bogged down in the nitty gritty of the ECS API and it's chunks paradigm, if you take a step back you will see that all you are doing is running a system that finds entities with a matching data filter/query and then runs a multi-thread foreach loop over the data with a function that updates the data or generates new data.

    The SQL where clause is just a sub filter that could exist within your inner loop as conditional statements, where it would cost more as you are checking all the matching data.

    Or it could be that you generate filtered data sets e.g. Range < 1km then LOS Player data that would only need to be updated when any of the data changes e.g. player/NPC moves. This would mean you have multiple interlinked ECS system that pass data to each other.

    Which is a lot of boilerplate code, code that could be written as one SQL style query with the right API approach.
     
    e199 likes this.
  25. snacktime

    snacktime

    Joined:
    Apr 15, 2013
    Posts:
    3,356
    What you are missing is what is required to do relational queries.

    You either need to structure it in a normalized fashion, or resort to inefficient iteration patterns to pull the data out of the denormalized structure.

    So the normal approach to this is you start with your source data, look at the most efficient way to query it,and create your denormalized data sets once up front.

    In the context of ECS chunks are that denormalized data. So you have your source data, you reason out how to group it and then put it into ECS in that grouping ready to query efficiently.

    What you are asking for is to just stick it all into ECS without doing any grouping at that point. And then ask ECS to do that for you on demand. Best case you did double work using that approach. Worst cases quickly degenerate horribly.
     
  26. Arowx

    Arowx

    Joined:
    Nov 12, 2009
    Posts:
    8,194
    We are not doing relational queries as this would imply querying multiple entities/tables we are filtering a pre-existing data set or only working on one table in database lingo.
     
    e199 likes this.
  27. 5argon

    5argon

    Joined:
    Jun 10, 2013
    Posts:
    1,555
    One table is still relational algebra. Selection and projection are such instructions that WHERE clause would ended up to which work on 1 table and produce query result (https://en.wikipedia.org/wiki/Relational_algebra). We can WHERE in ECS with ComponentType already. But you introduced "<" where the typing system does not support. ECS choose to go the "=="-only way because it is fast. Filtering is just asking chunk header. Getting data is copying a block of memory.

    What Firebase did to support anything other than "==" requires internal index book keeping work on each data element for each field so that they have a different version of themselve that are ordered contiguously ready for selection/projection by greater than/lesser than. I think I don't want that much processing in an ECS library. (Where this DB is on the player's phone not on a dedicated server.)

    That is correct, maybe I didn't get the point across good enough. There is nothing wrong for an ECS lib to make an iteration wrapper, and we just get ForEach syntax a while ago which is exactly that. IJobProcessComponentData is also an iteration wrapper. However both are based on iteration over chunks. As snacktime said the unit of our database here is chunks. So IJPCD and ForEach is the relational algebra of ECS. Because it selects and projects data in the most granular way in the context of ECS.

    I imagine if this "generate" exists in the library, it would work under the hood in ECS style as making a shadow clone of all Range chunks and adding component tag to segment them. (While maintaining the original data) If that is what you visualizing, I think it is doing too much transformation on the data before it could get you the query result. (and at this point I feel that it is already "out of ECS") Of course we all do this kind of tagging thing in the game and that's why you want it implemented as WHERE sub-query methods because you want shortcuts over things you do over and over, but "doing too much" means it is already too specific to the level which should be in user's game code not in a library.
     
    eizenhorn likes this.
Thread Status:
Not open for further replies.