I was comparing wheather Sql or Graph Gremlin API was a better fit and playing around in the cosmos db database Dashboards and making queries from there not from code.
I noticed when reading even a small complex object (an edge with 2 properties and two edges also with 2 properties each) using Gremlin. The request charge is almost 10x more than what it would be in SQL.
Just getting the properties using the properties method.
I played around using turning off and on indexing etc but all queries seem very large. An example query.
g.V().hasLabel('User').has('id', 1).project('UserData', 'RoleData').by(local(properties())).by(out('HasRole').local(properties()))
Is this just the normal that Gremlin queried reads and writies cost significantly more than SQL given the same data. Or I am i missing something
You can use the SQL API in some cases like this yes when the query is "simple". Gremlin, always keep in mind that you are requesting the whole graph, step by step. So if your first step is wide, in the unfiltered sense, like that: g.V().hasLabel('User') you request all User type objects. The more the volume changes, the more time it will take.
That's why most queries don't use hasLabel but has(LABEL, key, value) to filter as much as possible from the first step. However, you don't always have the info to be able to filter from the start.
So if you find cases where the SQL API can become more efficient, yes replace it, but don't bother doing it on complex queries because you'll waste your time. SQL is unable to do step-by-step navigation. You must have all known filters from the first step