Google BigQuery queries are slow

Question

I am using Google BigQuery and I am executing some simple queries from PHP. (e.g. SELECT * from emails WHERE email='[e-mail]') I am just checking if the email exists in the table.

The table "emails" is empty for now. But still the PHP script takes around 4 minutes to check 175 emails on an empty table .. As I wish in future the table will be filled and will have 500 000 mails then I guess the request time will be longer.

Is that normal ? Or are there any ideas/solutions to improve the checking time ?

(P.S. : The table "emails" contains only 8 columns, all are string type)

Thank you !

Answer 1

Here is a sample code in PHP to do streaming inserts, using the official https://github.com/google/google-api-php-client:

/**
 * 
 * @param type $client
 * @param type $project_id
 * @param type $dataset_id
 * @param type $rows
 * @return boolean
 * @throws Google_Service_Exception
 */
public function BQ_Tabledata_InsertAll($client, $project_id, $dataset_id, $rows) {
    $bq = new Google_Service_Bigquery($client);
    $request = new Google_Service_Bigquery_TableDataInsertAllRequest();
    $request->setRows($rows);
    try {
        $resp = new Google_Service_Bigquery_TableDataInsertAllResponse();
        $resp = $bq->tabledata->insertAll($project_id, $dataset_id, static::tableId(), $request);
        $errors = new Google_Service_Bigquery_TableDataInsertAllResponseInsertErrors();
        $errors = @$resp->getInsertErrors();
        if (!empty($errors)) {
            $error_msg = '';
            if (is_array($errors)) {
                $line = 0;
                foreach ($errors as $eP) {
                    $arr = $eP->getErrors();
                    if (is_array($arr)) {
                        foreach ($arr as $e) {
                            switch ($e->getReason()) {
                                case "stopped":
                                    break;
                                default:
                                    $error_msg.= sprintf("Error on line %s: %s\r\n", $line, $e->getMessage());
                                    break;
                            }
                        }
                    }
                    $line++;
                }
                $this->setErrorMessage($error_msg);
            } else {
                $this->setErrorMessage($errors);
            }
            //print_r($errors);
            //exit;
            return false;
        }
        return true;
    } catch (Google_Service_Exception $e) {
        $this->setErrors($e->getErrors())->setErrorMessage($e->getMessage());
        throw $e;
    }
}

Answer 2

If you are just checking for existence of a field, consider using SELECT COUNT(*) FROM emails where email='[e-mail]' instead. This will only require reading a single field, and so will cost less and be marginally faster on large tables.

And as Pentium10 suggested, consider using multiple lookups in a single query. You could do this like:

SELECT SUM((IF(email = '[e-mail]', 1, 0)) as m1,
       SUM((IF(email = '[e-mail]', 1, 0)) as m2,
       SUM((IF(email = '[e-mail]', 1, 0)) as m3,
       ...
 FROM emails

You're going to be limited to something like 64k of these in a single query, but it should be very fast to compute since it only requires scan of a single column in one pass.

Alternately,if you wanted the e-mails as one per row, you could do something a little bit fancier like

 SELECT email FROM emails WHERE email IN
 ('[e-mail]', '[e-mail]', '[e-mail]'...)
 GROUP BY email

As a further optimization, you could do it as a LEFT JOIN:

SELECT t1.email as email, IF(t2.email is not null, true, false) as found 
FROM [interesting_emails] t1  
LEFT OUTER JOIN [emails] t2 ON t1.email = t2.email

If the interesting_emails had the list of emails you wanted to check, like

[e-mail]
[e-mail]
[e-mail]

If the emails table contained only mail1@ and maiil2@, then you'd get back as results:

email            found
______________   _____
[e-mail]   true
[e-mail]   false
[e-mail]   true

The advantage of doing it this way is that it will scale up to the billions of e-mails if needed (when the number gets large you might consider using a JOIN EACH instead of a JOIN).