home / github / issue_comments

Menu
  • Search all tables
  • GraphQL API

issue_comments: 999870282

This data as json

html_url issue_url id node_id user created_at updated_at author_association body reactions issue performed_via_github_app
https://github.com/simonw/datasette/issues/1518#issuecomment-999870282 https://api.github.com/repos/simonw/datasette/issues/1518 999870282 IC_kwDOBm6k_c47mM9K 9599 2021-12-22T20:45:56Z 2021-12-22T20:46:08Z OWNER

New short-term goal: get facets and suggested facets to execute in parallel with the main query. Generate a trace graph that proves that is happening using datasette-pretty-traces.

I wrote code to execute those in parallel using asyncio.gather() - which seems to work but causes the SQL run inside the parallel async def functions not to show up in the trace graph at all.

```diff diff --git a/datasette/views/table.py b/datasette/views/table.py index 9808fd2..ec9db64 100644 --- a/datasette/views/table.py +++ b/datasette/views/table.py @@ -1,3 +1,4 @@ +import asyncio import urllib import itertools import json @@ -615,44 +616,37 @@ class TableView(RowTableShared): if request.args.get("_timelimit"): extra_args["custom_time_limit"] = int(request.args.get("_timelimit"))

  • Execute the main query!

  • results = await db.execute(sql, params, truncate=True, **extra_args)

  • Calculate the total count for this query

  • filtered_table_rows_count = None
  • if (
  • not db.is_mutable
  • and self.ds.inspect_data
  • and count_sql == f"select count(*) from {table} "
  • ):
  • We can use a previously cached table row count

  • try:
  • filtered_table_rows_count = self.ds.inspect_data[database]["tables"][
  • table
  • ]["count"]
  • except KeyError:
  • pass

  • Otherwise run a select count(*) ...

  • if count_sql and filtered_table_rows_count is None and not nocount:
  • try:
  • count_rows = list(await db.execute(count_sql, from_sql_params))
  • filtered_table_rows_count = count_rows[0][0]
  • except QueryInterrupted:
  • pass

  • Faceting

  • if not self.ds.setting("allow_facet") and any(
  • arg.startswith("_facet") for arg in request.args
  • ):
  • raise BadRequest("_facet= is not allowed")
  • async def execute_count():
  • Calculate the total count for this query

  • filtered_table_rows_count = None
  • if (
  • not db.is_mutable
  • and self.ds.inspect_data
  • and count_sql == f"select count(*) from {table} "
  • ):
  • We can use a previously cached table row count

  • try:
  • filtered_table_rows_count = self.ds.inspect_data[database][
  • "tables"
  • ][table]["count"]
  • except KeyError:
  • pass +
  • if count_sql and filtered_table_rows_count is None and not nocount:
  • try:
  • count_rows = list(await db.execute(count_sql, from_sql_params))
  • filtered_table_rows_count = count_rows[0][0]
  • except QueryInterrupted:
  • pass +
  • return filtered_table_rows_count +
  • filtered_table_rows_count = await execute_count()

     # pylint: disable=no-member
     facet_classes = list(
         itertools.chain.from_iterable(pm.hook.register_facet_classes())
     )
    
    • facet_results = {}
    • facets_timed_out = [] facet_instances = [] for klass in facet_classes: facet_instances.append( @@ -668,33 +662,58 @@ class TableView(RowTableShared): ) )
  • if not nofacet:

  • for facet in facet_instances:
  • (
  • instance_facet_results,
  • instance_facets_timed_out,
  • ) = await facet.facet_results()
  • for facet_info in instance_facet_results:
  • base_key = facet_info["name"]
  • key = base_key
  • i = 1
  • while key in facet_results:
  • i += 1
  • key = f"{base_key}_{i}"
  • facet_results[key] = facet_info
  • facets_timed_out.extend(instance_facets_timed_out)

  • Calculate suggested facets

  • suggested_facets = []
  • if (
  • self.ds.setting("suggest_facets")
  • and self.ds.setting("allow_facet")
  • and not _next
  • and not nofacet
  • and not nosuggest
  • ):
  • for facet in facet_instances:
  • suggested_facets.extend(await facet.suggest())
  • async def execute_suggested_facets():
  • Calculate suggested facets

  • suggested_facets = []
  • if (
  • self.ds.setting("suggest_facets")
  • and self.ds.setting("allow_facet")
  • and not _next
  • and not nofacet
  • and not nosuggest
  • ):
  • for facet in facet_instances:
  • suggested_facets.extend(await facet.suggest())
  • return suggested_facets +
  • async def execute_facets():
  • facet_results = {}
  • facets_timed_out = []
  • if not self.ds.setting("allow_facet") and any(
  • arg.startswith("_facet") for arg in request.args
  • ):
  • raise BadRequest("_facet= is not allowed") +
  • if not nofacet:
  • for facet in facet_instances:
  • (
  • instance_facet_results,
  • instance_facets_timed_out,
  • ) = await facet.facet_results()
  • for facet_info in instance_facet_results:
  • base_key = facet_info["name"]
  • key = base_key
  • i = 1
  • while key in facet_results:
  • i += 1
  • key = f"{base_key}_{i}"
  • facet_results[key] = facet_info
  • facets_timed_out.extend(instance_facets_timed_out) +
  • return facet_results, facets_timed_out +
  • Execute the main query, facets and facet suggestions in parallel:

  • (
  • results,
  • suggested_facets,
  • (facet_results, facets_timed_out),
  • ) = await asyncio.gather(
  • db.execute(sql, params, truncate=True, **extra_args),
  • execute_suggested_facets(),
  • execute_facets(),
  • ) +
  • results = await db.execute(sql, params, truncate=True, **extra_args)
     # Figure out columns and rows for the query
     columns = [r[0] for r in results.description]
    

    `` Here's the trace forhttp://127.0.0.1:4422/fixtures/compound_three_primary_keys?_trace=1&_facet=pk1&_facet=pk2` with the missing facet and facet suggestion queries:

{
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
1058072543  
Powered by Datasette · Queries took 0.88ms · About: github-to-sqlite