Excessive number (thousands) of DB queries for a single page

[kipz]

I've been using the API to page through a fully populated vulnerable code database, but the sheer number of queries generated make many of them time-out, even for very small page sizes.

I think the number of queries increases with offset...

For example, this returns a single result (I've removed other queries that are run a reasonable number of times):

curl -X GET "http://localhost:8000/api/packages/?limit=1&offset=34000" -H  "accept: application/json"

SELECT "vulnerabilities_vulnerabilityseverity"."id", "vulnerabilities_vulnerabilityseverity"."vulnerability_id", "vulnerabilities_vulnerabilityseverity"."value", "vulnerabilities_vulnerabilityseverity"."scoring_system", "vulnerabilities_vulnerabilityseverity"."reference_id" FROM "vulnerabilities_vulnerabilityseverity" WHERE "vulnerabilities_vulnerabilityseverity"."reference_id" = $1

calls: 7867

SELECT DISTINCT "vulnerabilities_vulnerabilityreference"."id", "vulnerabilities_vulnerabilityreference"."vulnerability_id", "vulnerabilities_vulnerabilityreference"."reference_id", "vulnerabilities_vulnerabilityreference"."url" FROM "vulnerabilities_vulnerabilityreference" WHERE "vulnerabilities_vulnerabilityreference"."vulnerability_id" = $1
calls: 425

I'm not sure if the Django ORM stuff can be tweaked here, but I'm able to write a single query that runs in a few milliseconds that gets the same results.

[sbs2001]

@kipz great find. I'll look into this when I get time.

OTOH, if you are trying to fetch specific packages there's #369 (comment) endpoint which is much better for this.

[TG1999]

We have done extensive work on decreasing the number of queries and it's not more an issue.