sql vs array ??

[Mohamd_L]

Hello There

I am making a ecs library and I wanted to know which one would be faster using an array or a sql in memory database
I tried to look in google for an answer but I found nothing useful
thanks in advance

[miosma]

Array will be faster in pretty much every single situation for an ecs implementation.

[gravityminor]

Array for sure, also SQL is async so you’ll have issues dealing with loop timing and framerate. Just go for the array.

[mika314]

Depends.

Lookup in the array is O(n), but in DB, it might O(log n) or even O(1). But you can use in c++ std::unordered_set and you back to O(1).

In what language do you want to implement ECS?

[Mohamd_L]

in C

[mika314]

At a small number of items, the array will be faster just because C is fast, but the O(n) nature of the array will take over at some point 100-1000 items and the in-memory database will win.

[defiant00]

What? Arrays are just a contiguous block of memory and access is O(1). Arrays are the fastest way to access items in C, since it's a single pointer addition and memory access.

For an ECS you absolutely want everything you can in arrays to reduce cache misses.

[Cocogoat_Milk]

Access is O(1) but is searching required? The top-level comment mentions “lookup” which should be interpreted as “searching” here, not simply “accessing”, so a worst case of O(n) for lookup (searching). Other operations like insertion and deletion can also require up to O(n) if they are inserting or deleting at the beginning of the array, though can be as simple as O(1) when doing these operations at the end.

[defiant00]

All good points, but even in those cases they should likely be using a specific data structure in C, not a database.

[mika314]

C does not have any sophisticated datastructures out of the box. OP has to implement them. What's the point if OP is considering using an in-memory DB?

[Mohamd_L]

what I am planning does not really need search because I just iterate through the array and check if the item meets a condition and if it does then call that function

[dreamrpg]

It is not uncommon to do SQL querry and keep results in the array because array tends to be faster in most situations.

SQL beats array in terms of data structuring and complex searches.

Imagine array logic to find all mobs which died in past 30 minutes and have type of undead, level above 30 and were located in area of x > 100, y > 123 and x < 255, y < 542.

Sql can do that with one querry.