Does performance suffer for more field queries

Hi

In general, if you are adding more fields to query via the API, does that take a hit in performance? We are also wondering if we there are any benefits of removing redundant fields from entity?

We are wondering if the performance of the SGTK loaders or general pipeline queries would go down if we start to query more fields for published file entities?

Not all fields would get utilized as depending on the context they would get filled.

Cheers

Pritish

6 Likes

Hi Pritish,

Part of your question is TK specific but I’m going to try and respond from a more general Shotgun perspective.

Definitely there is value in doing the above. The underlying database has a limited number of fields it can support per entity (between 250 and 1600 based on datatypes) so removing and cleaning up redundancies is a good thing. Performance-wise, that’s potentially a smaller amount of data to search through and index as well as a smaller schema for your site, all good things.

It will cause a hit in performance but the impact depends on which fields you are returning.

Regardless of the field type, more fields mean more data to serialize and send over the wire but this impact, although certainly not 0, may be negligible. If the extra fields are simple data types like numbers, text, list fields, checkboxes, etc., their impact beyond serialization and transfer time should again be negligible.

You’ll get a much more noticeable performance impact as you start asking that entity and multi-entity fields be returned. These field types require that extra database joins or extra database queries be performed for your single API query. The same is true of thumbnail and query fields. Depending on your scale of data and field configurations this impact will vary but can start to be noticeable.

These are of course broad guidelines and generalizations. The actual impact is very dependant on your query, fields returned, your schema and your actual data. Nothing beats a little performance testing with some simple scripts and API queries against your site to truly understand the scale of any impact.

I sure hope this helps you out.

8 Likes

Oh boy, this thread might be a can-o-worms. But hey, it’s THERAPEUTIC, amirite?

Things to worry about (11am edition)

  1. The “Home” page that comes ootb filled with widgets for five or so ‘perspectives’ …is a real pain point if you end up with a lot of query fields. Users cry, “can’t we just have a nice page with everything on it?”

  2. If you get spinners (which are usually query fields going nuts) you can close the browser and… your server will still be working on that horrible query… for minutes sometimes. Um, maybe I shouldn’t have mentioned that.(*)

  3. Adding a field to an entity – “to one project” as it’s advertised – merely HIDES it from all the other projects. You still end up with 1,600 fields on the Shot entity after doing this for ten years.

  4. Reports get written by coordinators or producers by exporting CSV, and then using Excel to massage that data into client-cleanliness, even though all day long they’re in Shotgun using what looks like a spreadsheet. After all, you don’t want to add another field for “Cut duration in M:S:F less handle at FPS-per-shot”-willy-nilly.

The up-side

We’re all in this together. Collaboration

  1. Is the most important part of any endeavor

  2. In our field it will start to dwarf everything else as time goes on.

  3. Shotgun Software has the unlimited resources of AutoDesk backing them, so they can rewrite Shotgun on top a new backend in all their spare time. They might even use a graph database this time. Neo4j… just go ahead, buy it. You know you want it.

  4. They might solve the performance and shifting-schema problems while they’re at it.

  5. Shotgun gets it, they, along with all of us, need to move forward together (since they can’t solve everybody’s problems individually.) That’s why this community matters.

(*) Please send a Shotgun Engineer a free beer.

4 Likes

Just to add onto what @bouchep was discussing, we actually have a guide to understanding what makes up a slow query. It addresses both field configuration as well as API calls:

https://support.shotgunsoftware.com/hc/en-us/articles/219030848-Optimizing-filters-and-API-scripts-to-make-your-Shotgun-site-faster

7 Likes

@dougm, we’re flattered you think Autodesk would give us their entire operating budget to rewrite Shotgun! We think they should, too, and we’re still working on that complete takeover from the inside to make it happen. You’ll be the first to know should our plan succeed!

13 Likes