Difference between revisions of "Snapquery Evaluation 2024-07"

From BITPlan cr Wiki
Jump to navigation Jump to search
(Created page with "= Duration long tail = show cut off by query time out. <source lang='sql'> SELECT duration,nq.name FROM QueryStats qs JOIN NamedQuery nq ON qs.query_id=nq.query_id WHERE nq....")
 
 
(3 intermediate revisions by one other user not shown)
Line 9: Line 9:
 
ORDER BY 1 DESC
 
ORDER BY 1 DESC
 
</source>
 
</source>
 +
named query query_failures_by_category:
 +
 +
sql:
 +
SELECT
 +
count(*) as count,
 +
nq.domain,
 +
nq.namespace,
 +
error_category
 +
FROM QueryStats qs
 +
JOIN NamedQuery nq
 +
ON qs.query_id=nq.query_id
 +
WHERE error_category IS NOT NULL
 +
 +
GROUP BY error_category,nq.namespace,nq.domain
 +
ORDER BY 1 DESC
 +
 +
== query_failures_by_category ==
 +
 +
=== query ===
 +
<source lang='sql'>
 +
SELECT
 +
count(*) as count,
 +
nq.domain,
 +
nq.namespace,
 +
error_category
 +
FROM QueryStats qs
 +
JOIN NamedQuery nq
 +
ON qs.query_id=nq.query_id
 +
WHERE error_category IS NOT NULL
 +
 +
GROUP BY error_category,nq.namespace,nq.domain
 +
ORDER BY 1 DESC
 +
 +
</source>
 +
 +
 +
=== result ===
 +
{| class="wikitable" style="text-align: left;"
 +
|+ <!-- caption -->
 +
|-
 +
! align="right"|  count !! domain                    !! namespace          !! error_category
 +
|-
 +
| align="right"|    2044 || scholia.toolforge.org    || named_queries      || Syntax Error
 +
|-
 +
| align="right"|    788 || wikidata.org              || examples          || Syntax Error
 +
|-
 +
| align="right"|    624 || wikidata.org              || examples          || EndPointInternalError
 +
|-
 +
| align="right"|    612 || qlever.cs.uni-freiburg.de || issues.wikidata    || Syntax Error
 +
|-
 +
| align="right"|    242 || wikidata.org              || short_url          || EndPointInternalError
 +
|-
 +
| align="right"|    208 || wikidata.org              || short_url          || Syntax Error
 +
|-
 +
| align="right"|    180 || wikidata.org              || examples          || Timeout
 +
|-
 +
| align="right"|    148 || qlever.cs.uni-freiburg.de || issues.wikidata    || Timeout
 +
|-
 +
| align="right"|    140 || scholia.toolforge.org    || named_queries      || EndPointInternalError
 +
|-
 +
| align="right"|    102 || ceur-ws.org              || challenge          || Syntax Error
 +
|-
 +
| align="right"|      74 || qlever.cs.uni-freiburg.de || issues.wikidata    || EndPointInternalError
 +
|-
 +
| align="right"|      46 || ceur-ws.org              || challenge          || EndPointInternalError
 +
|-
 +
| align="right"|      42 || wikidata.org              || short_url          || Timeout
 +
|-
 +
| align="right"|      28 || scholia.toolforge.org    || named_queries      || Other
 +
|-
 +
| align="right"|      26 || qlever.cs.uni-freiburg.de || issues.wikidata    || Service Unavailable
 +
|-
 +
| align="right"|      24 || wikidata.org              || examples          || Other
 +
|-
 +
| align="right"|      24 || scholia.toolforge.org    || named_queries      || Timeout
 +
|-
 +
| align="right"|      20 || wikidata.org              || examples          || Service Unavailable
 +
|-
 +
| align="right"|      20 || qlever.cs.uni-freiburg.de || performance-dblp  || Syntax Error
 +
|-
 +
| align="right"|      18 || wikidata.org              || short_url          || Service Unavailable
 +
|-
 +
| align="right"|      16 || bitplan.com              || federated-queries  || Syntax Error
 +
|-
 +
| align="right"|      14 || scholia.toolforge.org    || named_queries      || Service Unavailable
 +
|-
 +
| align="right"|      12 || bitplan.com              || federated-queries  || EndPointInternalError
 +
|-
 +
| align="right"|      12 || scholia.toolforge.org    || named_queries      || Too Many Requests
 +
|-
 +
| align="right"|      5 || wikidata.org              || snapquery-examples || Timeout
 +
|-
 +
| align="right"|      4 || wikidata.org              || snapquery-examples || EndPointInternalError
 +
|-
 +
| align="right"|      4 || wikidata.org              || examples          || Too Many Requests
 +
|-
 +
| align="right"|      2 || qlever.cs.uni-freiburg.de || issues.wikidata    || Authorization Error
 +
|-
 +
| align="right"|      2 || ceur-ws.org              || challenge          || Other
 +
|-
 +
| align="right"|      2 || qlever.cs.uni-freiburg.de || issues.wikidata    || Other
 +
|-
 +
| align="right"|      2 || ceur-ws.org              || challenge          || Service Unavailable
 +
|-
 +
| align="right"|      2 || qlever.cs.uni-freiburg.de || performance-dblp  || Service Unavailable
 +
|-
 +
| align="right"|      2 || qlever.cs.uni-freiburg.de || issues.wikidata    || Too Many Requests
 +
|-
 +
| align="right"|      2 || wikidata.org              || short_url          || Too Many Requests
 +
|}
 +
 +
named query query_failures_by_category_grouped:
 +
 +
sql:
 +
SELECT
 +
  count(*) AS count,
 +
  GROUP_CONCAT(DISTINCT nq.domain) AS domains,
 +
  GROUP_CONCAT(DISTINCT nq.namespace) AS namespaces,
 +
  error_category
 +
FROM QueryStats qs
 +
JOIN NamedQuery nq ON qs.query_id = nq.query_id
 +
WHERE error_category IS NOT NULL
 +
GROUP BY error_category
 +
ORDER BY  count DESC;
 +
 +
== query_failures_by_category_grouped ==
 +
 +
=== query ===
 +
<source lang='sql'>
 +
SELECT
 +
  count(*) AS count,
 +
  GROUP_CONCAT(DISTINCT nq.domain) AS domains,
 +
  GROUP_CONCAT(DISTINCT nq.namespace) AS namespaces,
 +
  error_category
 +
FROM QueryStats qs
 +
JOIN NamedQuery nq ON qs.query_id = nq.query_id
 +
WHERE error_category IS NOT NULL
 +
GROUP BY error_category
 +
ORDER BY  count DESC;
 +
 +
</source>
 +
 +
 +
=== result ===
 +
{| class="wikitable" style="text-align: left;"
 +
|+ <!-- caption -->
 +
|-
 +
! align="right"|  count !! domains                                                                              !! namespaces                                                                                      !! error_category
 +
|-
 +
| align="right"|    3790 || wikidata.org,qlever.cs.uni-freiburg.de,ceur-ws.org,scholia.toolforge.org,bitplan.com || short_url,issues.wikidata,challenge,named_queries,performance-dblp,examples,federated-queries  || Syntax Error
 +
|-
 +
| align="right"|    1142 || wikidata.org,ceur-ws.org,scholia.toolforge.org,bitplan.com,qlever.cs.uni-freiburg.de || short_url,challenge,examples,named_queries,federated-queries,snapquery-examples,issues.wikidata || EndPointInternalError
 +
|-
 +
| align="right"|    399 || wikidata.org,qlever.cs.uni-freiburg.de,scholia.toolforge.org                        || snapquery-examples,short_url,examples,issues.wikidata,named_queries                            || Timeout
 +
|-
 +
| align="right"|      82 || qlever.cs.uni-freiburg.de,ceur-ws.org,scholia.toolforge.org,wikidata.org            || issues.wikidata,challenge,performance-dblp,named_queries,short_url,examples                    || Service Unavailable
 +
|-
 +
| align="right"|      56 || scholia.toolforge.org,qlever.cs.uni-freiburg.de,ceur-ws.org,wikidata.org            || named_queries,issues.wikidata,challenge,examples                                                || Other
 +
|-
 +
| align="right"|      20 || wikidata.org,scholia.toolforge.org,qlever.cs.uni-freiburg.de                        || examples,named_queries,short_url,issues.wikidata                                                || Too Many Requests
 +
|-
 +
| align="right"|      2 || qlever.cs.uni-freiburg.de                                                            || issues.wikidata                                                                                || Authorization Error
 +
|}
 +
== query_failures_by_category_grouped_counted ==
 +
 +
=== query ===
 +
<source lang='sql'>
 +
SELECT
 +
  error_category,
 +
  SUM(entry_count) AS total_count,
 +
  GROUP_CONCAT(DISTINCT domain_counts ORDER BY domain_count DESC) AS domain_counts,
 +
  GROUP_CONCAT(DISTINCT namespace_counts ORDER BY namespace_count DESC) AS namespace_counts,
 +
  GROUP_CONCAT(DISTINCT endpoint_counts ORDER BY endpoint_count DESC) AS endpoint_counts
 +
FROM (
 +
  SELECT
 +
    error_category,
 +
    domain,
 +
    namespace,
 +
    endpoint_name,
 +
    COUNT(*) AS entry_count,
 +
    domain || ' (' || SUM(COUNT(*)) OVER (PARTITION BY error_category, domain) || ')' AS domain_counts,
 +
    namespace || ' (' || SUM(COUNT(*)) OVER (PARTITION BY error_category, namespace) || ')' AS namespace_counts,
 +
    endpoint_name || ' (' || SUM(COUNT(*)) OVER (PARTITION BY error_category, endpoint_name) || ')' AS endpoint_counts,
 +
    SUM(COUNT(*)) OVER (PARTITION BY error_category, domain) AS domain_count,
 +
    SUM(COUNT(*)) OVER (PARTITION BY error_category, namespace) AS namespace_count,
 +
    SUM(COUNT(*)) OVER (PARTITION BY error_category, endpoint_name) AS endpoint_count
 +
  FROM QueryStats qs
 +
  JOIN NamedQuery nq ON qs.query_id = nq.query_id
 +
  WHERE error_category IS NOT NULL
 +
  GROUP BY error_category, domain, namespace, endpoint_name
 +
) sub
 +
GROUP BY error_category
 +
ORDER BY total_count DESC;
 +
 +
</source>
 +
 +
 +
=== result ===
 +
{| class="wikitable" style="text-align: left;"
 +
|+ <!-- caption -->
 +
|-
 +
! error_category        !! align="right"|  total_count !! domain_counts                                                                                                      !! namespace_counts                                                                                                                      !! endpoint_counts
 +
|-
 +
| Syntax Error          || align="right"|          3790 || scholia.toolforge.org (2044),wikidata.org (996),qlever.cs.uni-freiburg.de (632),ceur-ws.org (102),bitplan.com (16) || named_queries (2044),examples (788),issues.wikidata (612),short_url (208),challenge (102),performance-dblp (20),federated-queries (16) || wikidata-triply (1100),wikidata-qlever (996),wikidata-openlinksw (788),wikidata (576),wikidata-scatter (330)
 +
|-
 +
| EndPointInternalError || align="right"|          1142 || wikidata.org (870),scholia.toolforge.org (140),qlever.cs.uni-freiburg.de (74),ceur-ws.org (46),bitplan.com (12)    || examples (624),short_url (242),named_queries (140),issues.wikidata (74),challenge (46),federated-queries (12),snapquery-examples (4)  || wikidata-openlinksw (642),wikidata-qlever (474),wikidata-scatter (20),wikidata (6)
 +
|-
 +
| Timeout              || align="right"|          399 || wikidata.org (227),qlever.cs.uni-freiburg.de (148),scholia.toolforge.org (24)                                      || examples (180),issues.wikidata (148),short_url (42),named_queries (24),snapquery-examples (5)                                          || wikidata-triply (342),wikidata (57)
 +
|-
 +
| Service Unavailable  || align="right"|            82 || wikidata.org (38),qlever.cs.uni-freiburg.de (28),scholia.toolforge.org (14),ceur-ws.org (2)                        || issues.wikidata (26),examples (20),short_url (18),named_queries (14),challenge (2),performance-dblp (2)                                || wikidata-triply (82)
 +
|-
 +
| Other                || align="right"|            56 || scholia.toolforge.org (28),wikidata.org (24),ceur-ws.org (2),qlever.cs.uni-freiburg.de (2)                        || named_queries (28),examples (24),challenge (2),issues.wikidata (2)                                                                    || wikidata (18),wikidata-openlinksw (14),wikidata-scatter (12),wikidata-triply (8),wikidata-qlever (4)
 +
|-
 +
| Too Many Requests    || align="right"|            20 || scholia.toolforge.org (12),wikidata.org (6),qlever.cs.uni-freiburg.de (2)                                          || named_queries (12),examples (4),issues.wikidata (2),short_url (2)                                                                      || wikidata (18),wikidata-qlever (2)
 +
|-
 +
| Authorization Error  || align="right"|            2 || qlever.cs.uni-freiburg.de (2)                                                                                      || issues.wikidata (2)                                                                                                                    || wikidata-openlinksw (2)
 +
|}
 +
 +
== Query Execution Times per Endpoint==
 +
[[File:snapquery_eval_2024-07_executiontime_per_endpoint.png|800px]]
 +
==Distribution of Query Errors==
 +
[[File:snapquery_eval_2024-07_query_errors.png|800px]]
 +
== Query Execution Status on different Endpoints ==
 +
[[File:snapquery_eval_2024-07_query_execution_status.png|800px]]
 +
 +
 +
[[Category:snapquery]]

Latest revision as of 23:25, 10 July 2024

Duration long tail

show cut off by query time out. 

SELECT duration,nq.name
FROM QueryStats qs
JOIN NamedQuery nq 
ON qs.query_id=nq.query_id
WHERE nq.domain="wikidata.org"
ORDER BY 1 DESC

named query query_failures_by_category:

sql: SELECT count(*) as count, nq.domain, nq.namespace, error_category FROM QueryStats qs JOIN NamedQuery nq ON qs.query_id=nq.query_id WHERE error_category IS NOT NULL

GROUP BY error_category,nq.namespace,nq.domain ORDER BY 1 DESC

query_failures_by_category

query

SELECT 
count(*) as count,
nq.domain,
nq.namespace,
error_category
FROM QueryStats qs
JOIN NamedQuery nq
ON qs.query_id=nq.query_id
WHERE error_category IS NOT NULL 

GROUP BY error_category,nq.namespace,nq.domain
ORDER BY 1 DESC


result

count domain namespace error_category
2044 scholia.toolforge.org named_queries Syntax Error
788 wikidata.org examples Syntax Error
624 wikidata.org examples EndPointInternalError
612 qlever.cs.uni-freiburg.de issues.wikidata Syntax Error
242 wikidata.org short_url EndPointInternalError
208 wikidata.org short_url Syntax Error
180 wikidata.org examples Timeout
148 qlever.cs.uni-freiburg.de issues.wikidata Timeout
140 scholia.toolforge.org named_queries EndPointInternalError
102 ceur-ws.org challenge Syntax Error
74 qlever.cs.uni-freiburg.de issues.wikidata EndPointInternalError
46 ceur-ws.org challenge EndPointInternalError
42 wikidata.org short_url Timeout
28 scholia.toolforge.org named_queries Other
26 qlever.cs.uni-freiburg.de issues.wikidata Service Unavailable
24 wikidata.org examples Other
24 scholia.toolforge.org named_queries Timeout
20 wikidata.org examples Service Unavailable
20 qlever.cs.uni-freiburg.de performance-dblp Syntax Error
18 wikidata.org short_url Service Unavailable
16 bitplan.com federated-queries Syntax Error
14 scholia.toolforge.org named_queries Service Unavailable
12 bitplan.com federated-queries EndPointInternalError
12 scholia.toolforge.org named_queries Too Many Requests
5 wikidata.org snapquery-examples Timeout
4 wikidata.org snapquery-examples EndPointInternalError
4 wikidata.org examples Too Many Requests
2 qlever.cs.uni-freiburg.de issues.wikidata Authorization Error
2 ceur-ws.org challenge Other
2 qlever.cs.uni-freiburg.de issues.wikidata Other
2 ceur-ws.org challenge Service Unavailable
2 qlever.cs.uni-freiburg.de performance-dblp Service Unavailable
2 qlever.cs.uni-freiburg.de issues.wikidata Too Many Requests
2 wikidata.org short_url Too Many Requests

named query query_failures_by_category_grouped:

sql: SELECT 

 count(*) AS count,
 GROUP_CONCAT(DISTINCT nq.domain) AS domains,
 GROUP_CONCAT(DISTINCT nq.namespace) AS namespaces,
 error_category

FROM QueryStats qs JOIN NamedQuery nq ON qs.query_id = nq.query_id WHERE error_category IS NOT NULL GROUP BY error_category ORDER BY count DESC;

query_failures_by_category_grouped

query

SELECT 
  count(*) AS count,
  GROUP_CONCAT(DISTINCT nq.domain) AS domains,
  GROUP_CONCAT(DISTINCT nq.namespace) AS namespaces,
  error_category
FROM QueryStats qs
JOIN NamedQuery nq ON qs.query_id = nq.query_id
WHERE error_category IS NOT NULL
GROUP BY error_category
ORDER BY  count DESC;


result

count domains namespaces error_category
3790 wikidata.org,qlever.cs.uni-freiburg.de,ceur-ws.org,scholia.toolforge.org,bitplan.com short_url,issues.wikidata,challenge,named_queries,performance-dblp,examples,federated-queries Syntax Error
1142 wikidata.org,ceur-ws.org,scholia.toolforge.org,bitplan.com,qlever.cs.uni-freiburg.de short_url,challenge,examples,named_queries,federated-queries,snapquery-examples,issues.wikidata EndPointInternalError
399 wikidata.org,qlever.cs.uni-freiburg.de,scholia.toolforge.org snapquery-examples,short_url,examples,issues.wikidata,named_queries Timeout
82 qlever.cs.uni-freiburg.de,ceur-ws.org,scholia.toolforge.org,wikidata.org issues.wikidata,challenge,performance-dblp,named_queries,short_url,examples Service Unavailable
56 scholia.toolforge.org,qlever.cs.uni-freiburg.de,ceur-ws.org,wikidata.org named_queries,issues.wikidata,challenge,examples Other
20 wikidata.org,scholia.toolforge.org,qlever.cs.uni-freiburg.de examples,named_queries,short_url,issues.wikidata Too Many Requests
2 qlever.cs.uni-freiburg.de issues.wikidata Authorization Error

query_failures_by_category_grouped_counted

query

SELECT 
  error_category,
  SUM(entry_count) AS total_count,
  GROUP_CONCAT(DISTINCT domain_counts ORDER BY domain_count DESC) AS domain_counts,
  GROUP_CONCAT(DISTINCT namespace_counts ORDER BY namespace_count DESC) AS namespace_counts,
  GROUP_CONCAT(DISTINCT endpoint_counts ORDER BY endpoint_count DESC) AS endpoint_counts
FROM (
  SELECT 
    error_category,
    domain,
    namespace,
    endpoint_name,
    COUNT(*) AS entry_count,
    domain || ' (' || SUM(COUNT(*)) OVER (PARTITION BY error_category, domain) || ')' AS domain_counts,
    namespace || ' (' || SUM(COUNT(*)) OVER (PARTITION BY error_category, namespace) || ')' AS namespace_counts,
    endpoint_name || ' (' || SUM(COUNT(*)) OVER (PARTITION BY error_category, endpoint_name) || ')' AS endpoint_counts,
    SUM(COUNT(*)) OVER (PARTITION BY error_category, domain) AS domain_count,
    SUM(COUNT(*)) OVER (PARTITION BY error_category, namespace) AS namespace_count,
    SUM(COUNT(*)) OVER (PARTITION BY error_category, endpoint_name) AS endpoint_count
  FROM QueryStats qs
  JOIN NamedQuery nq ON qs.query_id = nq.query_id
  WHERE error_category IS NOT NULL
  GROUP BY error_category, domain, namespace, endpoint_name
) sub
GROUP BY error_category
ORDER BY total_count DESC;


result

error_category total_count domain_counts namespace_counts endpoint_counts
Syntax Error 3790 scholia.toolforge.org (2044),wikidata.org (996),qlever.cs.uni-freiburg.de (632),ceur-ws.org (102),bitplan.com (16) named_queries (2044),examples (788),issues.wikidata (612),short_url (208),challenge (102),performance-dblp (20),federated-queries (16) wikidata-triply (1100),wikidata-qlever (996),wikidata-openlinksw (788),wikidata (576),wikidata-scatter (330)
EndPointInternalError 1142 wikidata.org (870),scholia.toolforge.org (140),qlever.cs.uni-freiburg.de (74),ceur-ws.org (46),bitplan.com (12) examples (624),short_url (242),named_queries (140),issues.wikidata (74),challenge (46),federated-queries (12),snapquery-examples (4) wikidata-openlinksw (642),wikidata-qlever (474),wikidata-scatter (20),wikidata (6)
Timeout 399 wikidata.org (227),qlever.cs.uni-freiburg.de (148),scholia.toolforge.org (24) examples (180),issues.wikidata (148),short_url (42),named_queries (24),snapquery-examples (5) wikidata-triply (342),wikidata (57)
Service Unavailable 82 wikidata.org (38),qlever.cs.uni-freiburg.de (28),scholia.toolforge.org (14),ceur-ws.org (2) issues.wikidata (26),examples (20),short_url (18),named_queries (14),challenge (2),performance-dblp (2) wikidata-triply (82)
Other 56 scholia.toolforge.org (28),wikidata.org (24),ceur-ws.org (2),qlever.cs.uni-freiburg.de (2) named_queries (28),examples (24),challenge (2),issues.wikidata (2) wikidata (18),wikidata-openlinksw (14),wikidata-scatter (12),wikidata-triply (8),wikidata-qlever (4)
Too Many Requests 20 scholia.toolforge.org (12),wikidata.org (6),qlever.cs.uni-freiburg.de (2) named_queries (12),examples (4),issues.wikidata (2),short_url (2) wikidata (18),wikidata-qlever (2)
Authorization Error 2 qlever.cs.uni-freiburg.de (2) issues.wikidata (2) wikidata-openlinksw (2)

Query Execution Times per Endpoint

Snapquery eval 2024-07 executiontime per endpoint.png

Distribution of Query Errors

Snapquery eval 2024-07 query errors.png

Query Execution Status on different Endpoints

Snapquery eval 2024-07 query execution status.png

Retrieved from "https://cr.bitplan.com/index.php?title=Snapquery_Evaluation_2024-07&oldid=2465"