Resources exceeded error in google big query

Question

I am trying to join two tables Tb1 and Tb2 and the join is made on field Id1 of table1 and Id2 of table2.

When I run this query, I get the error "Resources exceeded during query execution".

Would appreciate if someone could give feedback on this query improvement.

        SELECT
          *,
          DATE(DateTime) AS Date
        FROM (
          SELECT
            a.Id AS Id,
            b.DateTime AS DateTime,
            b.Location1 AS Latitude,
            b.Location2 AS Longitude
          FROM (
            SELECT
              *
            FROM (
              SELECT
                Id AS Hid1,
              FROM
                [Tb1]
              WHERE
                DBName LIKE '%honda%') AS a
            INNER JOIN (
              SELECT
                Id AS Hid2,
                DateTime AS DateTime,
                Location1 AS Latitude,
                Location2 AS Longitude
              FROM
                TABLE_DATE_RANGE([Tb2],TIMESTAMP('2017-02-13'),TIMESTAMP('2017-02-14'))) AS b
            ON
              a.Id1 = b.Id2 ))
        WHERE
          DATE(DateTime) BETWEEN '2017-02-13'
          AND '2017-02-14'
        ORDER BY
          Id,
          Date

The query in your question is just total mess/junk and will never work even if you remove ORDER BY!! Take a little efforts to clean/fix it so it at least makes sense. Doing this you can show some respect to those who willing to help you here on SO and you have better chances to get help! Meantime - obviously - the best way to get the error Resources exceeded during query execution - is to put ORDER BY for the very outer SELECT. Sure, just remove it or add LIMIT NNN — Mikhail Berlyant

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-03-16T01:19:15

This is ironic. This afternoon we watched a Google video on performance in BigQuery. One of the major issues is sorting. So, if your results are really big, then sorting is a big problem. (Something that I've seen before.)

The simplest solution is to swallow the lemonade and decide that you really, really like limit and don't need the entire result set. So, add a limit 100 or limit 10000 and see if the problem goes away.

Unfortunately, I don't have specific advice if you need the entire result set sorted.

Resources exceeded error in google big query

1 Answers